一般 SQL 連接器技術參考
本文說明一般 SQL 連接器。 本文適用於下列產品：
- Microsoft Identity Manager 2016 (MIM2016)
- Microsoft Entra 識別碼
針對MIM2016，連接器可從 Microsoft 下載中心下載。
若要查看這個作用中的連接器，請參閱 Generic SQL Connector step-by-step (一般 SQL 連接器的逐步解說) 一文。
注意
Microsoft Entra ID 現在提供輕量型代理程式型解決方案，以將使用者布建到SQL資料庫，而不需要 MIM 同步處理部署。 建議您將其用於輸出使用者布建。 深入瞭解。
一般 SQL 連接器概觀
一般 SQL 連接器可讓您整合同步處理服務與提供 ODBC 連線的資料庫系統。
目前的連接器版本大致支援下列功能：
| 功能 | 支援 |
|---|---|
| 連接的資料來源 | 所有 64 位 ODBC 驅動程式*都支援連接器。 此連接器已進行下列各項的測試： |
| 案例 | |
| Operations | |
| 結構描述 |
必要條件
在您使用連接器之前，請確定同步處理伺服器上有下列項目：
- Microsoft .NET 4.6.2 Framework 或更新版本
- 64 位元的 ODBC 用戶端驅動程式
- 如果您使用連接器與 Oracle 12c 通訊，這需要 Oracle Instant Client 12.2.0.1 或更新版本與 ODBC 套件通訊。
- 如果您使用連接器與 Oracle 18c-23c 通訊，這需要 Oracle Instant Client 18-23 或更新版本與 ODBC 套件進行通訊，而NLS_LANG系統變數必須設定為支援 UTF8 字元，例如 NLS_LANG=AMERICAN_AMERICA。AL32UTF8。
- 此連接器會使用 SQL 備妥語句和每個交易的多個語句。 某些 RDBM 系統可能會在其 ODBC 驅動程式中發生與交易處理、伺服器端備妥 SQL 語句和相同交易內多個語句相關的問題。 請據以設定 DSN 連線選項，以確保這些語句已正確傳送至您的資料庫。例如，MySQL ODBC Driver 8.0.32 版需要選項 NO_SSPS=1 和 MULTI_STATEMENTS=1。 其他選項，例如「自動認可」或「僅限成功作業認可」，可能會影響批次導出的處理方式;如需詳細資訊，請參閱資料庫管理員。 若要針對匯出期間的問題進行疑難解答，請將匯出批次大小設定為1，並啟用連接器詳細信息記錄。
部署此連接器可能需要變更資料庫的設定，以及 MIM 的組態變更。 對於將 MIM 與生產環境中第三方資料庫伺服器整合的部署，我們建議客戶與其資料庫廠商合作，或部署合作夥伴以取得此整合的說明、指引和支援。
連接的資料來源權限
若要在一般 SQL 連接器中建立或執行任何支援的工作，您必須具備：
- db_datareader
- db_datawriter
連接埠和通訊協定
如需 ODBC 驅動程式運作所需的連接埠，請參閱資料庫廠商的說明文件。
建立新的連接器
若要建立一般 SQL 連接器，請在 [同步處理服務] 中選取 [管理代理程式] 和 [建立]。 選取 [一般 SQL (Microsoft)] 連接器。
連接性
連接器會使用 ODBC DSN 檔案進行連線。 使用在 [系統管理工具] 下的開始功能表中找到的 [ODBC 資料來源]，建立 DSN 檔案。 在系統管理工具中，建立 [檔案 DSN] ，以便提供給連接器。
當您建立新的一般 SQL 連接器時，[連線能力] 是第一個畫面。 您必須先提供下列資訊：
- DSN 檔案路徑
- 驗證
- 使用者名稱
- 密碼
資料庫應該支援下列其中一種驗證方法：
- Windows 驗證：驗證資料庫會使用 Windows 認證來驗證使用者。 指定的使用者名稱/密碼用來向資料庫進行驗證。 此帳戶需要資料庫的權限。
- SQL 驗證：驗證資料庫會使用 [連線能力] 畫面上定義的使用者名稱/密碼連接到資料庫。 如果您在 DSN 檔案中儲存使用者名稱/密碼，則優先使用在 [連線能力] 畫面上提供的認證。
- Azure SQL 資料庫驗證：如需詳細資訊，請參閱透過 Microsoft Entra 驗證連線到 SQL Database。
DN 是錨點：如果您選取此選項，DN 也會做為錨點屬性。 它可用於簡單實作，但也有下列限制：
- 連接器只支援一種物件類型。 因此，所有參考屬性只能參考相同的物件類型。
匯出類型：物件取代：在匯出期間，只有一些屬性已變更時，包含所有屬性的整個物件則會匯出並取代現有的物件。
結構描述 1 (偵測物件類型)
此頁面上，您將設定連接器要如何在資料庫中尋找不同的物件類型。
每個物件類型會顯示為一個資料分割，並且在 [設定資料分割和階層] 上進一步設定。
物件類型偵測方法：連接器支援下列物件類型偵測方法。
- 固定值：您以逗號分隔的清單來提供物件類型清單。 例如：
User,Group,Department。
- 資料表/檢視/預存程序：提供資料表/檢視/預存程序的名稱，然後提供資料行名稱以提供物件類型的清單。 如果您使用預存程序，也需以下列格式提供其參數： [名稱]:[方向]:[值]。 在個別一行上提供每個參數 (使用 Ctrl+Enter 來換行)。
- SQL 查詢：此選項可讓您提供 SQL 查詢，以傳回包含物件類型的單一資料行，例如
SELECT [Column Name] FROM TABLENAME。 傳回的資料行必須是字串類型 (varchar)。
結構描述 2 (偵測屬性類型)
在此頁面上，您將設定要如何偵測屬性名稱和類型。 系統會針對在前一頁偵測到的每個物件類型，列出設定選項。
屬性類型偵測方法：連接器會以 [結構描述 1] 畫面中每個偵測到的物件類型，支援下列屬性類型偵測方法。
- 資料表/檢視/預存程序：提供資料表/檢視/預存程序的名稱，以便用來尋找屬性名稱。 如果您使用預存程序，也需以下列格式提供其參數： [名稱]:[方向]:[值]。 在個別一行上提供每個參數 (使用 Ctrl+Enter 來換行)。 若要偵測多重值屬性中的屬性名稱，請提供以逗號分隔的資料表或檢視清單。 當父和子資料表具有相同的資料行名稱時，則不支援多重值案例。
- SQL 查詢：此選項可讓您提供 SQL 查詢，以傳回包含屬性名稱的單一資料行，例如
SELECT [Column Name] FROM TABLENAME。 傳回的資料行必須是字串類型 (varchar)。
結構描述 3 (定義錨點和 DN)
此頁面可讓您為每個偵測到的物件類型設定錨點和 DN 屬性。 您可以選取多個屬性，讓錨點變成唯一的。
不會列出多重值和布林值屬性。
DN 和錨點無法使用相同的屬性，除非已在 [連線能力] 頁面上選取 [DN 是錨點] 。
如果已在 [連線能力] 頁面上選取 [DN 是錨點] ，此頁面只需要 DN 屬性。 這個屬性也會做為錨點屬性。
結構描述 4 (定義屬性類型、參考和方向)
此頁面可讓您設定每個屬性的屬性類型，如整數、二進位或布林值和方向。 [結構描述 2] 頁面中的所有屬性都會列出，包括多重值屬性。
- DataType：用來將屬性類型對應至同步處理引擎所知的屬性類型。 預設會使用在 SQL 結構描述中偵測到的相同類型，但 DateTime 和 Reference 不容易偵測。 因此，您必須指定 DateTime 或 Reference。
- 方向：您可以設定 Import、Export 或 ImportExport 的屬性方向。 ImportExport 是預設值。
注意：
- 如果連接器無法偵測屬性類型，則會使用字串資料類型。
- 巢狀資料表 視為一個資料行的資料庫資料表。 Oracle 不會以任何特定順序儲存巢狀資料表的資料列。 不過，當您將巢狀資料表擷取至 PL/SQL 變數時，資料列便會有從 1 開始的連續下標。 這可讓您取得個別資料列的類似陣列存取。
- VARRYS 。
結構描述 5 (定義參考屬性的資料分割)
在此頁面上，您會為所有參考屬性設定屬性所參考的資料分割 (物件類型)。
如果您使用 [DN 是錨點] ，則必須使用相同的物件類型做為您參考的來源物件類型。 您無法參考其他物件類型。
注意
從 2017 年 3 月的更新開始，現在已有 "*" 的選項。在選擇了此選項時，系統便會匯入所有可能的成員類型。
重要
自 2017 年 5 月起，“*” 也稱為 任何選項 都已變更，以支持匯入和導出流程。 如果您想要使用此選項，您的多重值資料表/檢視應該有包含物件類型的屬性。
如果選取 「*」，則也必須指定具有物件類型的數據行名稱。
在匯入後，您會看到類似下圖的情形︰
全域參數
[全域參數] 頁面用來設定差異匯入、日期/時間格式，以及密碼方法。
一般 SQL 連接器支援使用下列差異匯入方法：
- 觸發程序：請參閱 使用觸發程序產生差異檢視。
- 浮水印：可以用於任何資料庫的一般方法。 浮水印查詢會根據資料庫供應商預先填入。 浮水印資料行必須出現在所用的每個資料表/檢視上。 此資料行必須追蹤資料表的插入和更新，以及其相依 (多重值或子系) 資料表。 同步處理服務與資料庫伺服器之間的時鐘必須同步。 如果沒有同步，則可能會省略差異匯入中的某些項目。
限制：- 浮水印策略不支援已刪除的物件。
- 快照：(僅適用於 Microsoft SQL Server) 使用快照產生差異檢視
- 變更追蹤：(僅適用於 Microsoft SQL Server) About 變更追蹤
限制：- 錨點和 DN 屬性必須屬於資料表中所選物件的主索引鍵。
- 在使用變更追蹤的匯入和匯出期間內，不支援 SQL 查詢。
其他參數：指定 [資料庫伺服器時區]，以指出您的資料庫伺服器所在的位置。 這個值用來支援各種格式的日期和時間屬性。
連接器一律會以 UTC 格式儲存日期和日期時間。 若要能夠正確地轉換日期和時間，必須指定資料庫伺服器的時區以及所用的格式。 格式應以 .NET 格式表示。
在匯出期間，必須器以 UTC 時間格式將每個日期時間屬性提供給連接器。
密碼設定：連接器會提供密碼同步處理功能並支援設定和變更密碼。
連接器提供兩種方法來支援密碼同步處理：
- 預存程序：此方法需要兩個預存程序，以支援設定和變更密碼。 按照以下範例，分別在 [設定密碼 SP] 和 [變更密碼 SP] 參數中輸入新增和變更密碼作業的所有參數。
- 密碼延伸模組：此方法需要密碼延伸模組 DLL (您必須提供實作 IMAExtensible2Password 介面的延伸模組 DLL 名稱)。 密碼延伸模組組件必須放在延伸模組資料夾中，連接器才可以在執行階段載入 DLL。
您也必須在 [設定延伸模組] 頁面上啟用密碼管理。
設定分割區與階層
在資料分割和階層頁面上，選取所有物件類型。 每個物件類型都在自己的資料分割中。
您也可以覆寫在 [連線能力] 或 [全域參數] 頁面上定義的值。
設定錨點
此頁面是唯讀頁面，因為已經定義錨點。 選取的錨點屬性一律會附加物件類型，以確保它在所有物件類型中保持獨一無二。
設定執行步驟參數
這些步驟設定於連接器的執行設定檔。 這些設定會進行匯入和匯出資料的實際工作。
完整和差異匯入
一般 SQL 連接器支援使用下列方法的完整和差異匯入：
- Table
- 檢視
- 預存程序
- SQL 查詢
數據表/檢視
若要將物件的多重值屬性匯入，就必須在 [多重資料表/檢視名稱] 中提供資料表/檢視名稱，並在父資料表的 [聯結條件] 中，提供各自的聯結條件。 如果數據源中有多個多重值數據表，您可以使用聯集至單一檢視。
重要
一般 SQL 管理代理程式僅能使用一個多重值資料表。 請勿將多個資料表名稱放入 [多重值資料表/檢視的名稱] 中。 這是一般 SQL 的限制。
範例：您想要匯入 [員工] 物件與其所有的多重值屬性。 有兩個資料表：[員工] \(主要資料表) 和 [部門] \(多重值)。 執行下列動作：
- 在 [資料表/檢視/SP] 中輸入員工。
- 在 [多重資料表/檢視名稱] 中輸入 [部門]。
- 在 [聯結條件] 中輸入 [員工] 和 [部門] 之間的聯結條件，例如
Employee.DEPTID=Department.DepartmentID。
預存程序
- 如果您有大量資料，建議實作預存程序的分頁。
- 若要讓預存程序支援分頁，您必須提供開始索引和結束索引。 請參閱： 有效地進行大量資料的分頁。
- 在執行時，會以在 [設定步驟] 頁面上設定的各自頁面大小值取代 @StartIndex 和 @EndIndex。 例如，當連接器擷取第一頁且頁面大小設為 500 時，在這種情況下 @StartIndex 會是 1 而 @EndIndex 會是 500。 當連接器擷取後續頁面並變更 @StartIndex 和 @EndIndex 值時，這些值增加。
- 若要執行參數化預存程序，請以
[Name]:[Direction]:[Value]格式提供參數。 在個別一行上輸入每個參數 (使用 Ctrl + Enter 來換行)。 - 一般 SQL 連接器也支援從 Microsoft SQL Server 中連結伺服器的匯入作業。 如果應該從連結的伺服器中的資料表擷取資訊，則應該以下列格式提供資料表：
[ServerName].[Database].[Schema].[TableName] - 一般 SQL 連接器僅支援在執行步驟資訊和結構描述偵測之間具有類似結構 (包括別名和資料類型) 的物件。 如果結構描述中選取的物件與在執行步驟提供的資訊不同，則 SQL 連接器無法支援這類案例。
SQL 查詢
重要
CRLF 或新的行字元可作為多個語句之間的分隔符。
使用分頁的 SQL 查詢範例 - 不正確的查詢將無法運作，因為使用新的行字元：
WITH A AS
(select dense_rank() over (order by BusinessEntityID)
rownumber, BusinessEntityID, DeptID, NationalIDNumber, LoginID, JobTitle, BirthDate, MaritalStatus, HireDate, ModifiedDate, Password
from Employees
) select * from A where rownumber between @StartIndex and @EndIndex
使用分頁的 SQL 查詢範例 - 正確的查詢：
WITH A AS (select dense_rank() over (order by BusinessEntityID) rownumber, BusinessEntityID, DeptID, NationalIDNumber, LoginID, JobTitle, BirthDate, MaritalStatus, HireDate, ModifiedDate, Password from Employees) select * from A where rownumber between @StartIndex and @EndIndex
- 不支援多個結果集查詢。
- SQL 查詢支援分頁，並提供 Start Index 和 End Index 做為變數來支援分頁。
差異匯入
相較於完整匯入，差異匯入設定需要一些更詳細的設定。
- 如果您選擇 [觸發程序] 或 [快照] 方法來追蹤差異變更，則在 [歷程記錄資料表或快照資料庫名稱] 方塊中提供歷程記錄資料表或快照資料庫。
- 您也必須提供歷程記錄資枓表與父資料表之間的聯結條件，例如
Employee.ID=History.EmployeeID - 若要從歷程記錄資料表追蹤父資料表上的交易，您必須提供包含作業資訊 (新增/更新/刪除) 的資料行名稱。
- 如果您者選擇 [浮水印] 來追蹤差異變更，則在 [浮水印資料行名稱] 中提供包含作業資訊的資料行名稱。
- 變更類型需要 [變更型別屬性] 資料行。 此資料行會將主要資料表或多重值資料表中發生的變更，對應至差異檢視中的變更類型。 此資料行可以包含適用於屬性層級變更的 Modify_Attribute 變更類型，或適用於物件層級變更的 [新增]、[修改] 或 [刪除] 變更類型。 如果除了預設值 [新增]、[修改] 或 [刪除] 以外，您可以使用此選項來定義這些值。
匯出
一般 SQL 連接器支援使用下列四種方法的匯出：
- Table
- 檢視
- 預存程序
- SQL 查詢
數據表/檢視
如果您選擇 [資料表/檢視] 選項，則連接器會產生各自的查詢來進行匯出。
預存程序
如果您選擇預存程序選項，則匯出需要 3 個不同的預存程序才能執行插入/更新/刪除作業。
- 新增 SP 名稱：如有任何物件來到連接器以便在各自的資料表中插入，就會執行此 SP。
- 更新 SP 名稱：如有任何物件來到連接器以便在各自的資料表中更新，就會執行此 SP。
- 刪除 SP 名稱：如有任何物件來到連接器以便在各自的資料表中刪除，就會執行此 SP。
- 從結構描述選取的屬性會做為預存程序的參數值。 範例：
@EmployeeName: INPUT: EmployeeName(EmployeeName 已在連接器結構描述中選取，而連接器會在進行匯出時取代各自的值) - 若要執行參數化預存程序，請以
[Name]:[Direction]:[Value]格式提供參數。 在個別一行上輸入每個參數 (使用 Ctrl + Enter 來換行)。
SQL 查詢
如果您選擇 SQL 查詢選項，則匯出需要 3 個不同的查詢來執行插入/更新/刪除作業。
- 插入查詢：如有任何物件來到連接器以便在各自的資料表中插入，就會執行此查詢。
- 更新查詢：如有任何物件來到連接器以便在各自的資料表中更新，就會執行此查詢。
- 刪除查詢：如有任何物件來到連接器以便在各自的資料表中刪除，就會執行此查詢。
- 從結構描述選取的屬性會做為查詢的參數值，例如
Insert into Employee (ID, Name) Values (@ID, @EmployeeName)
重要
CRLF 或新的行字元可作為多個語句之間的分隔符。
範例多步驟更新 SQL 查詢 - 新的行字元可用來分隔 SQL 語句：
update Employee set jobTitle=@JOBTITLE where BusinessEntityID=@BUSINESSENTITYID
insert into ChangeLog VALUES (@BUSINESSENTITYID)
疑難排解
- 如需如何啟用記錄來疑難排解連接器的資訊，請參閱 如何啟用連接器的 ETW 追蹤。