建立連結的伺服器 (SQL Server 資料庫引擎)

  • 發行項

適用於：SQL ServerAzure SQL 受控執行個體

此文章說明如何使用 SQL Server Management Studio (SSMS) 或 Transact-SQL 來建立連結的伺服器，並從另一個 SQL Server、Azure SQL 受控執行個體或其他資料來源存取資料。 連結的伺服器讓 SQL Server 資料庫引擎和 Azure SQL 受控執行個體能夠從遠端資料來源讀取資料，並針對 SQL Server 執行個體外部的遠端資料庫伺服器 (例如 OLE DB 資料來源) 執行命令。

背景

連結的伺服器通常會設定為讓資料庫引擎能夠執行 Transact-SQL 陳述式，以包含另一個 SQL Server 執行個體或另一個資料庫產品 (例如 Oracle) 中的資料表。 許多類型的資料來源可以設定為連結的伺服器，包括協力廠商資料庫提供者與 Azure Cosmos DB。

建立連結的伺服器之後，即可對這部伺服器執行分散式查詢，而且查詢可以加入來自多個資料來源的資料表。 如果連結的伺服器定義為 SQL Server 的執行個體或 Azure SQL 受控執行個體，則可執行遠端預存程序。

連結之伺服器的功能以及所需的引數可能會大大地改變。 本文中的範例提供一般範例，但並未描述所有選項。 如需詳細資訊，請參閱 sp_addlinkedserver (Transact-SQL) (部分機器翻譯)。

權限

使用 Transact-SQL 陳述式時，需要伺服器的 ALTER ANY LINKED SERVER 權限或 setupadmin 固定伺服器角色的成員資格。 使用 Management Studio 時，需要 CONTROL SERVER 權限或 sysadmin 固定伺服器角色中的成員資格。

透過 SSMS 建立連結的伺服器

使用下列程序，透過 SSMS 建立連結的伺服器：

開啟 [新增連結的伺服器] 對話方塊

在 SQL Server Management Studio (SSMS) 中：

  1. 開啟物件總管。
  2. 展開 [伺服器物件]。
  3. 使用滑鼠右鍵按一下 [連結的伺服器]。
  4. 選取 [新增連結的伺服器]。

編輯連結伺服器屬性的 [一般] 頁面

在 [一般] 頁面的 [連結的伺服器] 方塊中，輸入您要連結之 SQL Server 的執行個體名稱。

注意

如果 [SQL Server] 的執行個體是預設的執行個體，請輸入裝載 [SQL Server] 執行個體之電腦的名稱。 如果 SQL Server 是具名執行個體，請輸入電腦的名稱和執行個體的名稱，例如 Accounting\SQLExpress。

視需要指定伺服器類型及相關資訊：

  • SQL Server
    將連結的伺服器識別為 Microsoft SQL Server 的執行個體或 Azure SQL 受控執行個體。 如果您使用此方法來定義連結的伺服器，則在 [連結的伺服器] 中指定的名稱必須是伺服器的網路名稱。 另外，從伺服器擷取的任何資料表，都會是來自已連結伺服器上之登入所定義的預設資料庫。

  • 其他資料來源
    指定 SQL Server 以外的 OLE DB 伺服器類型。 按一下這個選項會啟動在它下面的選項。

    • 提供者
      從清單方塊中選取 OLE DB 資料來源。 在登錄中，OLE DB 提供者是使用給定的 PROGID 註冊。

    • 產品名稱
      輸入 OLE DB 資料來源的產品名稱，以加入成為連結的伺服器。

    • 資料來源
      輸入資料來源的名稱，如 OLE DB 提供者所解譯。 如果您要連線到 SQL Server 的執行個體，請提供執行個體名稱。

    • 提供者字串
      輸入對應至資料來源之 OLE DB 提供者的唯一程式設計識別碼 (PROGID)。 如需有效提供者字串的範例，請參閱 sp_addlinkedserver (Transact-SQL) (部分機器翻譯)。

    • 位置
      依 OLE DB 提供者的解譯，輸入資料庫的位置。

    • 目錄
      輸入連接到 OLE DB 提供者時，要使用的目錄名稱。

編輯連結伺服器屬性的 [安全性] 頁面

在 [安全性] 頁面上，指定原始執行個體連線到連結的伺服器時將使用的資訊安全內容。 您可以在這裡設定兩種策略，這兩種策略可以單獨使用或合併。 第一種是將本機伺服器的登入對應到遠端伺服器，第二種則是連結的伺服器應該如何處理未對應的登入。

新增登入對應

您可以選擇性地指定特定本機伺服器登入如何使用連結的伺服器進行驗證。

在 [本機伺服器登入與遠端伺服器登入對應] 底下，針對您想要對應的每個登入重複執行下列流程：

  1. 選取 [新增]。

  2. 指定本機登入。

    指定可以連接到連結伺服器的本機登入。 本機登入可以是使用 SQL Server 驗證的登入或 Windows 驗證登入。 不支援使用 Windows 群組或自主資料庫使用者。 使用這份清單可限制與特定登入的連接，或允許某些登入連接成不同的登入。

    注意

    使用 Windows 驗證到遠端 SQL Server 執行個體之連結伺服器的常見問題，源於服務主體名稱 (SPN) 的問題。 如需詳細資訊，請參閱用戶端連線中的服務主體名稱 (SPN) 支援 (部分機器翻譯)。 適用於 SQL Server 的 Microsoft Kerberos Configuration Manager 是一種診斷工具，有助於針對 SQL Server 上發生的 Kerberos 相關連線問題進行疑難排解。 如需詳細資訊，請參閱 Microsoft Kerberos Configuration Manager for SQL Server。

  3. 選取 [模擬] \(選擇性\)。

    將使用者名稱和密碼從本機登入傳送給連結伺服器。 針對 SQL Server 驗證，遠端伺服器上必須要有名稱和密碼完全相同的登入。 若為 Windows 登入，則登入必須是連結伺服器上的有效登入。

    若要使用模擬，則組態必須符合委派需求。

  4. 如果您未使用模擬，請指定遠端使用者。

    使用遠端使用者來對應已在 [本機登入] 中定義的使用者。 遠端使用者必須是遠端伺服器上的 SQL Server 驗證登入。

  5. 如果您未使用模擬，請指定遠端密碼。

    • 指定遠端使用者的密碼。

  6. 視需要選取 [移除] 以移除現有的本機登入。

針對不存在於對應清單中的登入，指定預設資訊安全內容

在使用者使用其網域登入進行連線的網域環境中，選取 [使用登入的目前安全性內容建立] 通常是最佳選擇。 當使用者使用 [SQL Server] 登入連線到原始 [SQL Server] 時，最佳選擇通常是選取 [使用此安全性內容] ，然後提供所需的認證在連結的伺服器進行驗證。

選取下列其中一個選項：

  • 不建立
    不會針對清單中未定義的登入建立連線。

  • 不使用安全性內容建立
    不會使用資訊安全內容來為清單中未定義的登入建立連線。

  • 使用登入的目前安全性內容建立
    使用登入目前的資訊安全內容來為清單中未定義的登入建立連線。 如果使用 Windows 驗證連線到本機伺服器，就會使用您的 Windows 認證連線到遠端伺服器。 如果使用 SQL Server 驗證連線到本機伺服器，則會使用登入名稱和密碼來連線到遠端伺服器。 在此情況下，遠端伺服器上必須要有名稱和密碼完全相同的登入。

  • 使用此安全性內容建立
    使用 [遠端登入] 和 [使用密碼] 方塊中指定的登入和密碼，來為清單中未定義的登入建立連線。 遠端登入必須是遠端伺服器上的 SQL Server 驗證登入。

在連結的伺服器屬性中編輯 [伺服器選項] 頁面 (選擇性)

若要檢視或指定伺服器選項，請選取 [伺服器選項] 頁面。 您可以編輯下列任何選項：

  • 定序相容
    影響針對連結伺服器的分散式查詢執行。 如果此選項設為 True，SQL Server 假設連結伺服器中的所有字元在字元集和定序序列 (或排序次序) 方面都與本機伺服器相容。 這使 SQL Server 能夠將字元資料行的比較傳送給提供者。 如果未設定此選項，SQL Server 一律會在本機評估字元資料行的比較。

    只有在確定對應於連結伺服器的資料來源與本機伺服器的字元集和排序順序相同時，才應該設定這個選項。

  • 資料存取
    啟用和停用連結伺服器的分散式查詢存取。

  • RPC
    從指定的伺服器啟用遠端程序呼叫 (RPC)。

  • RPC 輸出
    啟用對指定伺服器的 RPC。

  • 使用遠端定序
    決定將使用遠端資料行或本機伺服器的定序。

    如果為 True，即會針對 SQL Server 資料來源使用遠端資料行的定序，並針對非 SQL Server 資料來源使用定序名稱中指定的定序。

    如果為 False，分散式查詢一律會使用本機伺服器的預設定序，而定序名稱與遠端資料行的定序則會被忽略。 預設值為 false。

  • 定序名稱
    如果使用遠端定序為 True，而且資料來源不是 SQL Server 資料來源，請指定遠端資料來源所使用的定序名稱。 名稱必須是 SQL Server 所支援的定序之一。

    在存取不是 SQL Server，但其定序卻符合某個 SQL Server 定序的 OLE DB 資料來源時，請使用此選項。

    連結伺服器必須支援供這部伺服器的所有資料行使用的單一定序。 如果連結的伺服器支援單一資料來源內的多重定序，或者無法確定連結伺服器的定序是否符合某個 SQL Server 定序時，請勿設定此選項。

  • 連接逾時
    連接到連結伺服器的逾時值 (以秒為單位)。

    若為 0，則使用 sp_configure 的預設 remote query timeout 選項值。

  • 查詢逾時
    針對連結伺服器進行查詢的逾時值 (以秒為單位)。

    如果為 0，則使用 sp_configure 預設 remote query timeout 選項值。

  • 啟用分散式交易的升級
    使用此選項，透過 Microsoft 分散式交易協調器 (MS DTC) 交易，保護伺服器對伺服器程序的動作。 此選項為 TRUE 時，呼叫遠端預存程序就會啟動分散式交易，而且會利用 MS DTC 來編列這項交易。 如需詳細資訊，請參閱 sp_serveroption (Transact-SQL) (部分機器翻譯)。

儲存連結的伺服器

選取 [確定]。

在 SSMS 中檢視或編輯連結的伺服器提供者選項

所有提供者的可用選項並不相同。 例如，某些資料類型有索引可用，而某些可能沒有。 使用此對話方塊來協助 SQL Server 了解提供者的功能。 SQL Server 會安裝某些常見的資料提供者，不過，當提供資料的產品變更時，SQL Server 所安裝的提供者不一定支援所有最新功能。 提供資料之產品功能的最佳資源來源是該產品的說明文件。
在 SSMS 中開啟連結伺服器的 [提供者選項] 頁面：

  1. 開啟物件總管。
  2. 展開 [伺服器物件]。
  3. 展開 [連結的伺服器]。
  4. 展開 [提供者]。
  5. 使用滑鼠右鍵按一下提供者，然後選取 [屬性]。

提供者選項的定義如下：

  • 動態參數
    表示提供者允許在參數化查詢時使用 '?' 參數標記語法。 這個選項只有在提供者支援 IcommandWithParameters 介面，並支援 '?' 作為參數標記時才能設定。 設定此選項可讓 SQL Server 針對提供者執行參數化查詢。 針對提供者執行參數化查詢的能力，對於某些查詢而言可以達到較佳的效能。

  • 巢狀查詢
    表示該提供者允許 FROM 子句中使用巢狀 SELECT 陳述式。 設定此選項可讓 SQL Server 向需要在 FROM 子句中巢狀處理 SELECT 陳述式的提供者委派特定查詢。

  • 限層級零
    只會針對提供者叫用層級 0 的 OLE DB 介面。

  • 允許 Inprocess

    SQL Server 允許將提供者具現化為同處理序伺服程式。 未設定此選項時，預設行為是在 SQL Server 處理序以外將提供者具現化。 在 SQL Server 處理序以外將提供者具現化，可防止 SQL Server 處理序在提供者中產生錯誤。 在 SQL Server 處理序以外將提供者具現化時，不允許對要參考的長資料行 (text、ntext 或 image) 進行更新或插入。

  • 非交易更新
    即使 ITransactionLocal 無法使用，SQL Server 仍然允許更新。 如果已啟用此選項，則針對提供者的更新便無法復原，因為提供者不支援交易。

  • 索引為存取路徑
    SQL Server 會嘗試使用提供者的索引來擷取資料。 依預設值，索引只用於中繼資料，絕不會開啟

  • 不允許特定存取
    SQL Server 不允許透過 OPENROWSET 與 OPENDATASOURCE 函數來對 OLE DB 提供者進行特定存取。 當未設定此選項時，SQL Server 不允許特定存取。

  • 支援 'Like' 運算子
    表示該提供者支援使用 LIKE 關鍵字的查詢。

使用 Transact-SQL 建立連結的伺服器

若要使用 Transact-SQL 建立連結的伺服器，請使用 sp_addlinkedserver (Transact-SQL) (部分機器翻譯)、CREATE LOGIN (Transact-SQL) (部分機器翻譯) 與 sp_addlinkedsrvlogin (Transact-SQL) (部分機器翻譯) 陳述式。

此範例使用 Transact-SQL 來建立與另一個 SQL Server 執行個體連結的伺服器：

  1. 在查詢編輯器中，輸入下列 Transact-SQL 命令以連結到名為 SRVR002\ACCTG 的 SQL Server 執行個體：

    USE [master]  
GO  
EXEC master.dbo.sp_addlinkedserver   
    @server = N'SRVR002\ACCTG',   
    @srvproduct=N'SQL Server';  
GO

  2. 執行下列程式碼來設定連結的伺服器使用將使用連結的伺服器之登入的網域認證。

    EXEC master.dbo.sp_addlinkedsrvlogin   
    @rmtsrvname = N'SRVR002\ACCTG',   
    @locallogin = NULL ,   
    @useself = N'True';  
GO

待處理：建立連結的伺服器之後所採取的步驟

下列步驟可協助您驗證連結的伺服器。

測試連結的伺服器

請考慮下列兩種方法之一，以在目前的資訊安全內容中測試連結伺服器的驗證。

  • 若要測試在 SSMS 中連線到連結伺服器的能力，在 [物件總管] 中瀏覽到連結的伺服器、使用滑鼠右鍵按一下連結的伺服器，然後選取 [測試連線]。

  • 若要測試連線到 T-SQL 中連結伺服器的功能，請執行簡單的 SELECT 陳述式，例如，擷取基本資料庫目錄資訊。 此範例會傳回連結伺服器上的資料庫名稱。

    SELECT name FROM [SRVR002\ACCTG].master.sys.databases;  
GO

從連結的伺服器聯結資料表

使用四部分的名稱來表示連結之伺服器上的物件。 執行下列程式碼以傳回本機伺服器上的所有登入清單，及其連結之伺服器上相符的登入。

SELECT local.name AS LocalLogins, linked.name AS LinkedLogins  
FROM master.sys.server_principals AS local  
LEFT JOIN [SRVR002\ACCTG].master.sys.server_principals AS linked  
     ON local.name = linked.name;  
GO

針對連結的伺服器登入傳回 NULL 時，表示該登入不存在連結的伺服器上。 除非將連結的伺服器設定為傳遞不同的安全性內容，或連結的伺服器接受匿名連線，否則這些登入將無法使用連結的伺服器。

具有 Azure SQL 受控執行個體的連結伺服器

如果您使用 Azure SQL 受控執行個體，請參閱下列來自 sp_addlinkedserver (Transact-SQL) (部分機器翻譯) 的範例：

下一步

若要深入了解如何管理連結的伺服器，請參閱下列文章：