How to query Active Directory from MS SQL Server?

Recently, I am creating intranet portal for a company that I am working for. Active directory has a lot of build-in fields for users like first name, last name, email, phone. But it still not enough for a bussiness needs. So, I had to create additional table for extended information in ms sql server database. Now how do you connect extended information and information from active directory?

Active directory has objectGuid field. You can use this field to connect your table and active directory information. .NET has two ways to access active directory data using old ODBC method or System.DirectoryServices namespace. However manual connection of table from sql server and active directory information is too much of work. It is much more easier to use linked server feature of MS SQL Server.

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

And then you can query linked server like this:

SELECT * FROM OpenQuery(ADSI, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://DC=whaever,DC=domain,DC=org'' where objectClass = ''User''')

 

You might see following error:

The OLE DB provider "ADSDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation.

Then you need to go to properties of newly created linked server and adjust security settings (Server Objects > Linked Servers > ADSI).


Posted on Monday, January 11, 2010 by | Add Comment

New Comment

Your Name:
Email (for internal use only):
Comment:
 
Code above:

Categories

Recent Tweets

  • Simon Ince's Blog: Hierarchies with HierarchyID in SQL 2008 http://t.co/xSDwiF6rRS.
  • Visual Studio 2010 WAS painfully slow - CodeProject http://t.co/Usba1x6CZy

Valid HTML5