BackgroundI»

m создание SQL для помощи в аудите безопасности; при этом информация о безопасности будет взята из различных системных баз данных и из Active Directory, и будет получен список всех аномалий (т.е. случаи, когда учетные записи закрыты в одной системе, но не в других.

Текущий код

Чтобы получить список пользователей, которые являются членами группы безопасности, я запускаю следующий 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

Проблема / ВопросI '

мне хотелось бы, чтобы этот код работал рекурсивно; то есть, если пользователь является членом группы, которая является членом указанной группы, они также должны быть включены (для полной иерархии). Кто-нибудь знает, как это сделать через SQL?

UPDATEI»

Теперь мы решили несколько проблем (не связанных с указанной проблемой, но некоторые другие проблемы, которые яд)

lastLogon выдавал ошибку. Это было потому, что версия сервера была x86. Использование базы данных x64 решило проблему.lastLogon был возвращен как число. Добавлен код для преобразования этого в DateTime2.Мне удалось переместить имя группы из жестко запрограммированной строки, сделав сам OpenQuery динамическим, поэтому в контексте OpenQuery сгенерированная строка выглядит статичной.

..

--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)