FAQ: How do I query Active Directory from SQL Server?


There are several ways but the most commonly used approach of using linked servers hits ona  1000 row limit in the AD OLE DB Provider, here is a solution using SQL CLR, I have not tried it but if it works as advertised then it should help, beware it does use SQLCLR.


 


 

Comments (4)

  1. Manges says:

    –A little easier way to perform this 🙂

    –Create the linked server

    EXEC sp_addlinkedserver ‘ADSI’, ‘Active Directory Services 2.5’,

    ‘ADSDSOObject’, ‘adsdatasource’

    –Create the login account

    –(change SQL_ACCOUNT_NAME, –NT_ACCOUNT_NAME, PASSSWORD)

    EXEC sp_addlinkedsrvlogin ‘ADSI’, False, ‘SQL_ACCOUNT_NAME’,

    ‘NT_ACCOUNT_NAME’, ‘PASSWORD’

    –Execute your query

    SELECT samAccountName, givenName [First Name], sn [Last Name],

    legacyExchangeDN

    FROM OPENQUERY(ADSI,

      ‘SELECT samAccountName,

          givenName, sn, legacyExchangeDN

       FROM ”LDAP://DC=zm,DC=COM”

       WHERE objectClass=”Person”

       AND objectClass = ”User”’)

    WHERE givenName IS NOT NULL

  2. Euan Garden says:

    Yep thats simpler but by default is it not subject to the 1000 row limit I mentioned?

  3. So James has responded back to me on ADAM.