Query Active Directory

Het kan handig zijn om data uit je Active Directory (AD) op te nemen in je Datawarehouse. In mijn situatie was het handig omdat de klant graag wilde zien of een gebruiker die uit dienst was nog steeds in AD bestond. Deze koppeling had men op dit moment niet, de helpdesk moest dus handmatig van alles controleren.

De oplossing is gezocht in de integratie van AD-data in het Datawarehouse. Is het mogelijk om Active Directory te Queryen vanuit SQL Server Management Studio? Het antwoord daarop is JA. Of het moeilijk is kan ik ook een antwoord geven, NEE. Maar zoals alle dingen die makkelijk zijn, je moet even weten hoe het moet. Zoek je op Google op “Qeury Active Directory” dan zul je veel resultaten krijgen. Hoe je het precies moet doen en waar je op moet letten zal ik hieronder beschrijven.

Stap 1 Add Linked-Server
Active Directory is voor SQL Server een externe bron. Je zult dus een Linked-Server object moeten aanmaken in je SQL Server Management Studio. Daarna is het van groot belang om de Linked-Server een gebruikersnaam en wachtwoord mee te geven welke toegang heeft tot Active-Directory. Dit kan bijvoorbeeld je eigen gebruikersaccount zijn maar wellicht heb je een standaard account waarop je SQL-Services al draaien dus gebruik die dan ook!

Voer onderstaande statement-in (je hoeft niets aan te passen):

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

Ik dacht eerst dat je de laatste term ‘adsdatasource’ moest veranderen in je eigen datasource, dit hoeft dus niet!

Je hebt nu een Linked-Server aangemaakt, de connectie tussen je SQL-Server en Active Directory. Nu is het belangrijk de credentials (username/password) mee te geven aan de Linked-Server. Klik me je rechtermuis op de Linked-Server die je net hebt aangemaakt (ADSI) en klik op “Properties”. Let even op dat je sysadmin moet zijn om de Linked-Server properties in te kunnen stellen.

Klik op het “Security-Tabblad” en vul onderin de credentials in waarmee de Linked-Server toegang heeft tot Active Directory, bijvoorbeeld DOMAINUSER . Doe je dit niet zul je AD niet kunnen Queryen! Je krijgt dan een foutmelding die hierop lijkt:
“……..for execution against OLE DB provider “ADSDSOObject” for linked server “ADSI”.”

Stap 2 Query Active Directory
Nu is het tijd om een Qeury af te vuren op de Linked Server. Even ter notitie, AD werkt met zogenaamde “Pages” die hij teruggeeft als resultaat van een Qeury. AD geeft maximaal 1000 rijen terug per Qeury die je afvuurt.

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

** De opbouw van LDAP:// is:DC=ldap-server,DC=my-company,DC=com
Bijvoorbeeld: LDAP://dc=nl,dc=mycompany,dc=com

Uiteraard zit er wel meer in AD dan enkel de gebruikers. Wat je kunt doen om je dataset te verkleinen is nog enkele voorwaarden opnemen in je Query. Je wilt bijvoorbeeld alleen de AD-accounts hebben die beginnen met 000000. Je statement ziet er dan ongeveer zo uit:

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

Vervolgens kun je meerdere resultaten aan elkaar gaan plakken door gebruik te maken van een UNION. Je wilt bijvoorbeeld alle account die beginnen met een 1, daarna alle account met een 2, etc.

SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn,
sAMAccountName, cn, mail, displayName, department, telephoneNumber,
streetAddress, st, distinguishedName, physicalDeliveryOfficeName,
userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName,
whenCreated, whenChanged, userAccountControl FROM ''dc=nl,dc=mycompany,dc=com''
WHERE objectClass=''User'' AND objectCategory=''Person'' AND cn = ''000001*''')
UNION ALL
SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn,
sAMAccountName, cn, mail, displayName, department, telephoneNumber,
streetAddress, st, distinguishedName, physicalDeliveryOfficeName,
userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName,
whenCreated, whenChanged, userAccountControl FROM ''dc=nl,dc=mycompany,dc=com''
WHERE objectClass=''User'' AND objectCategory=''Person'' AND cn = ''000002*''')

Nog een opmerking op het veld UserAccountControl. Dit veld geeft de status van een AD-User aan. De volgende combinaties kwamen bij mij voor:

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

Meer informatie vind je hier.

De datumvelden die AD wegschijft zijn opgeslagen in het formaat: 100 nanosecond intervals since January 1, 1601 (UTC). Ik heb me rot gezocht hoe ik dit kan omzetten naar een datetimeveld in SQL. Uiteindelijk vond ik een handige functie:

CREATE FUNCTION [dbo].[SystemTimeToDateTime] (
@biFileTime BIGINT
)
RETURNS datetime AS
BEGIN
DECLARE @Ret AS datetime
DECLARE @ms AS BIGINT
DECLARE @MIN AS INT
DECLARE @DAY AS INT
 
SET @ms = (@biFileTime - 94354848000000000)/10000
IF (@ms < 0) RETURN CAST(0 AS datetime)
SET @MIN = CAST(@ms/60000 AS INT)
SET @DAY = CAST(@MIN/1440 AS INT)
 
SET @Ret = DATEADD(ms,
CAST(@ms%60000 AS INT),
DATEADD(mi,
CAST(@min%1440 AS INT),
DATEADD(dd,
@DAY,
CAST(0 AS datetime)
)
)
)
RETURN @Ret
END

Wanneer je meerdere domain controllers hebt zul je problemen hebben met het veld LastLogon. Als je namelijk een query draait op het hele domeinn zal hij een willekeurige Last Logon pakken. De Last Logons worden namelijk niet goed gerepliceerd. Een oplossing is de specifieke domain controllers te queryen en dan het MAXIMALE datumveld te pakken, in dit geval de meest recente Last Logon!

 FromSystemTimeToDateTime