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:
|
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 |
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
|
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 |
Result
Source code for consulting computers
|
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] |
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 “ADDSSOObject” 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:
|
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 |
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
Is it possible to change instead of just listing, changing users in Ad via a sql server query? or is it read only?
Excellent post.
I have a question, you know which field represents in AD the status of users in Active or Inactive, I searched in several places and did not get this answer.
Uses the userAccountControl field
The codes corresponding to this field are on the link:
https://nvlan.com.br/comunidade/utilizando-o-atributo-useraccountcontrol/
Example:
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