Abfrage der AD-Gruppenmitgliedschaft Rekursiv Durch SQL

Hintergrund

Ich bin momentan dabei, einige SQL zu unterstützen, die überwachung der Sicherheit; dies wird von Sicherheitsinformationen aus verschiedenen Systemen, Datenbanken und Active Directory und erzeugt eine Liste aller Anomalien (D. H. Fälle, in denen Konten geschlossen in einem system, in anderen aber nicht.

Aktuellen Code

Erhalten Sie eine Liste der Benutzer, die Mitglied der Sicherheitsgruppe, die ich führen Sie die folgenden SQL:

if not exists(select 1 from sys.servers where name = 'ADSI') 
    EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

SELECT sAMAccountName, displayName, givenName, sn, isDeleted --, lastLogonTimestamp --, lastLogon (Could not convert the data value due to reasons other than sign mismatch or overflow.)
FROM OPENQUERY(ADSI
, 'SELECT sAMAccountName, displayName, givenName, sn, isDeleted
FROM ''LDAP://DC=myDomain,DC=myCompany,DC=com''
WHERE objectCategory = ''Person''
AND objectClass = ''user'' 
AND memberOf = ''CN=mySecurityGroup,OU=Security Groups,OU=UK,DC=myDomain,DC=myCompany,DC=com''
')
order by sAMAccountName

Problem /Frage

Ich würde gerne diesen code, um in der Lage zu arbeiten rekursiv, d.h. ist ein Benutzer Mitglied einer Gruppe ist, die Mitglied der angegebenen Gruppe, sollten Sie enthalten sein, auch (für die vollständige Hierarchie). Weiß jemand, wie dies zu tun ist durch SQL?

UPDATE

Habe ich jetzt behoben, ein paar Probleme (nicht im Zusammenhang mit dem zitierten problem, aber einige andere Probleme, die ich hatte).

lastLogon war throwning ein Fehler. Dies war, weil die server-version war, x86. Mit einem x64-Datenbank ist das problem gelöst.
lastLogon wurde als Zahl zurückgegeben. Hinzugefügt einige code zu konvertieren, das in DateTime2.
Ich war in der Lage sich zu bewegen Sie den Namen der Gruppe, aus der eine hart codierte Zeichenfolge, indem OpenQuery sich dynamisch, so dass im Rahmen der OpenQuery-die generierte Zeichenfolge sieht statisch.

--create linked server
if not exists(select 1 from sys.servers where name = 'ADSI')
begin
    --EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
    EXEC master.dbo.sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible',  @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation',  @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation',  @optvalue=N'true'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
end


declare @path nvarchar(1024) = 'DC=myDomain,DC=myCompany,DC=com'
declare @groupCN nvarchar(1024) = 'CN=My Security Group,OU=Security Groups,OU=UK,' + @path
, @sql nvarchar(max)

--construct the query we send to AD
set @sql = '
SELECT sAMAccountName, displayName, givenName, sn, isDeleted, lastLogon
FROM ''LDAP://' + replace(@path,'''','''''') + '''
WHERE objectCategory = ''Person''
AND objectClass = ''user'' 
AND memberOf = ''' + replace(@groupCN,'''','''''') + '''
'

--now wrap that query in the outer query
set @sql = 'SELECT sAMAccountName, displayName, givenName, sn, isDeleted
case     
    when cast([lastLogon] as bigint) = 0 then null
    else dateadd(mi,(cast([lastlogon] as bigint) /600000000), cast(''1601-01-01'' as datetime2)) 
  end LastLogon
FROM OPENQUERY(ADSI, ''' + replace(@sql,'''','''''') + ''')
order by sAMAccountName'

--now run it
exec(@sql)

ps. Ich habe auch ein paar sub-Fragen auf dieser - noch nicht hier veröffentlichen, wie ich arbeite immer noch über Google, aber dachte, ich würde erwähnen Sie in den Kommentaren, falls jemand helfen kann. Weiß jemand, wie man die lastLogon Wert? Weiß jemand, wie man die SQL dynamisch (d.h. so kann ich eine variable verwenden, für die Sicherheit der Gruppe statt hart codieren, es in der Abfrage)? Vielen Dank im Voraus an alle.
lastLogon Problem war nach unten in die DB (server, nicht client) als x86. Diese Funktionalität funktioniert nur auf x64.
Kleinere Probleme behoben, siehe UPDATE oben für neuen code. Formulierte Frage ist allerdings noch offen.

InformationsquelleAutor JohnLBevan | 2012-12-17

Obwohl dies eine alte post, Google gerne immer noch werfen Sie es an die Spitze der Ergebnisse, so wie ich kämpfte mit diesem problem sehr viel, ich wollte nach meinen Erkenntnissen/Lösung, mit Kredit Riverway für mich immer auf dem richtigen Weg.

Erstellen einer Gespeicherten Prozedur:

CREATE PROCEDURE [dbo].[GetLdapUserGroups]
    (
    @LdapUsername NVARCHAR(max)
    )
AS
BEGIN
DECLARE @Query NVARCHAR(max), @Path NVARCHAR(max)

SET @Query = '
    SELECT @Path = distinguishedName
    FROM OPENQUERY(ADSI, ''
        SELECT distinguishedName 
        FROM ''''LDAP://DC=DOMAIN,DC=COM''''
        WHERE 
            objectClass = ''''user'''' AND
            sAMAccountName = ''''' + @LdapUsername + '''''
    '')
'

EXEC SP_EXECUTESQL @Query, N'@Path NVARCHAR(max) OUTPUT', @Path = @Path OUTPUT 

  SET @Query = '
    SELECT cn AS [LdapGroup]
    FROM OPENQUERY (ADSI, ''<LDAP://DOMAIN.COM>;
    (&(objectClass=group)(member:1.2.840.113556.1.4.1941:= ' + @Path + '));
    cn, adspath;subtree'')
    ORDER BY cn;
'

EXEC SP_EXECUTESQL @Query
END

Dann, rufen Sie Ihren SP nur durch die übergabe der Benutzername ist:

DECLARE @UserGroup table (LdapGroup nvarchar(max))
INSERT INTO @UserGroup exec Datamart.dbo.GetLdapUserGroups @LdapUser

Bin ich dann über einen hash-Tabelle, um richtig passen die AD-Gruppe auf dem SQL-Daten und was der Endbenutzer sehen sollte.

DECLARE @RptPermissions table (ldapGroup nvarchar(max),scholarshipCode nvarchar(50),gender nvarchar(2))
INSERT INTO @RptPermissions VALUES('EMP_Enrollment_Admissions','ALL','MF')

In meinem Fall, ich bin mit diesem ziehen Sie, die SSRS-user-variable übergeben und in der Abfrage für die Auswahl der Datensätze basiert auf AD-group-Mitgliedschaft.

;WITH CTE_Permissions AS
(
    SELECT
        p.scholarshipCode
        ,p.gender
    FROM @UserGroup AS g
    JOIN @RptPermissions AS p ON
        g.ldapGroup = p.ldapGroup
)

... Später in der Abfrage

JOIN CTE_Permissions AS p ON
         s.SCHOLARSHIP_ID = p.scholarshipCode
         OR p.scholarshipCode = 'ALL'

Hoffe, das hilft.

InformationsquelleAutor Apothis

So?

--Get all members of a group
SELECT cn,AdsPath 
FROM OPENQUERY (ADSI, '<LDAP: dc="corp,dc=mycorp,dc=com">;(&(objectCategory=person)(memberOf:1.2.840.113556.1.4.1941:=CN=Administrators,CN=Builtin,DC=corp,DC=mycorp,DC=com));cn, adspath;subtree')
ORDER BY cn; 

--get all groups a user is a member of
SELECT cn,AdsPath
FROM OPENQUERY (ADSI, '<LDAP: dc="corp,dc=mycorp,dc=com">;(&(objectClass=group)(member:1.2.840.113556.1.4.1941:=CN=John Doe,OU=Developers,OU=Staff,DC=corp,DC=mycorp,DC=com));cn, adspath;subtree')
ORDER BY cn;

Sehen http://msdn.microsoft.com/en-us/library/aa746475(VS.85).aspx für die rekursive Suche Bedingungen.

InformationsquelleAutor Riverway

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.