SQL SERVER – How to Query Active Directory Data Using ADSI / LDAP Linked Server

There are situations when you need to integrate SQL Server with other product. Once of the classic example was seen during my last visit to a client.  They wanted list of email addresses and phone numbers for all users in the company to be fetched by Active Directory. I have told them that SQL can read that data via linked server. Here are the steps to learn how to query active directory data.

Create Linked Server

We need to use Active Directory Service Interfaces (ADSI) linked server.  You need to provide a domain account which can query AD.

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

As highlighted above, you need to change @rmtuser and @rmtpassword variables to a login and password that has access to your Active Directory in the second statement.

Find LDAP URL

Below is the query which I ran to find next query.

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

We ran above query to find LDAP clause which we need to get details.

Fire the query

Let’s say we want to get more details about the SQL Service account, we can pick up the LADP URL from previous output.

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

Here is the output

As we can see query will return Active Directory’s attributed. Also 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 as shown above.

Reference: Pinal Dave (https://blog.sqlauthority.com)