使用T-SQL進行活動目錄查詢

最近在某個項目中，需要針對TFS的用戶按照所屬的AD組來進行數據分析，但發現TFS中並沒有存儲用戶所屬的組信息，故考慮直接從AD中提取這個信息並存放在SQL Server的資料庫表裡面去。

經過一番GOOGLE後，找到瞭如下相關的資料：
https://community.spiceworks.com/how_to/27494-create-a-sql-linked-server-to-adsi
https://www.mssqltips.com/sqlservertip/2580/querying-active-directory-data-from-sql-server/
http://stackoverflow.com/questions/1766061/tsql-how-to-get-a-list-of-groups-that-a-user-belongs-to-in-active-diretory

以下就是我的測試過程。

首先運行以下查詢在SQL SERVER中創建 鏈接伺服器

-- create a linked server
 EXEC sp_addlinkedserver @server = 'ADSI', @srvproduct = 'Active Directory Services 2.5', @provider = 'ADSDSOObject', @datasrc = 'adsdatasource'
 -- add AD accessiable account
 EXEC sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'False', @locallogin = 'sa', @rmtuser = '{domain}\{user}', @rmtpassword = '{passowrd}'

註意以上命令中的用戶名和密碼需要更換為對AD有訪問許可權的用戶。運行完成後可以在 伺服器對象 | 鏈接伺服器中找到所創建的ADSI節點：

查看屬性

現在我們就可以針對這個 鏈接伺服器 進行查詢了，比如如下查詢

-- Query AD user properties
 SELECT * FROM OpenQuery(ADSI, 'SELECT displayName, sAMAccountName, givenName, sn, userAccountControl
 FROM ''LDAP://devopshub.local/DC=devopshub,DC=local'' where objectClass = ''User''')
 WHERE (sn is not null) and (givenName is not null)

這裡是結果

這樣我們就可以把AD中的數據存放到SQL SERVER的數據表中，並根據displayName或者其他欄位與TFS的DataWarehouse中的用戶信息進行join，得出那些只有在AD中才能找到的信息。

但是使用T-SQL查詢用戶所屬的組信息並不容易，我知道的最好的解決辦法是使用以下查詢建立一個存儲過程，並通過它來獲取某個用戶的組信息。這主要是因為userMembership在AD中是一個多值數據類型，直接用T-SQL來處理還是比較困難的。

CREATE PROCEDURE dbo.Get_ADGroups_ForUser
 (
 @Username NVARCHAR(256)
 )
 AS
 BEGIN
 DECLARE @Query NVARCHAR(1024), @Path NVARCHAR(1024)

 -- Find the fully qualified CN e.g: CN=Beau Holland,OU=Users,OU=Australia,OU=NSO, OU=Company,DC=Domain,DC=local
 -- replace "LDAP://DC=Domain,DC=local" with your own domain
 SET @Query = '
 SELECT @Path = distinguishedName
 FROM OPENQUERY(ADSI, ''
 SELECT distinguishedName
 FROM ''''LDAP://DC=devopshub,DC=local''''
 WHERE
 objectClass = ''''user'''' AND
 sAMAccountName = ''''' + @Username + '''''
 '')
 '
 EXEC SP_EXECUTESQL @Query, N'@Path NVARCHAR(1024) OUTPUT', @Path = @Path OUTPUT
 
 -- get all groups for a user
 -- replace "LDAP://DC=Domain,DC=local" with your own domain
 
SET @Query = '
 SELECT cn,AdsPath
 FROM OPENQUERY (ADSI, ''<LDAP://DC=devopshub,DC=local>;(&(objectClass=group)(member:1.2.840.113556.1.4.1941:=' + @Path +'));cn, adspath;subtree'')'

 EXEC SP_EXECUTESQL @Query
 
 END
 GO

  -- Example of usage
 Get_ADGroups_ForUser 'leixu' --AccountName

同樣，你需要替換你自己的AD 信息。運行這個存儲過程的結果如下：

至此，我們所需要的信息就完整了。後面就是簡單的T-SQL把這些數據放到表裡面就OK了。

如果你需要AD中所有的屬性名稱，請參考以下鏈接：
http://www.kouti.com/tables/userattributes.htm

請關註微信公眾號 devopshub，獲取更多關於DevOps研發運維一體化的信息