Recently I have had to extract user’s details from Active Directory (AD) for certain security groups. Having looked through a slew of internet resources it is obvious that the work required to do this has changed little over many years – and it looks like is was deliberately designed to make it difficult. I’d like to think it wasn’t but, that is how it looks.
Firstly, depending upon the version of various bits and pieces an LDAP query will return either 901 rows or 1000 rows, before an error is raised. Also unlike SQL, you can’t simply extract all columns from a table with an asterisk in order to ascertain what is available. If you don’t know what a column is called then you’ll never get to see it.
There are a handful of methods for querying AD but the method I am using here is with a Linked Server and OPENQUERY.
The Linked Server
For the linked server you will need to know what to connect to (probably a domain name or possibly a domain controller) and an account with password that will allow you access. In my case this information was supplied by one of the Infrastructure Engineers.
USE [master] GO EXEC master.dbo.sp_addlinkedserver @server = N'MyADDataSource', @srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject', @datasrc=N'test.com' GO EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyADDataSource', @useself=N'False', @locallogin=NULL, @rmtuser=N'ADAccount', @rmtpassword='<password>' GO
Extracting User Details
The basic code to extract users from an AD server:
SELECT objectSID, SAMAccountName, sn, mail, distinguishedName FROM OPENQUERY( MyADDataSource, 'SELECT sn, SAMAccountName, objectSID, userAccountControl, mail, distinguishedName FROM ''LDAP://DC=MyDomain,DC=co,DC=uk ''WHERE objectCategory = ''Person''' )
If you’re looking for users within the AD for ‘MyDomain.co.uk’ then it has to be split across three ‘DC=’ parameters, as in the example above.
Trying ‘DC=MyDomain.co.uk’ or just ‘DC=MyDomain, DC=co’ will not work.
objectSID is the ID of this account within AD. It should never change, whereas a person’s name or email can change for a variety of reasons.
distinguishedName also uniquely identifies the object (row) in question and can be used to locate members of specified groups.
Extracting AD Groups
The code to extract Group details is almost the same as the code for User details – just change the ‘Person’ parameter to ‘Group’:
SELECT objectSID, SAMAccountName, sn, mail, distinguishedName FROM OPENQUERY( MyADDataSource, 'SELECT sn, SAMAccountName, objectSID, userAccountControl, mail, distinguishedName FROM ''LDAP://DC=MyDomain,DC=co,DC=uk ''WHERE objectCategory = ''Group''' )
Extracting Users for a Specified Group
Having extracted a list of all groups you can now identify users within a specified group. Within the results of the Group enquiry use either the ‘displayName’ or ‘SAMAccountName’ column to identify your group and copy the column data for ‘distinguishedName’. This data will be used to check a user is a member of that Group.
select objectSID, SAMAccountName, sn, mail, distinguishedName, userAccountControl from openquery (MyADDataSource,'SELECT objectSID, SAMAccountName, sn, mail, distinguishedName, userAccountControl FROM ''LDAP://DC=MyDomain,DC=co,DC=uk '' WHERE objectCategory = ''person'' AND objectClass = ''user'' AND memberOf=''CN=Test_Group,OU=Internal Testing Group,OU=Groups,DC=MyDomain,DC=co,DC=uk''')
The contents of the ‘memberOf’ parameter were copied from the ‘distinguishedName’ of the required Group from the earlier query (extracting the Group details). If the User is part of the Group that has that specified ‘distinguishedName’ then it should be selected. Obviously I have invented the contents of that column and yours may have more elements to it, or less.
Too Many Results!
If the query returns too many rows (as stated earlier) it will fail with a fairly useless error message:
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider “ADsDSOObject” for linked server “MyADDataSource”.
To get around this you can construct the query as a WHILE loop, which extracts Groups/Users (depending upon the entry within ‘objectcategory’) where the name (‘SAMAccountName’) starts with one letter of the alphabet at a time:
/* AD is limited to send 1000 records in one batch. In an ADO interface you can define this batch size, not in OPENQUERY. Because of this limitation, we just loop through the alphabet. */ DECLARE @cmdstr varchar(1024) DECLARE @nAsciiValue smallint DECLARE @sChar char(1) DECLARE @str1 varchar(max); DECLARE @str2 varchar(1024); SELECT @nAsciiValue = 65 set @str1 = 'SELECT cn, distinguishedName, SAMAccountName FROM OPENQUERY( MyADDataSource, ''SELECT cn, SAMAccountName, distinguishedName FROM ''''LDAP://DC=MyDomain,DC=co,DC=uk ''''WHERE objectCategory = ''''group'''' AND '; set nocount on; WHILE @nAsciiValue < 91 BEGIN SELECT @sChar= CHAR(@nAsciiValue) EXEC master..xp_sprintf @str2 OUTPUT, 'SAMAccountName = ''''%s*'''''' )', @sChar set @cmdstr = @str1 + @str2; EXEC( @cmdstr ) SELECT @nAsciiValue = @nAsciiValue + 1 END
Due to the limitation of parameter size within xp_sprintf I have split the SQL into two varchar entries, otherwise the SQL string can be truncated and execution fails.
2 thoughts on “LDAP – Using SQL to get Active Directory details – Basic Examples”
Just wanted to say thank you for this article. I’ve written a framework for extracting and analysing all instance and database level security metadata (principles, securables, and permissions). The missing part for me was extracting all AD group memberships along with nested group memberships, so that I could accurately map permissions inheritance. I want to use a graph database to show the relationships between nested groups and eventually the entire currently granted permissions structure.
I’d come across so many potential solutions that ultimately turned into dead-ends. xp_logininfo for instance only shows users within a group, it does not show nested groups. Also, my powershell-fu is weak so my friends suggestion to get to grips with https://dbatools.io/, although correct and worthwhile, is a bit outside my comfort zone for now. And then there is the sheer alieness of AD lingo and points of reference. I’d previously set up a Linked Server to my home lab domain, but my querying attempts were getting nowhere.
I gave the whole project a break for a few months, changed jobs, settled in and after a few weeks I decided to tackle this again. This time round I came across your article and started working through it from scratch and after a few false starts, squeeled a bit for joy when i saw my query return all group members, including the nested groups.
Maybe simple stuff at root, but sometimes the brainfog is strong. As if often the case, a fresh head and some fresh help (your article) and I’m now in a good place to map AD nested groups across the domain and link them to the existing permissions hierarchy within the SQL estate.
Thank you muchly sir!
I’m glad it helped. It was certainly an interesting project at the time. As you say – simple at root, but the documentation out there was less than ideal. Hence this article.