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

培训内容：
本期培训主要目的：从域中读取基本信息的一种方法，以及读取域属性的工具：adsiedit.msc
内容实现方法：
 1.安装域控制器  具体安装方法可参考百度
        2.设置参数 执行以下代码
--添加连接服务器 ADSI:你要设置的服务器名称；后面的照搬[本人尚不懂]
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
---设置访问用户名密码 @rmtuser：设置域的用户名  @rmtpassword：设置域的密码
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'ADSI', @locallogin = NULL , @useself = N'True',
@rmtuser = N'infoeyechina.com\administrator', @rmtpassword = N'pass@word2'
参考地址：http://www.alixixi.com/program/a/2010062662234.shtml

取域属性的工具：adsiedit.msc:
ADSI Edit (AdsiEdit.msc) 是一个 Microsoft Windows? Server 工具，可用于通过 Active Directory 服务界面 (ADSI) 协议查看和编辑原始 Active Directory? 目录服务属性
使用方法：
对 adsiedit.dll 运行 regsvr32 命令
直接双击adsiedit.msc打开。

下载地址：http://download.csdn.net/detail/jycjyc/3980331

===============================参考他人===============================================================

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'domain\Account', @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,
convert(varchar(50), 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,
convert(varchar(50), [Description]) GroupDescription
from openquery(ADSI,
'select Name, Description
from ''LDAP://OU=VizAbility Groups,DC=vizability,DC=intellinet,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, SAMAccountName, 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

1.	Click Start, and then click Run.
2.	In the Open text box, type ntdsutil, and then press ENTER. To view help at any time, type ? at the command prompt.

Modifying policy settings

1.	At the Ntdsutil.exe command prompt, type LDAP policies, and then press ENTER.
2.	At the LDAP policy command prompt, type Set setting to variable, and then press ENTER. For example, type Set MaxPoolThreads to 8. This setting changes if you add another processor to your server.
3.	You can use the Show Values command to verify your changes. To save the changes, use Commit Changes.
4.	When you finish, type q, and then press ENTER.
5.	To quit Ntdsutil.exe, at the command prompt, type q, and then press ENTER.

资料来源:

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

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