最近在某个项目中，需要针对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://*.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研发运维一体化的信息
