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