T-SQL Active Directory abfragen

-- Voraussetzung: Der SQL Server-Dienst muss über ausreichende Rechte im AD verfügen. 
-- Typische LDAP-Basis: LDAP://DC=contoso,DC=com 
-- Filter (z.B. (objectClass=user)) holt alle Benutzerobjekte.

-- Linked Server "ADSI" erstellen (Provider: ADSDSOObject) 
EXEC sp_addlinkedserver  
@server = 'ADSI',  
@srvproduct = 'Active Directory Services',  
@provider = 'ADSDSOObject',  
@datasrc = 'LDAP://DC=contoso,DC=com'; 
 
-- Optional: Mapping für die aktuelle Anmeldung (für integrierte Sicherheit) 
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'true'; 
 
-- Abfrage der Domänenbenutzer über den Linked Server 
SELECT *  
FROM OPENQUERY(ADSI,  
'SELECT Name, sAMAccountName, mail, distinguishedName, title, department 
FROM ''LDAP://DC=contoso,DC=com'' 
WHERE objectClass = ''user'' AND objectCategory = ''person''');

-- Basisabfrage: Alle Benutzer mit Vor- und Nachname, E-Mail, Login 
SELECT  
sAMAccountName AS Loginname, 
Name AS Vollname, 
mail AS Email, 
telephoneNumber AS Telefon, 
department AS Abteilung 
FROM OPENQUERY(ADSI,  
'SELECT sAMAccountName, Name, mail, telephoneNumber, department 
 FROM ''LDAP://DC=contoso,DC=com'' 
 WHERE objectClass = ''user'' AND objectCategory = ''person''  
 AND sAMAccountName IS NOT NULL') 
ORDER BY Name; 
 
-- Ausgabe als Tabelle – kann direkt mit lokalen Tabellen verknüpft werden

-- Nur Benutzer, die nicht deaktiviert sind (userAccountControl: 512 = aktiv) 
SELECT sAMAccountName, Name, mail, userAccountControl 
FROM OPENQUERY(ADSI,  
'SELECT sAMAccountName, Name, mail, userAccountControl 
 FROM ''LDAP://DC=contoso,DC=com'' 
 WHERE objectClass = ''user''  
 AND (userAccountControl = 512 OR userAccountControl = 514) 
 AND sAMAccountName = ''mustermann'''); 
 
-- Alle Benutzer einer bestimmten Organisationseinheit (OU) – Path direkt angeben 
SELECT Name, sAMAccountName, mail 
FROM OPENQUERY(ADSI,  
'SELECT Name, sAMAccountName, mail 
 FROM ''LDAP://OU=IT,DC=contoso,DC=com'' 
 WHERE objectClass = ''user''');

-- Alle direkten Mitglieder einer Gruppe (z.B. "Domänen-Admins") 
SELECT member AS MemberDN 
FROM OPENQUERY(ADSI,  
'SELECT member  
 FROM ''LDAP://CN=Domain Admins,CN=Users,DC=contoso,DC=com'' 
 WHERE objectClass = ''group'''); 
 
-- Mitglieder auflösen: Mit einer zweiten OPENQUERY die Benutzerdaten laden 
-- (Dazu werden die ausgegebenen DNs in eine IN-Klausel überführt – praktisch über eine Tabelle oder dynamisches SQL) 
 
-- Beispiel für einen festen Benutzer: Mitgliedschaft prüfen 
SELECT * 
FROM OPENQUERY(ADSI,  
'SELECT sAMAccountName, memberOf  
 FROM ''LDAP://CN=maxmustermann,CN=Users,DC=contoso,DC=com'' 
 WHERE objectClass = ''user''');

-- Paging und Filter verbessern Performance 
-- Tokenbasierte Suche (z.B. sAMAccountName wie 'adm%') 
SELECT sAMAccountName, Name 
FROM OPENQUERY(ADSI,  
'SELECT sAMAccountName, Name 
 FROM ''LDAP://DC=contoso,DC=com'' 
 WHERE objectClass = ''user''  
 AND sAMAccountName = ''admin*'''); 
 
-- Query-Timeout für Linked Server anpassen (falls nötig) 
EXEC sp_serveroption 'ADSI', 'query timeout', 300; 
 
-- Sicherheit: Vermeiden Sie xp_cmdshell mit Directory Services – nutzen Sie den Linked Server. 
-- Benötigte Berechtigung: Das SQL Server-Servicekonto muss Leseberechtigung in der Domäne haben.

-- Aktivieren von xp_cmdshell nur temporär (erfordert Sysadmin-Rechte) 
EXEC sp_configure 'show advanced options', 1; RECONFIGURE; 
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE; 
 
-- PowerShell-Befehl, der AD-User als CSV exportiert und in Tabelle lädt 
CREATE TABLE #ADUsers (SamName VARCHAR(100), FullName NVARCHAR(200), Email VARCHAR(200)); 
INSERT INTO #ADUsers 
EXEC xp_cmdshell 'powershell -Command "Get-ADUser -Filter * -Properties Name,SamAccountName,mail | Select SamAccountName,Name,mail | ConvertTo-Csv -NoTypeInformation"'; 
 
-- Nach Gebrauch xp_cmdshell wieder deaktivieren 
EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE; 
 
-- Vorsicht: Diese Methode ist weniger performant und stellt ein Sicherheitsrisiko dar.