There are situations when you need to integrate SQL Server with other products. One classic example is when you need to fetch email addresses and phone numbers for all users in a company from Active Directory. In this article, we will learn how to query Active Directory data using a linked server in SQL Server.

Create Linked Server

To query Active Directory data, we need to use the Active Directory Service Interfaces (ADSI) linked server. Here are the steps to create the linked server:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'hadomain\administrator',@rmtpassword='@very$tr0ngP@$$w0rd1234'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible',  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation',  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation',  @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Make sure to change the @rmtuser and @rmtpassword variables in the second statement to a login and password that has access to your Active Directory.

Find LDAP URL

Next, we need to find the LDAP URL to query the Active Directory data. Here is the query to find the LDAP URL:

SELECT * FROM OpenQuery (
ADSI,
'SELECT *
FROM ''LDAP://hadomain.com/DC=hadomain,DC=com''
WHERE objectClass = ''User''
')

Run the above query to find the LDAP clause that we need to get the details.

Fire the Query

Now, let’s say we want to get more details about the SQL Service account. We can pick up the LDAP URL from the previous output and use it in the following query:

SELECT * FROM OpenQuery
(
ADSI,
'SELECT pager, displayName, telephoneNumber, sAMAccountName,
mail, mobile, facsimileTelephoneNumber, department, physicalDeliveryOfficeName, givenname
FROM  ''LDAP://hadomain.com/CN=SQLService,CN=Users,DC=HADOMAIN,DC=COM''
WHERE objectClass =  ''User''
') AS tblADSI

The above query will return the Active Directory attributes for the SQL Service account. Note that when we query Active Directory, it actually creates the SELECT statement backwards. We started the SELECT statement with SELECT pager… but in the results pane, it displayed pager last.

By following these steps, you can integrate SQL Server with Active Directory and fetch the required data for your applications.