SQLServer 應用ADSI履行散布式查詢ActiveDorectory對象。本站提示廣大學習愛好者：（SQLServer 應用ADSI履行散布式查詢ActiveDorectory對象）文章只能為提供參考，不一定能成為您想要的結果。以下是SQLServer 應用ADSI履行散布式查詢ActiveDorectory對象正文
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
若何應用SQL查詢運動目次對象語法: http://www.microsoft.com/china/technet/community/columns/scripts/sg0505.mspx#EMBAC
