¡Hola, chicos!
¿Estás bien?

En esta publicación, le demostraré cómo consultar información de Active Directory (AD) a través de SQL Server utilizando un servidor vinculado simple y la interfaz ADSI (Interfaces de servicio de Active Directory). Una vez más, Microsoft ha ido aportando recursos para que SQL Server pueda integrarse con cada vez más herramientas e interfaces, convirtiéndose con cada versión en una solución aún más completa.

En mi trabajo utilizo este recurso para cargar una tabla diaria en la base de datos con todos los usuarios y parte de su información de AD, facilitando así las consultas y manipulación de estos datos en informes.

Cómo crear un servidor vinculado

Para poder acceder a los datos de Active Directory, deberá crear un servidor vinculado utilizando el “Proveedor OLE DB para servicios de directorio de Microsoft”, como se muestra en la siguiente pantalla:

Si eres fanático de las líneas de código (como yo), también puedes crear el servidor vinculado usando 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

Cómo consultar información de Active Directory

Una vez que haya creado su servidor vinculado usando ADSI, comencemos a consultar la información de AD.

Código fuente para consultar a los usuarios.

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 fuente para consultar computadoras.

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

Limitaciones de ADSI

Cuando empiece a utilizar ADSI, se dará cuenta de que tiene algunas limitaciones. Una de ellas es que al realizar consultas que devuelvan más de 901 registros, verás el siguiente mensaje de error:

Mensaje 7330, nivel 16, estado 2, línea 1 No se puede recuperar una fila del proveedor OLE DB "ADSDSOObject" para el servidor vinculado "ADSI".

Para sortear esta limitación, puede crear una tabla para recibir los datos, realizar un TOP 901, revisar los registros y escribir en esta tabla. Para hacerte la vida más fácil, te dejo el guión listo:

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

Otra limitación que ocurre en las consultas que utilizan ADSI es que al intentar consultar información de campos llenos de datos multivalor (Ej: postOfficeBox), recibirá este mensaje de error:

Mensaje 7346, Nivel 16, Estado 2, Línea 1
No se pueden obtener los datos de la fila del proveedor OLE DB "ADsDSOObject" para el servidor vinculado "ADSI". No se pudo convertir el valor de los datos por motivos distintos a la falta de coincidencia de signos o el desbordamiento.

Desafortunadamente, no conozco otra solución para esta situación que no sea usar CLR (C#) o PowerShell 🙁

Bueno, ¡eso es todo amigos!
Espero que te haya gustado esta publicación.
Un abrazo y hasta la próxima.

servidor sql tsql seleccionar consulta consultar leer recuperar obtener lista ad directorio activo usuarios grupos computadoras

servidor sql tsql seleccionar consulta consultar leer recuperar obtener lista ad directorio activo usuarios grupos computadoras