Active Directory Query
-
Is it possible to query Active Directory for user account information? I thought I heard something about an Extended Stored Proc that did something similar to this.
Thanks!
-
Thank you for the links. I did find those, but I seem to still be missing something. Do I just simply run the sp below, or do I still need to do some additional configurations?
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
Thanks again!
-
Here is some more information:
PDC: Windows 2003 Server
SQL Server 2000 on Windows 2000 Server
I executed the sp to add the linked server using the exact same verbage listed on that page.
I then executed the next query to specify the user and password since we use Mixed Mode. I used the domain administrator account.
I then executed the query to return the users. That returns this error:
Server: Msg 7321, Level 16, State 2, Procedure viewADContacts, Line 3
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare:
repare returned 0x80040e14].Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'viewADContacts'.
What are the steps I'm missing? Thank you!!
-
You can try this SQL query:
select * FROM OPENROWSET('ADSDSOObject',
'adsdatasource;', 'SELECT cn, mail, co, distinguishedName, displayName
FROM ''xxxxxxxxxxxxxxxxxx'' where objectClass = ''User'' ')
Note that xxxxxxxxxxxxxxxx can be
GC://something.com or LDAP://something.com where something.com is an Active directory server.
-
That worked perfectly, thanks!
-
Note that there is a resultset size threshold (normally 5000) on LDAP queries, set at the domain level. If you have more users, the OPENROWSET part of the query will always return exactly 5000 rows - no error message.
-
I used your sp:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
And ran the query against it on MS SQL 2000 it works fine, but when I repeat the same steps on MS SQL 2005 the server executes the sp just fine. The query is what fails with the following error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT cn, mail, co, distinguishedName, displayName FROM 'LDAP://presidioad/DC=presidio,DC=corp' where mail = 'm@m.com' " for execution against OLE DB provider "ADSDSOObject" for linked server "(null)".
I'm runing the sp like this:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
Any suggestions?
Thanks,
Mamoon
-
Just checking. Are your 2005 services running as a local user?
If this is the case change your linked server to connect as a domain user with relevent AD Query access rights (or change services to run as a domain user with relevent AD Query access rights).
-
Folks,
I have the same problem here, but the solutions provided solved the problem... while running the query on the server.
select * FROM OPENROWSET('ADSDSOObject',
'adsdatasource;', 'SELECT cn, mail, co, distinguishedName, displayName
FROM ''LDAP://myDomain.lan'' where objectClass = ''User'' ')
If i try to run the same query using SSMS on any desktop accessing this server, I get this error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT cn, mail, co, distinguishedName, displayName
FROM 'LDAP://myDomain.lan' where objectClass = 'User' " for execution against OLE DB provider "ADSDSOObject" for linked server "(null)".
If I try the other solution
select * from openquery
(ADSI,'SELECT name
FROM ''LDAP://myDomain.lan''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')
I get the same weird error people complain about:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT name
FROM 'LDAP://myDomain.lan'
WHERE objectCategory = 'Person' AND objectClass = 'user'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".
Any clues? Thanx!
-
I too get the same error you decribed. What I was told is the service SQL Server (MSSQLSERVER) cannot be run as a local user or local network. It has to be set up as a domain admin user within Active Directory. Although I have tried this and it doesn't work either. I am getting a bit agitated with this error as I have been trying to deal with this problem for close to 2 weeks now.
Any ideas on where to look toward next?
-
Actually , you don't even need to run sp_addLinkedServer. What you need to do is
1) your SQL server must run under the account which must have access right to the active directory.
2) you must login to active directory to run this query.
SELECT*
FROM OPENROWSET('ADSDSOObject', 'adsdatasource;',
'SELECT mail, displayName,givenname,distinguishedName, SAMAccountName, CN
FROM ''LDAP://ServerName/cn=users, DC=DOMAINName, DC=com'' ')
-
Ensure the following services are enabled and started and running under the same service account:
SQL Server
SQL Server Agent
SQL Server Analysis Services
SQL Browser
SQL FullText Search
SQL Reporting Services
We were able to query AD while logged onto the SQL Server (both locally and remotely) but not from a client computer using Management Studio. Once we set all the above services to run under the same service account, we were able to query AD from a remote computer.
-
I was able to retrieve data using a Linked Server if I specified the security context to use to access the Active Directory server. (The server running SQL Server is not a member of any domain and uses a local computer account to start the services.)
Here is a sample of the Linked Server Definition (SQL Server 2005 SP2):
/****** Object: LinkedServer [ADSI] Script Date: 06/27/2008 13:50:37 ******/
IF NOT EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'ADSI')
BEGIN
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'ADSDSOObject',
@provider=N'ADsDSOObject', @datasrc=N'adsdatasource'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,
@rmtuser=N'Domain\User',@rmtpassword='password'
END
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'
Then, I used the following query to retrieve the results:
SELECT * FROM OPENQUERY
(
ADSI,'SELECT name
FROM ''LDAP://server''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')
I hope this helps.
-
In most cases this error has something to do with the permissions of the credentials you are using to query the AD. Make sure these credentials are not restricted from searching/reading the active directory.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply