主頁(yè) > 知識(shí)庫(kù) > SQLServer 使用ADSI執(zhí)行分布式查詢ActiveDorectory對(duì)象

SQLServer 使用ADSI執(zhí)行分布式查詢ActiveDorectory對(duì)象

熱門(mén)標(biāo)簽:貴州房產(chǎn)智能外呼系統(tǒng)供應(yīng)商 一個(gè)導(dǎo)航軟件能用幾個(gè)地圖標(biāo)注點(diǎn) 鎮(zhèn)江網(wǎng)路外呼系統(tǒng)供應(yīng)商 小e電話機(jī)器人 外呼運(yùn)營(yíng)商線路收費(fèi) 百度地圖標(biāo)注改顏色 申請(qǐng)400電話在哪辦理流程 臨沂智能電銷(xiāo)機(jī)器人加盟哪家好 電銷(xiāo)外呼有錄音系統(tǒng)有哪些
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'
對(duì)于 SQL Server 授權(quán)登錄,可以使用sp_addlinkedsrvlogin 系統(tǒng)存儲(chǔ)過(guò)程配置用于連接到目錄服務(wù)的適當(dāng)?shù)牡卿?密碼.
參考這里: http://blogs.msdn.com/euanga/archive/2007/03/22/faq-how-do-i-query-active-directory-from-sql-server.aspx
如果SQLServer使用Windows 授權(quán)登錄,只需自映射就足以通過(guò)使用 SQL Server 安全委托來(lái)訪問(wèn)AD。簡(jiǎn)單點(diǎn)說(shuō)就是直接運(yùn)行第三步語(yǔ)句即可.
Step 3:Querying the Directory Service.
復(fù)制代碼 代碼如下:

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

說(shuō)明:但是這樣默認(rèn)查詢出來(lái)的是1000個(gè)對(duì)象.怎么辦呢?
方法一,通過(guò)字母來(lái)循環(huán).見(jiàn)以下:
復(fù)制代碼 代碼如下:

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

我推薦的方法:在微軟搜索到的.如何通過(guò) NTDSUtil為服務(wù)器修改限制 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.

資料來(lái)源:

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

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

如何使用SQL查詢活動(dòng)目錄對(duì)象語(yǔ)法: http://www.microsoft.com/china/technet/community/columns/scripts/sg0505.mspx#EMBAC

標(biāo)簽:合肥 延邊 晉城 嘉興 日照 三明 澳門(mén) 保定

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《SQLServer 使用ADSI執(zhí)行分布式查詢ActiveDorectory對(duì)象》,本文關(guān)鍵詞  SQLServer,使用,ADSI,執(zhí)行,分布式,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《SQLServer 使用ADSI執(zhí)行分布式查詢ActiveDorectory對(duì)象》相關(guān)的同類(lèi)信息!
  • 本頁(yè)收集關(guān)于SQLServer 使用ADSI執(zhí)行分布式查詢ActiveDorectory對(duì)象的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章