SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query LDAP server from SQL Server


Query LDAP server from SQL Server

Author
Message
Avinash Pawar
Avinash Pawar
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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.].

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


Karl Klingler
Karl Klingler
Right there with Babe
Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

Group: General Forum Members
Points: 722 Visits: 2100
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
dave-dj
dave-dj
SSC Eights!
SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)

Group: General Forum Members
Points: 986 Visits: 1149
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)
dave-dj
dave-dj
SSC Eights!
SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)

Group: General Forum Members
Points: 986 Visits: 1149
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)
dave-dj
dave-dj
SSC Eights!
SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)

Group: General Forum Members
Points: 986 Visits: 1149
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)
Bob Lee
Bob Lee
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 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
hector.sa
hector.sa
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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.
NeddyRock
NeddyRock
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 51
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
Terri-92562
Terri-92562
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 1242
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.]
Geoff Fox
Geoff Fox
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 46
make sure you put 2 single quotes not 1 double quote in the scripts above!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search