SQL Server 建立連結伺服器標準腳本範本：sp_addlinkedserver、sp_serveroption、sp_addlinkedsrvlogin

標準化連結伺服器建立流程，確保安全性、效能與可維護性。

三步驟標準腳本

1. 建立連結伺服器 (sp_addlinkedserver)

EXEC sp_addlinkedserver
    @server = 'LINK_PROD_ERP',           -- 連結伺服器名稱 (本地別名)
    @srvproduct = 'SQL Server',          -- 產品名稱 (SQL Server 可省略)
    @provider = 'SQLNCLI11',             -- 提供者：SQLNCLI11 (2012+), SQLNCLI10 (2008/R2), SQLOLEDB (舊版)
    @datasrc = '192.168.10.100',         -- 遠端伺服器 IP/主機名
    @catalog = 'ERP_Database';           -- 預設資料庫 (可選)
GO

2. 設定伺服器選項 (sp_serveroption)

-- 啟用 RPC 遠程預存程序呼叫
EXEC sp_serveroption 'LINK_PROD_ERP', 'rpc out', 'true';
EXEC sp_serveroption 'LINK_PROD_ERP', 'rpc', 'true';

-- 安全性建議
EXEC sp_serveroption 'LINK_PROD_ERP', 'remote proc transaction promotion', 'true'; -- 異動提升
EXEC sp_serveroption 'LINK_PROD_ERP', 'collation compatible', 'false'; -- 定序不強制相容
EXEC sp_serveroption 'LINK_PROD_ERP', 'lazy schema validation', 'true'; -- 延遲結構驗證 (效能)
GO

3. 建立登入映射 (sp_addlinkedsrvlogin)

-- 方案 A: 特定本地登入映射到遠端固定帳號 (最常用)
EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'LINK_PROD_ERP',
    @useself = 'false',           -- 不使用當前登入上下文
    @locallogin = 'app_user',     -- 本地登入 (NULL 表示所有)
    @rmtuser = 'erp_reader',      -- 遠端登入
    @rmtpassword = 'Str0ngP@ss';  -- 遠端密碼
GO

-- 方案 B: 當前登入自動映射 (需雙方同帳號/信任網域)
-- EXEC sp_addlinkedsrvlogin 'LINK_PROD_ERP', 'true', NULL, NULL, NULL;
GO

提供者選擇指南

安全性最佳實務

• 遠端帳號授予 最小權限 (僅 db_datareader / 執行特定預存程序)。
• 密碼**勿硬編碼**於部署腳本，使用 SQLCMD 變數 / 環境變數 / Key Vault 注入。
• 定期稽核 sys.linked_logins、sys.servers。
• 跨網域/不信任網域需啟用 rpc out 並配置 Kerberos 委派 (雙跳驗證)。

驗證查詢

-- 基本連線測試
SELECT * FROM OPENQUERY(LINK_PROD_ERP, 'SELECT @@SERVERNAME, @@VERSION');

-- 四部分命名查詢
SELECT TOP 10 * FROM LINK_PROD_ERP.ERP_Database.dbo.Orders;