Query Active Directory from SQL 2005

9. April 2008 00:10

I just wanted to post a simple way to pull data out of Active Directory via SQL 2005

First you will need to setup a linked server on your SQL Server named ADSI.  You can do this by following the directions from the link below:


http://msdn2.microsoft.com/en-us/library/aa772380(VS.85).aspx

You may have to change the Active Directory Services from 2.5 to 3.0 depending on the version.

Once this ADSI linked server is setup then you can query it.  The link above provides a nice query, but this is the one I use the most.

SELECT samAccountName As WinNT_ID
    ,sn As Last_Name
    ,givenName As First_Name
    ,displayName AS Display_Name
    ,mail As Email_Address
    ,ISNULL(title, 'N/A') As Job_Title    
    ,ISNULL(telephonenumber, 'N/A') As Phone_Number
    ,ISNULL(mobile, 'N/A') As Mobile_Number
    ,ISNULL(facsimiletelephonenumber, 'N/A') As Fax_Number
    ,ISNULL(physicalDeliveryOfficeName, 'N/A') As Office
    ,ISNULL(department, 'N/A') AS Department
    ,streetAddress As Stree_Address
    ,l As City
    ,st As State
    ,postalCode As Zip_Code
    FROM OPENQUERY(ADSI, '
    SELECT samAccountName, sn, givenName, title, displayName
    ,department,mail, telephonenumber, mobile, facsimiletelephonenumber
    ,physicalDeliveryOfficeName,l,streetAddress,st,postalCode
    FROM ''LDAP://OU=,DC=,DC=,DC=''
    WHERE objectClass=''user'' AND objectClass<>''computer''
    ') AS tblADSI
WHERE sn IS NOT NULL
AND mail IS NOT NULL
ORDER BY sn

In the query make sure to fill in the LDAP information accordingly. 

What else is nice about this is that you can pull info based off ntlogins by adding the condition in the WHERE clasuse so it's nice to use in web apps as well.

Tags: ,

Microsoft SQL

blog comments powered by Disqus



My Random Thought

I think the OCW is a great thing to have available to those who are in school, just finished school or just want to educate themself

http://ocwconsortium.org/

John On Twitter

Discounts