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’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,
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 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
以上方法源自于：
我推薦的方法:在微軟搜索到的.如何通過 NTDSUtil為服務器修改限制 maxPageSize
Modifying policy settings
資料來源:
如何使用SQL查詢活動目錄對象語法:
