MS SQL Server 讀取 AD 資料

MS SQL Server 讀取 AD 方式有二
1.OPENROWSET
2.Linked Server + OPENQUERY

[OPENROWSET]
Step1 開啟 OPENROWSET 功能(已開啟則省略)

--開啟進階選項 1:開啟 / 0:關閉
sp_configure 'show advanced options',1
reconfigure

--開啟openrowset功能 1:開啟 / 0:關閉
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

Step2 T-SQL
   [Account] : domain\account, ex. ABC\John
   [Password] : account password
   [Domain] : domain name, ex. ABC.com
   ※LDAP必須大寫

select * from openrowset(
   'ADsDSOObject' --Provider
   ,'';'[Account]';'[Password]' --Data Source;DomainAccount;Password
   ,'select sn,givenname,mail from ''LDAP://[Domain]'' where sn=''*'' '
)

常用 AD 欄位

欄位名	說明	欄位名	說明
samaccountname	AD帳號	mail	Email
givenname	名字	sn	姓氏
displayname	Fullname (姓+名)	company	公司
department	部門	location	位置
title	職稱	請參考 LDAP Properties for CSVDE and VBScript

LDAP查詢語法請參考LDAP 查詢基礎

[OPENQUERY]
Step1 新增Linked Server
   提供者 : OLE DB Provider for Microsoft Directory Services
   產品名稱 : 隨便
   資料來源 : domain name, ex. ABC.com


Step2 安全性設定
   遠端登入 : domain\account, ex. ABC\John
   指定密碼 : account password


Step3 T-SQL

select * from openquery(ADSI
   ,'select sn,givenname,mail from ''LDAP://[Domain]'' where sn=''*'' '
)

附註 :
   1. SQL Server 2005 最多只能抓到 1000 筆 AD 資料
   2. SQL Server 2008 R2 最多只能抓到 901 筆 AD 資料
   3. SQL Server 2012 最多只能抓到 901 筆 AD 資料