使用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研發運維一體化的資訊