SQL Server 使用ADSI执行分布式查询ActiveDorectory对象

发布时间：2020-05-23 21:37:05 所属栏目：MsSql 来源：互联网

导读：SQL Server 使用ADSI执行分布式查询ActiveDorectory对象

感兴趣的小伙伴，下面一起跟随脚本之家 jb51.cc的小编两巴掌来看看吧！

Step 1：Creating a Linked Server.
EXEC sp_addlinkedserver 'ADSI','Active Directory Services 2.5','ADSDSOObject','adsdatasource'
Step 2：Creating a SQL Server Authenticated Login
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'ADSI',@locallogin = NULL,@useself = N'False',@rmtuser = N'domainAccount',@rmtpassword = N'Password'
对于 SQL Server 授权登录，可以使用sp_addlinkedsrvlogin 系统存储过程配置用于连接到目录服务的适当的登录/密码.
参考这里: http://blogs.msdn.com/euanga/archive/2007/03/22/faq-how-do-i-query-active-directory-from-sql-server.aspx
如果SQLServer使用Windows 授权登录，只需自映射就足以通过使用 SQL Server 安全委托来访问AD。简单点说就是直接运行第三步语句即可.
Step 3：Querying the Directory Service.

代码如下:

 
-- Query for a list of User entries in an OU using the SQL query dialect 
select convert(varchar(50),[Name]) as FullName,convert(varchar(50),Title) as Title,TelephoneNumber) as PhoneNumber 
from openquery(ADSI,'select Name,Title,TelephoneNumber 
from ''LDAP://OU=Directors,OU=Atlanta,OU=Intellinet,DC=vizability,DC=intellinet,DC=com'' 
where objectClass = ''User''') 
-- Query for a list of Group entries in an OU using the SQL query dialect 
select convert(varchar(50),[Name]) as GroupName,[Description]) GroupDescription 
from openquery(ADSI,Description 
from ''LDAP://OU=VizAbility Groups,DC=com'' 
where objectClass = ''Group''')

引用：
http://msdn2.microsoft.com/en-us/library/aa772380.aspx
http://www.atlantamdf.com/presentations/AtlantaMDF_111201_examples.txt

说明：但是这样默认查询出来的是1000个对象.怎么办呢?
方法一,通过字母来循环.见以下:

代码如下:

 
CREATE TABLE #tmpADUsers 
( employeeId varchar(10) NULL,SAMAccountName varchar(255) NOT NULL,email varchar(255) NULL) 
GO 
/**//* AD is limited to send 1000 records in one batch. In an ADO interface you can define this batch size,not in OPENQUERY. 
Because of this limitation,we just loop through the alphabet. 
*/ 
DECLARE @cmdstr varchar(255) 
DECLARE @nAsciiValue smallint 
DECLARE @sChar char(1) 
SELECT @nAsciiValue = 65 
WHILE @nAsciiValue < 91 
BEGIN 
SELECT @sChar= CHAR(@nAsciiValue) 
EXEC master..xp_sprintf @cmdstr OUTPUT,'SELECT employeeId,SAMAccountName,Mail FROM OPENQUERY( ADSI,''SELECT Mail,employeeID FROM ''''LDAP://dc=central,dc=mydomain,dc=int''''WHERE objectCategory = ''''Person'''' AND SAMAccountName = ''''%s*'''''' )',@sChar 
INSERT #tmpADUsers 
EXEC( @cmdstr ) 
SELECT @nAsciiValue = @nAsciiValue + 1 
END 
DROP TABLE #tmpADUsers

以上方法源自于：http://www.sqlservercentral.com/Forums/Topic231658-54-1.aspx#bm231954

我推荐的方法:在微软搜索到的.如何通过 NTDSUtil为服务器修改限制 maxPageSize

Modifying policy settings

资料来源:

http://support.microsoft.com/kb/315071/en-us

http://support.microsoft.com/?scid=kb%3Bzh-cn%3B299410&x=16&y=10

如何使用SQL查询活动目录对象语法: http://www.microsoft.com/china/technet/community/columns/scripts/sg0505.mspx#EMBAC

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!