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)

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

24 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

    Reply
  • 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.

    Reply

Leave a Reply

Menu
Exit mobile version