SQL Server采用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节点:

SQL Server 1

查阅属性

SQL Server 2

SQL Server 3

现今我们就足以本着这些 链接服务器 举行查询了,比如如下查询

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

这里是结果

SQL Server 4

诸如此类大家就可以把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 音信。运行这么些蕴藏过程的结果如下:

SQL Server 5

时至前几天,我们所需要的信息就完整了。前面就是简单的T-SQL把这么些数据放到表里面就OK了。

一旦您需要AD中所有的习性名称,请参见以下链接:
http://www.kouti.com/tables/userattributes.htm


 

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

SQL Server 6

相关文章