SQL Server - Como consultar informações do Active Directory (AD) utilizando Linked Server (ADSI)

Fala galera!
Tudo bem com vocês ?

Neste post eu vou demonstrar a vocês como consultar informações do Active Directory (AD) pelo SQL Server utilizando um simples Linked Server e a interface ADSI (Active Directory Service Interfaces). Mais uma vez, a Microsoft vem fornecendo recursos para que o SQL Server consiga se integrar com cada vez mais ferramentas e interfaces e se tornando uma solução ainda mais completa a cada versão.

No meu trabalho, utilizo esse recurso para fazer a carga diária de uma tabela no banco com todos os usuários e algumas informações deles do AD, facilitando assim, consultas e manipulação desses dados em relatórios.

Como criar o Linked Server

Para que seja possível acessar os dados do Active Directory, você precisará criar um Linked Server utilizando o provider “OLE DB Provider for Microsoft Directory Services”, conforme a tela abaixo:

Caso você seja um fã de linha de código (como eu), você também pode criar o linked server utilizando 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

Como consultar informações do Active Directory

Uma vez que você tenha criado o seu Linked Server utilizando ADSI, vamos começar a consultar as informações do AD.

Código-fonte para consultar usuários

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

Resultado

Código-fonte para consultar computadores

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]

Resultado

Limitações do ADSI

A medida que você começar a utilizar o ADSI, vai perceber que ele tem algumas limitações. Uma delas, é que ao realizar consultas que retornem mais de 901 registros, você verá a mensagem de erro abaixo:

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

Para contornar essa limitação, você pode criar uma tabela para receber os dados, realizar um TOP 901 e ir percorrendo os registros e gravando nessa tabela. Para facilitar a sua vida, já vou deixar o script pronto para vocês:

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

Uma outra limitação que ocorre nas consultas utilizando o ADSI, é que ao tentar consultar informações de campos preenchidos com dados multivalorados (Ex: postOfficeBox), você irá receber essa mensagem de erro:

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.

Infelizmente, não conheço solução para essa situação que não seja utilizar CLR (C#) ou PowerShell 🙁

Bom, é isso aí pessoal!
Espero que tenham gostado desse post.
Um abraço e até a próxima.

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

Dirceu Resende

Arquiteto de Banco de Dados e BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Veja minhas certificações

Comentários (0)

Carregando comentários…

Como criar o Linked Server

Como consultar informações do Active Directory

Código-fonte para consultar usuários

Resultado

Código-fonte para consultar computadores

Resultado

Limitações do ADSI

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

Dirceu Resende

Posts relacionados

Comentários (0)