Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query LDAP server from SQL Server Expand / Collapse
Author
Message
Posted Tuesday, March 14, 2006 3:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 19, 2012 8:37 PM
Points: 1, Visits: 12

Has anyone queried the LDAP server for authentication purpose from SQL server.  I have not had much luck, I tried the following steps listed on microsoft website and got an error (see below).  Anyone who has had any luck please post your reply.

Thanks

 

___________________________________________________________________________________________________________

exec sp_addlinkedserver
'ADSITest',
'My Active DirectoryTest',
'ADSDSOObject',
'adsdatasource'
go
exec sp_addlinkedsrvlogin
'ADSITest',
false,
'locallogin',
'remotelogin',
'remotepwd'
go

Select * from OpenQuery(ADSITest,'select givenName,sn,samAccountName,cn
from
''LDAP://samme323/cn=users,dc=samme323,dc=com'' where cn=''sk*'' and
objectCategory
= ''Person''')

_________________________________________________________________________________________________

I can successfully setup the addlinkesrvlogin. After that I try the OpenQuery using the command below (please note the ldap server at "lockheed martin co - lmco" is A04dc47)

-------------------------------------------------------------------

Select * from OpenQuery(ADSITest,'select givenName,sn,samAccountName,cn
from
''LDAP://A04dc47/cn=users,dc=A04dc47,dc=com'' where cn=''sk*'' and
objectCategory
= ''Person''')

-------------------------------------------------------------

and I get the following error:

-----------------------------------------------------------------------------------

Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'ADSDSOObject' reported an error. The provider ran out of memory.

OLE DB error trace [OLE/DB Provider 'ADSDSOObject' IDBCreateCommand::CreateCommand returned 0x8007000e: The provider ran out of memory.].

-----------------------------------------------------------------------

Post #265745
Posted Wednesday, March 15, 2006 6:00 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:02 AM
Points: 477, Visits: 1,891
Hi,

i'm using

SELECT * FROM OPENQUERY( [SERVER1],
'SELECT sAMAccountName, userPrincipalName, company, department, Name, givenName, SN, Mail, telephoneNumber, mobile,
l, physicalDeliveryOfficeName, postalCode, streetAddress, facsimileTelephoneNumber, msExchHideFromAddressLists, distinguishedName, info
FROM ''LDAP://SERVER1/ DC=subsubdomain,DC=subdomain,DC=domain''
WHERE
objectClass = ''user''
AND
objectCategory = ''Person''
AND (mobile = ''*'' OR telephoneNumber = ''*'' OR facsimileTelephoneNumber = ''*'' OR mail = ''*'' )
ORDER BY userPrincipalName
')

The difference lies in a space between "LDAP://SERVER1/" and "DC=subsubdomain,DC=subdom..."
and in the where clause. Don't know if that will make the difference?

regards karl



Best regards
karl
Post #265833
Posted Thursday, July 9, 2009 8:11 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:57 AM
Points: 405, Visits: 1,137
Hi hopefully someone can help me with this as well.

as above, I'm trying to query my LDAP server.

This is what I have setup:

I have access to our Active Directory and can view users and machines in active directory

- SQL Server 2005 Develper Edition installed on my machine.

- All services started.
SQL Server, SQL Server Agent, SSAS, SQL Server Browser, SQL Full Text Search & SQL Server VSS Writer all started as Local System
SSIS is started as Network Service

- OpenQuery is enabled
(I can run open query commands to other systems)

- I have defined a AD linked server as follows:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

- Having configure the linked server I have run the following query:
select * from openquery
(ADSI,'SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" ')

where the domain is MyDOMAIN.NET.

I get the error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".


What is: MSSQLServerADHelper - should this be started??
How do I know if SQL Server is operating in Kerboros mode - where do I change it?
Have I missed anything else??

Sorry - I know this has been covered several times, but I' struggling to see what the issue is!


_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #750262
Posted Thursday, July 9, 2009 8:11 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:57 AM
Points: 405, Visits: 1,137
Hi hopefully someone can help me with this as well.

as above, I'm trying to query my LDAP server.

This is what I have setup:

I have access to our Active Directory and can view users and machines in active directory

> SQL Server 2005 Develper Edition installed on my machine.

> All services started.
SQL Server, SQL Server Agent, SSAS, SQL Server Browser, SQL Full Text Search & SQL Server VSS Writer all started as Local System
SSIS is started as Network Service

> OpenQuery is enabled
(I can run open query commands to other systems)

> I have defined a AD linked server as follows:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

> Having configure the linked server I have run the following query:
select * from openquery
(ADSI,'SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" ')

where the domain is MyDOMAIN.NET.

I get the error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".


What is: MSSQLServerADHelper - should this be started??
How do I know if SQL Server is operating in Kerboros mode - where do I change it?
Have I missed anything else??

Sorry - I know this has been covered several times, but I' struggling to see what the issue is!


_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #750263
Posted Thursday, July 9, 2009 8:12 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:57 AM
Points: 405, Visits: 1,137
Hi hopefully someone can help me with this as well.

as above, I'm trying to query my LDAP server.

This is what I have setup:

I have access to our Active Directory and can view users and machines in active directory

> SQL Server 2005 Develper Edition installed on my machine.

> All services started.
SQL Server, SQL Server Agent, SSAS, SQL Server Browser, SQL Full Text Search & SQL Server VSS Writer all started as Local System
SSIS is started as Network Service

> OpenQuery is enabled
(I can run open query commands to other systems)

> I have defined a AD linked server as follows:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

> Having configure the linked server I have run the following query:
select * from openquery
(ADSI,'SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" ')



_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #750264
Posted Thursday, August 13, 2009 2:09 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 24, 2012 2:06 PM
Points: 97, Visits: 356
Hey Dave,

By default the adhoc queries are turned off in SQL 2005. I think you stated that you can run open queries but I'd double check the following.

sp_configure 'show advanced options', 1
reconfigure with override
GO
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
Post #770482
Posted Thursday, September 24, 2009 4:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 21, 2010 9:59 AM
Points: 1, Visits: 23
that happens because you are recovering information from LDAP that it isn't complatible with sqlServer

try this:

In SELECT statement put:

sAMAccountName,
cn

I hope this will be useful for you.
Post #793606
Posted Monday, March 15, 2010 12:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 23, 2012 10:11 AM
Points: 6, Visits: 49
I found this link to be very helpful. It demonstrates querying LDAP from SQL SERVER 2000:

http://sql.dzone.com/news/querying-active-directory-thro
Post #883254
Posted Tuesday, March 16, 2010 3:25 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 3, 2012 8:35 AM
Points: 242, Visits: 1,242
dave-dj (7/9/2009)
> OpenQuery is enabled
(I can run open query commands to other systems)

> I have defined a AD linked server as follows:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

> Having configure the linked server I have run the following query:
select * from openquery
(ADSI,'SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" ')



I had this problem too. I had to remove distinguishedName and co from my query and then it worked. I ended up trying each active directory field individually to find ones that would data in my query.




Terri

To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
Post #884258
Posted Friday, April 23, 2010 8:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 14, 2014 4:18 AM
Points: 2, Visits: 46
make sure you put 2 single quotes not 1 double quote in the scripts above!
Post #909492
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse