Query LDAP server from SQL Server
-
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.].
-----------------------------------------------------------------------
-
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 -
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)
-
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)
-
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)
-
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
-
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.
-
I found this link to be very helpful. It demonstrates querying LDAP from SQL SERVER 2000:
-
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.
TerriTo 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.] -
make sure you put 2 single quotes not 1 double quote in the scripts above!
-
Hello,
I was recently experiencing this same issue for a Cross-Forest Two-Way Trust and the solution was to create a Local Domain User Account in the other Domain and use a Linked Server specifically for Non-Local Domains.
First, SQL Server 2005 is more restrictive from a Security Perspective. So, on the Security page of the Linked Server Properties, you must Select the 'Be made using the security context:' Radio button and enter a Domain User Account in one of the following forms ADSIUSER@DOMAIN.COM or DOMAIN\ADSIUSER into the 'Remote Login:' field, and the password for the User into the 'With password:' field.
I.E. Local Domain is LOCAL.COM and the user account specified in the Linked Server is: LOCAL\ADSILOC, the External Domain to query via ADSI is TEST.COM. I received the following:
'Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADSDSOObject" for linked server "ADSILOC" reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "%ADSIQUERY%" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSILOC". for this Linked Server.'
When I changed the Credentials to TEST\ADSITST, the Linked Server was able to query ADSI.
SQL Server does not seem to pass permissions cross-domain well, using a Local Domain User Account for each Forest we connect to has resolved our issue.
-
I got your example to work when I'm physically on the server, but when I try it from my workstation, I get:
An error occurred while preparing the query "SELECT * FROM 'LDAP://NOVMDC02.SD.INT' where objectClass = 'User' " for execution against OLE DB provider "ADsDSOObject" for linked server "(null)".
I assume it's some sort of security issue.
Any and all ideas are welcome.
-
Most - if not all - security issues are supposedly resolved if Kerberos is implemented.
Which is not the case where I work.
-
Peter Pirker (5/12/2011)
I got your example to work when I'm physically on the server, but when I try it from my workstation, I get:An error occurred while preparing the query "SELECT * FROM 'LDAP://NOVMDC02.SD.INT' where objectClass = 'User' " for execution against OLE DB provider "ADsDSOObject" for linked server "(null)".
I assume it's some sort of security issue.
Same exact behavior here, works fine if I remote onto the server where Linked Server is set up. I was thinking it was like a double hop issue.
-
I'm able to query LDAP from our SSRS server as well as directly from our SQL Server. I haven't tried it from any workstation.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply