SQL Server 建立連結伺服器與遠端登入完整腳本教學

連結伺服器（Linked Server）讓 SQL Server 執行個體能存取異質資料來源（另一台 SQL Server、Oracle、PostgreSQL、Excel 等），實現跨伺服器分散式查詢與交易。本文提供標準化建立腳本與關鍵參數說明。

標準建立腳本（SQL Server 到 SQL Server）

-- 1. 參數化設定，方便維護
DECLARE @linked_srv   NVARCHAR(128) = N'LINK_PROD';      -- 連結伺服器名稱
DECLARE @remote_srv   NVARCHAR(128) = N'192.168.1.100';   -- 遠端伺服器 IP/主機名
DECLARE @remote_db    NVARCHAR(128) = N'ERP_DB';          -- 遠端資料庫
DECLARE @remote_user  NVARCHAR(128) = N'link_user';       -- 遠端登入帳號
DECLARE @remote_pwd   NVARCHAR(128) = N'Str0ngP@ssw0rd!'; -- 遠端密碼

-- 2. 若已存在則先移除（含遠端登入）
IF EXISTS (SELECT 1 FROM sys.servers WHERE name = @linked_srv)
BEGIN
    EXEC sp_dropserver @server = @linked_srv, @droplogins = 'droplogins';
END

-- 3. 建立連結伺服器 (使用 SQLOLEDB / SQLNCLI 提供者)
EXEC sp_addlinkedserver
    @server     = @linked_srv,
    @srvproduct = N'SQL Server',
    @provider   = N'SQLNCLI',          -- 建議 SQLNCLI11 或 MSOLEDBSQL (新版)
    @datasrc    = @remote_srv,
    @catalog    = @remote_db;

-- 4. 設定遠端登入對應 (本地 sa 對應遠端 link_user)
EXEC sp_addlinkedsrvlogin
    @rmtsrvname  = @linked_srv,
    @useself     = N'FALSE',           -- 不使用本地登入認證
    @locallogin  = N'sa',              -- 本地登入 (NULL 表所有)
    @rmtuser     = @remote_user,
    @rmtpassword = @remote_pwd;

-- 5. 重要伺服器選項設定
EXEC sp_serveroption @server = @linked_srv, @optname = N'collation compatible',     @optvalue = N'true';   -- 定序相容，遠端排序
EXEC sp_serveroption @server = @linked_srv, @optname = N'use remote collation',     @optvalue = N'true';   -- 使用遠端定序
EXEC sp_serveroption @server = @linked_srv, @optname = N'data access',              @optvalue = N'true';   -- 啟用資料存取
EXEC sp_serveroption @server = @linked_srv, @optname = N'rpc',                      @optvalue = N'true';   -- 允許 RPC
EXEC sp_serveroption @server = @linked_srv, @optname = N'rpc out',                  @optvalue = N'true';   -- 允許 RPC 輸出
EXEC sp_serveroption @server = @linked_srv, @optname = N'remote proc transaction promotion', @optvalue = N'true'; -- 分散式交易

關鍵參數深度解析

驗證連結伺服器

-- 測試基本連線
EXEC sp_testlinkedserver @linked_srv;

-- 測試查詢 (四部份命名)
SELECT TOP 3 * FROM [LINK_PROD].[ERP_DB].[dbo].[Customers];

-- 測試 RPC 呼叫
EXEC [LINK_PROD].[ERP_DB].[dbo].[usp_GetOrderCount] @CustID = 1001;

常見問題排查

• 雙跳認證失敗：Windows 認證跨兩台機器需設定 SPN、委派、Kerberos，建議改用 SQL 認證（@useself = FALSE）
• MSDTC 未啟動：分散式交易需兩台伺服器皆啟用 Distributed Transaction Coordinator 服務並設定網路存取
• 定序衝突：跨庫 JOIN 字串欄位報錯，加上 COLLATE DATABASE_DEFAULT 或設定 collation compatible = true
• 防火牆/連接埠：確認 1433 (TCP) 與 MSDTC 135/動態連接埠 開放

安全性最佳實務

1. 專用低權限登入帳號（僅 SELECT/EXECUTE 必要物件）
2. 密碼勿硬編碼於腳本，使用 Azure Key Vault、密碼管理工具或加密儲存
3. 定期稽核 sys.linked_logins 與 sys.servers
4. 限制 rpc out 僅必要連結伺服器啟用

掌握連結伺服器建立與調校，即可打破資料孤島，實現跨伺服器即時整合查詢與異質資料來源存取。