Query Active Directory

It can be useful to include data from your Active Directory (AD) in your data warehouse. In my case it was useful because the customer wanted to see whether a user who had left the company still existed in AD. They did not have this link at the time, so the helpdesk had to check all kinds of things by hand.

The solution was to integrate AD data into the data warehouse. Can you query Active Directory from SQL Server Management Studio? The answer is YES. Is it hard? I can answer that too: NO. But as with all things that are easy, you have to know how to do it. Search Google for “Query Active Directory” and you get lots of results. Below I describe exactly how to do it and what to watch out for.

Step 1 Add Linked-Server
For SQL Server, Active Directory is an external source. So you have to create a Linked-Server object in SQL Server Management Studio. After that it is very important to give the Linked-Server a user name and password with access to Active-Directory. This can be your own user account, but maybe you have a standard account that your SQL services already run under. If so, use that one!

Run the statement below (you do not have to change anything):

1
sp_addlinkedserver 'ADSI', 'Active Directory Service
2
Interfaces', 'ADSDSOObject', 'adsdatasource

At first I thought you had to change the last term ‘adsdatasource’ to your own data source. You don’t!

You have now created a Linked-Server: the connection between your SQL Server and Active Directory. Now it is important to give the Linked-Server the credentials (username/password). Right-click the Linked-Server you just created (ADSI) and click “Properties”. Note that you must be sysadmin to set the Linked-Server properties.

Click the “Security” tab and, at the bottom, fill in the credentials the Linked-Server uses to access Active Directory, for example DOMAINUSER . If you don’t, you cannot query AD! You then get an error that looks like this:
“……..for execution against OLE DB provider “ADSDSOObject” for linked server “ADSI”.”

Step 2 Query Active Directory
Now it is time to fire a query at the Linked Server. A note first: AD works with so-called “Pages” that it returns as the result of a query. AD returns at most 1000 rows per query you fire.

1
SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn,
2
sAMAccountName, cn, mail, displayName, department, telephoneNumber,
3
streetAddress, st, distinguishedName, physicalDeliveryOfficeName,
4
userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName,
5
whenCreated, whenChanged, userAccountControl FROM ''LDAP://dc=nl,dc=mycompany,dc=com''
6
WHERE objectClass=''User'' AND objectCategory=''Person'' ')

** The structure of LDAP:// is:DC=ldap-server,DC=my-company,DC=com
For example: LDAP://dc=nl,dc=mycompany,dc=com

Of course there is more in AD than just the users. To make your dataset smaller, you can add a few conditions to your query. Say you only want the AD accounts that start with 000000. Your statement then looks roughly like this:

1
SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn,
2

3
sAMAccountName, cn, mail, displayName, department, telephoneNumber,
4
streetAddress, st, distinguishedName, physicalDeliveryOfficeName,
5
userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName,
6
whenCreated, whenChanged, userAccountControl FROM ''dc=nl,dc=mycompany,dc=com''
7
WHERE objectClass=''User'' AND objectCategory=''Person'' AND cn = ''000000*''')

Then you can glue several results together with a UNION. For example, you want all accounts that start with a 1, then all accounts that start with a 2, and so on.

1
SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn,
2
sAMAccountName, cn, mail, displayName, department, telephoneNumber,
3
streetAddress, st, distinguishedName, physicalDeliveryOfficeName,
4
userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName,
5
whenCreated, whenChanged, userAccountControl FROM ''dc=nl,dc=mycompany,dc=com''
6
WHERE objectClass=''User'' AND objectCategory=''Person'' AND cn = ''000001*''')
7
UNION ALL
8
SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn,
9
sAMAccountName, cn, mail, displayName, department, telephoneNumber,
10
streetAddress, st, distinguishedName, physicalDeliveryOfficeName,
11
userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName,
12
whenCreated, whenChanged, userAccountControl FROM ''dc=nl,dc=mycompany,dc=com''
13
WHERE objectClass=''User'' AND objectCategory=''Person'' AND cn = ''000002*''')

One more note on the UserAccountControl field. This field shows the status of an AD user. I came across these combinations:

512: Normal account
514: Account disable (512 + 2)
544: Password not required (512 + 32)
546: Disabled + Password not required
66048: Normal Account + Password never expires
66080: Normal Account + Password never expires + Password not Required

You can find more information here.

The date fields that AD writes are stored in the format: 100 nanosecond intervals since January 1, 1601 (UTC). I searched like mad for a way to convert this to a datetime field in SQL. In the end I found a handy function:

1
CREATE FUNCTION [dbo].[SystemTimeToDateTime] (
2
@biFileTime bigint
3
)
4
RETURNS datetime AS
5
BEGIN
6
DECLARE @Ret AS datetime
7
DECLARE @ms AS bigint
8
DECLARE @min AS Int
9
DECLARE @day AS Int
10

11
SET @ms = (@biFileTime - 94354848000000000)/10000
12
IF (@ms < 0) Return CAST(0 AS datetime)
13
SET @min = CAST(@ms/60000 AS int)
14
SET @day = CAST(@min/1440 AS int)
15

16
SET @Ret = DATEADD(ms,
17
CAST(@ms%60000 AS int),
18
DATEADD(mi,
19
CAST(@min%1440 AS int),
20
DATEADD(dd,
21
@day,
22
CAST(0 as datetime)
23
)
24
)
25
)
26
RETURN @Ret
27
END

When you have more than one domain controller, you will have problems with the LastLogon field. When you run a query against the whole domain, it takes a random Last Logon, because the Last Logons are not replicated properly. One solution is to query the specific domain controllers and then take the MAXIMUM date field, in this case the most recent Last Logon!

FromSystemTimeToDateTime