SQL Server - How to query Active Directory (AD) information using Linked Server (ADSI)

Views: 2.832
Reading Time: 4 minutes

Hey guys!
All right with you ?

In this post I will show you how to query Active Directory (AD) information through SQL Server using a simple Linked Server and Active Directory Service Interfaces (ADSI) interface. Once again, Microsoft has been providing capabilities for SQL Server to integrate with more and more tools and interfaces and become an even more complete solution with each release.

In my work, I use this feature to daily load a table in the bank with all users and some information from them from AD, thus facilitating queries and manipulation of this data in reports.

How to create Linked Server

In order to be able to access Active Directory data, you will need to create a Linked Server using the provider “OLE DB Provider for Microsoft Directory Services”, as shown in the screen below:

If you are a fan of code line (like me), you can also create linked server using T-SQL:

How to query Active Directory information

Once you have created your Linked Server using ADSI, let's start looking at AD information.

Source code to query users

Result

Source code for consulting computers

Result

ADSI Limitations

As you start using ADSI, you will find that it has some limitations. One is that when performing queries that return more than 901 records, you will see the error message below:

Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider “ADSDSOObject” for linked server “ADSI”.

To work around this limitation, you can create a table to receive the data, perform a TOP 901, and scroll through the records and write to that table. To make your life easier, I'll have the script ready for you:

Another limitation that occurs in queries using ADSI is that when you try to query information from fields populated with multivalued data (eg postOfficeBox), you will get this error message:

Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider “ADsDSOObject” for linked server “ADSI”. Could not convert the data value due to reasons other than sign mismatch or overflow.

Unfortunately, I don't know a solution for this situation other than using CLR (C #) or PowerShell 🙁

Well, that's it folks!
I hope you enjoyed this post.
A hug and see you next.

sql server tsql select query query read retrieve get list ad active directory users groups computers

sql server tsql select query query read retrieve get list ad active directory users groups computers