How to query Active Directory from MS SQL Server?

Recently, I was creating an intranet portal for a company that I am working for. Active directory has a lot of built-in fields for users like first name, last name, email, and phone. But it is still not enough for a business’s needs. So, I had to create an additional table for extended information in MS SQL Server database. Now how do you connect extended information and information from an active directory?

An 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 an old ODBC method or System.DirectoryServices namespace. However manual connection to a table from SQL Server and active directory information is too much work. It is much easier to use a linked server feature of MS SQL Server.

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

And then you can query a 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 the following error:

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

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