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]
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'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible',  @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation',  @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation',  @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'

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.


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

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

SQL SERVER - How to Query Active Directory Data Using ADSI / LDAP Linked Server ADSI-01
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 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

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

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)

SQL Scripts, SQL Server
Previous Post
SQL SERVER – How to Add Multiple New Columns to Table with Default Values?
Next Post
SQL SERVER – Finding If Status of Bulk Logging Enabled or Not From Logs

Related Posts

27 Comments. Leave new

  • Try removing the WHERE clause on organizationUnit and try entering your OUs starting with child, then parent OU, like:

    FROM ”LDAP://hadomain.com/OU=ChildOU,OU=ParentOU,DC=HADOMAIN,DC=COM”

    And if you want a where clause on objectClass you might try something like:

    FROM ”LDAP://hadomain.com/OU=ChildOU,OU=ParentOU,DC=HADOMAIN,DC=COM”
    WHERE objectClass = ”user”
    AND objectClass ”computer”
    ‘) AS tblADSI

    Make sure you wrap those choices using two single quotes ”user” or ”computer” not double quotes

  • Looks like the not equal symbols (less-than greater-than) were stripped out of my comments. Meant to write objectClass not equals computer in the where clause in order to avoid querying computer accounts.

  • Hi…. appreciate the post. We have our ADSI LS setup this way and can see the creds being passed in clear text. Is there a secure method for this one? Many thanks.

  • When you create the linked server, choose the option to use “the login’s current security context” in the security section, instead of specifying a remote login ID / password. This will use kerberos to query the AD using the same AD account that runs your SQL server / SQL server agent. Make sure that account also has the rights to query your AD and ports are open for ldap and kerberos. If SQL is running as a local user, you could create a new service account on that AD with basic domain user privs, then change your SQL server and SQL server agent to run as that domain service account, etc.

  • Robert W. Steele
    May 7, 2024 8:38 pm

    So, I use SSMS on a machine to remotely connect to an instance on a server. I am able to do ADSI queries on my old machine and on the server itself but a not on my new replacement machine. I get the following error:

    Msg 7321, Level 16, State 2, Procedure vw_AD, Line 3 [Batch Start Line 0]
    An error occurred while preparing the query “SELECT sAMAccountName,distinguishedName,userPrincipalName,givenName,initials,sn,title, mail,manager,telephoneNumber, mobile, employeeID,department,company,physicalDeliveryOfficeName,streetAddress, l, st, postalCode,displayname,facsimileTelephoneNumber

    WHERE objectCategory = ‘Person’ AND objectClass = ‘user’
    AND ‘userAccountControl:1.2.840.113556.1.4.803:’2” for execution against OLE DB provider “ADSDSOObject” for linked server “ADSI”.

    Any ideas? It seems like the query executes but it cannot parse the data…any help would be appreciated.


Leave a Reply