Notes on SQL

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”.

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

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.

 

Resources

LDAP Attributes

UserAccountControl Values

Various filters for LDAP

LDAP Syntax Filters