Post reply

Active Directory Query

  • February 17, 2006 at 1:40 pm

    #103090

    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!

  • February 17, 2006 at 2:41 pm

    #621805

    http://support.microsoft.com/default.aspx?scid=kb;en-us;299410

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/distributed_query.asp

    http://msdn.microsoft.com/library/en-us/adsi/adsi/distributed_query.asp?frame=true

     

    Cheers,

     


    * Noel

  • February 17, 2006 at 4:10 pm

    #621836

    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!

  • February 17, 2006 at 4:54 pm

    #621844

    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!!

     

     

  • February 17, 2006 at 6:25 pm

    #621850

    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.

  • February 20, 2006 at 9:02 am

    #622059

    That worked perfectly, thanks!

  • February 20, 2006 at 1:31 pm

    #622107

    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.

  • March 31, 2006 at 12:23 pm

    #630218

    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

  • April 21, 2006 at 1:46 am

    #633420

    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). 

  • September 27, 2007 at 2:43 pm

    #736926

    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!

  • November 30, 2007 at 1:11 pm

    #756124

    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?

  • February 27, 2008 at 11:12 pm

    #782992

    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'' ')

  • June 18, 2008 at 8:28 am

    #830494

    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.

  • June 27, 2008 at 1:17 pm

    #835600

    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.

  • September 5, 2008 at 12:40 pm

    #867998

    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