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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 |
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
sql server tsql select query consultar ler retrieve get list ad active directory users groups computers
Portuguese 
English
Seria possivel alterar no lugar de apenas listar, alterar usuarios no Ad via um query do sql server? ou isso serve apenas para leitura?
Excelente post.
Tenho uma dúvida, você sabe qual o campo que representa no AD o status dos usuários em Ativo ou Inativo, procurei em vários lugares e não obtive esta resposta.
Utiliza o campo userAccountControl
Os códigos correspondente a este campo está no link:
https://nvlan.com.br/comunidade/utilizando-o-atributo-useraccountcontrol/
Exemplo:
512 – Enable Account
514 – Disable account
576 – Enable Account + Passwd_cant_change
544 – Account Enabled – Require user to change password at first logon
4096 – Workstation/server
66048 – Enabled, password never expires
66050 – Disabled, password never expires
262656 – Smart Card Logon Required
532480 – Domain controller