使用T-SQL进展活动目录查询_SQL

使用T-SQL进行活动目录查询

最近在某个项目中，需要针对TFS的用户按照所属的AD组来进行数据分析，但发现TFS中并没有存储用户所属的组信息，故考虑直接从AD中提取这个信息并存放在SQL Server的数据库表里面去。

以下就是我的测试过程。

首先运行以下查询在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, [email protected] 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研发运维一体化的信息

1楼Spraus: 这种方式貌似受限于AD的IO限制，sql2008下超出限制只是查出一部分，到了2012里超限之后直接就报错了。