Hey guys!
Are you all right?

In this post I will demonstrate to you how to query information from Active Directory (AD) through SQL Server using a simple Linked Server and the ADSI (Active Directory Service Interfaces) interface. Once again, Microsoft has been providing resources so that SQL Server can integrate with more and more tools and interfaces, becoming an even more complete solution with each version.

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

How to create Linked Server

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

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

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'', @provider=N'ADsDSOObject'
GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
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

How to query Active Directory information

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

Source code to query users

SELECT
    userPrincipalName,
    SamAccountName,
    displayName,
    givenName, 
    sn, 
    physicalDeliveryOfficeName, 
    mail, 
    telephoneNumber,
    Title,
    department,
    company,
    manager,
    mobile
FROM OPENQUERY (ADSI, '
SELECT
    userPrincipalName,
    SamAccountName,
    displayName,
    givenName, 
    sn, 
    physicalDeliveryOfficeName, 
    mail, 
    telephoneNumber,
    Title,
    department,
    company,
    manager,
    mobile
FROM
    ''LDAP://dirceuresende.local:389'' 
WHERE 
    objectCategory = ''Person''
    AND objectClass = ''User''
') AS Resultado
ORDER BY displayname

Result

Source code for querying computers

SELECT
    [Name],
    [Location],
    [Description],
    whenCreated,
    whenChanged, 
    logoncount
FROM OPENQUERY (ADSI, '
SELECT
    Name,
    Location,
    Description,
    whenCreated,
    whenChanged,
    logoncount
FROM
    ''LDAP://dirceuresende.local:389'' 
WHERE 
    objectClass = ''Computer''
') AS Resultado
ORDER BY [name]

Result

Limitations of ADSI

As you start using ADSI, you will realize that it has some limitations. One of them 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 get around this limitation, you can create a table to receive the data, perform a TOP 901 and go through the records and write to this table. To make your life easier, I'll leave the script ready for you:

IF (OBJECT_ID('tempdb..#Usuarios_AD') IS NOT NULL) DROP TABLE #Usuarios_AD
CREATE TABLE #Usuarios_AD (
    displayName nvarchar (4000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    SamAccountName nvarchar (4000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    userPrincipalName nvarchar (4000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    givenName nvarchar (4000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    sn nvarchar (4000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    Title nvarchar (4000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    department nvarchar (4000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    company nvarchar (4000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    physicalDeliveryOfficeName nvarchar (4000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    mail nvarchar (4000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    telephoneNumber nvarchar (4000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    mobile nvarchar (4000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    manager nvarchar (4000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    postOfficeBox varchar (10) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
)


IF (OBJECT_ID('tempdb..#Usuarios_AD_Temp') IS NOT NULL) DROP TABLE #Usuarios_AD_Temp
SELECT displayName, SamAccountName, userPrincipalName, givenName, sn, Title, department, company, physicalDeliveryOfficeName, mail, telephoneNumber, mobile, manager
INTO #Usuarios_AD_Temp
FROM #Usuarios_AD


        
DECLARE 
    @Ds_Ultimo_Login VARCHAR(100) = 'a',
    @Query VARCHAR(MAX)
        
        
WHILE(@Ds_Ultimo_Login IS NOT NULL)
BEGIN
        
            
    TRUNCATE TABLE #Usuarios_AD_Temp


    SET @Query = '
    SELECT 
        userPrincipalName,
        SamAccountName,
        displayName,
        givenName, 
        sn, 
        physicalDeliveryOfficeName, 
        mail, 
        telephoneNumber,
        Title,
        department,
        company,
        manager,
        mobile
    FROM 
        ''''LDAP://dirceuresende.local:389''''
    WHERE 
        objectCategory = ''''Person'''' 
        AND objectClass = ''''User''''
        AND SamAccountName > ''''' + @Ds_Ultimo_Login + '''''
    ORDER BY
        SamAccountName
    '
            
    SET @Query = '
    INSERT INTO #Usuarios_AD_Temp (displayName, SamAccountName, userPrincipalName, givenName, sn, Title, department, company, physicalDeliveryOfficeName, mail, telephoneNumber, mobile, manager)
    SELECT TOP 901 displayName, SamAccountName, userPrincipalName, givenName, sn, Title, department, company, physicalDeliveryOfficeName, mail, telephoneNumber, mobile, manager 
    FROM OPENQUERY([ADSI], ''' + @Query + ''')'
            
    EXEC(@Query)


    INSERT INTO #Usuarios_AD(displayName, SamAccountName, userPrincipalName, givenName, sn, Title, department, company, physicalDeliveryOfficeName, mail, telephoneNumber, mobile, manager)
    SELECT DISTINCT displayName, SamAccountName, userPrincipalName, givenName, sn, Title, department, company, physicalDeliveryOfficeName, mail, telephoneNumber, mobile, manager 
    FROM #Usuarios_AD_Temp

            
            
    SET @Ds_Ultimo_Login = NULL
    SELECT TOP 1 @Ds_Ultimo_Login = SamAccountName FROM #Usuarios_AD_Temp ORDER BY SamAccountName DESC
        
        
END


SELECT * FROM #Usuarios_AD

Another limitation that occurs in queries using ADSI is that when trying to query information from fields filled with multivalued data (Ex: postOfficeBox), you will receive 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 liked this post.
A hug and see you next time.

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

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