Notes on SQL

Random articles from a puzzled DBA

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]

EXEC master.dbo.sp_addlinkedserver @server = N'MyADDataSource',
                                   @srvproduct=N'Active Directory Services',

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyADDataSource',


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 ‘’ then it has to be split across three ‘DC=’ parameters, as in the example above.
Trying ‘’ 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”.

Very helpful.

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


SELECT @sChar= CHAR(@nAsciiValue)

EXEC master..xp_sprintf @str2 OUTPUT, 'SAMAccountName = ''''%s*'''''' )', @sChar
set @cmdstr = @str1 + @str2;

EXEC( @cmdstr )

SELECT @nAsciiValue = @nAsciiValue + 1


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.



LDAP Attributes

UserAccountControl Values

Various filters for LDAP

LDAP Syntax Filters

2 thoughts on “LDAP – Using SQL to get Active Directory details – Basic Examples

  1. Nikos (DBA) says:

    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, 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!

    1. Steve Hall says:

      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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: