转载

使用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进行活动目录查询

使用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进行活动目录查询

正文到此结束
Loading...