转载

使用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节点：

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

查看属性

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

现在我们就可以针对这个 链接服务器 进行查询了，比如如下查询

-- 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)

这里是结果

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

这样我们就可以把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进行活动目录查询

至此，我们所需要的信息就完整了。后面就是简单的T-SQL把这些数据放到表里面就OK了。

如果你需要AD中所有的属性名称，请参考以下链接：

http://www.kouti.com/tables/userattributes.htm

请关注微信公众号 devopshub ，获取更多关于DevOps研发运维一体化的信息

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

正文到此结束

所属分类：编程技术

本文标签： db 数据 SQL Server ACE tab UI provider Word sql list http 测试数据库 Select DDL ldap find DOM src 服务器 CTO dist 目录 https Google example ip IDE dataSource
版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
本文海报： 生成海报一生成海报二

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

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

热门推荐

相关文章

说给你听

本文目录

随机标签

书籍教程

近期评论

网站信息

其他链接

关于本站

问题交流