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