SQL Server Agent CmdExec 實戰：跨 VPN 呼叫異構資料庫存儲過程

在混合雲或多資料中心架構中，常需透過本地 SQL Server 作為跳板，經由 VPN 連線呼叫遠端資料庫的存儲過程。本文實戰記錄如何利用 SQL Server Agent 作業結合 CmdExec 步驟實現「自動撥號 VPN → 跨連結伺服器呼叫遠端 SP → 自動掛斷 VPN」的完整流程，並深度解析分散式交易 (MSDTC) 與 Agent 服務帳號權限兩大核心坑點。

業務場景

• 本地 SQL Server (Jump Server)： 作為作業排程與 VPN 控制中樞
• SMS SQL Server： 提供簡訊發送介面 usp_SendSMS
• Stage SQL Server： 存放待發送資料，需定期查詢並推送至 SMS Server
• 網路拓撲： 本地透過 VPN 連線可達兩台遠端伺服器

作業步驟設計

排程： 每日 09:00 執行，步驟流程控制設為 1→2→3 串序執行，任一步驟失敗則停止後續。

核心難點與解決方案

1. 分散式交易 (MSDTC) 配置

跨連結伺服器呼叫存儲過程預設會提升為分散式交易，必須在「本地」與「遠端」兩台伺服器均配置 MSDTC：

1. 執行 dcomcnfg 開啟元件服務
2. 展開「電腦」→「我的電腦」→ 右鍵「屬性」→「MSDTC」頁籤→「安全性設定」
3. 勾選以下選項：
   • 網路 DTC 存取
   • 允許遠端用戶端 / 允許遠端管理
   • 允許輸入 / 允許輸出 / 不要求驗證
4. 確認 DTC 登入帳號為 NT AUTHORITY\NetworkService
5. 重啟 MSDTC 服務生效

若遠端伺服器為叢集，需在叢集資源中啟用 MSDTC 資源並配置相同設定。

2. SQL Agent 服務帳號權限不足

現象： CmdExec 步驟預設以 SQL Agent 啟動帳號執行（通常為 NT AUTHORITY\NetworkService），此帳號非系統管理員，無法執行 rasdial 等需要提升權限的命令，導致步驟失敗。

解決方案：

• 方案 A（推薦）： 將 SQL Agent 服務帳號變更為具備「登入為批次作業」權限的網域服務帳號，並加入本地 Administrators 群組（或透過 GPO 授予特定權限）。
• 方案 B： 建立 Proxy 帳號 (msdb.dbo.sp_add_proxy)，指定具權限的認證 (sp_xp_cmdshell_proxy_account)，並在作業步驟中指定 Run As 為該 Proxy。

完整作業建立腳本範例

-- 1. 建立連結伺服器 (僅需一次)
EXEC sp_addlinkedserver @server = N'SMS_Server', @srvproduct = N'SQL Server';
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'SMS_Server', @useself = N'False', @locallogin = NULL, @rmtuser = N'sms_user', @rmtpassword = N'sms_pwd';

EXEC sp_addlinkedserver @server = N'Stage_Server', @srvproduct = N'SQL Server';
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'Stage_Server', @useself = N'False', @locallogin = NULL, @rmtuser = N'stage_user', @rmtpassword = N'stage_pwd';

-- 2. 建立作業
DECLARE @jobId BINARY(16);
EXEC msdb.dbo.sp_add_job
    @job_name = N'CrossVPN_SMS_Dispatch',
    @enabled = 1,
    @description = N'每日09:00撥VPN發送簡訊',
    @job_id = @jobId OUTPUT;

-- 步驟1: 撥VPN
EXEC msdb.dbo.sp_add_jobstep
    @job_id = @jobId,
    @step_name = N'DialVPN',
    @subsystem = N'CmdExec',
    @command = N'rasdial "CorpVPN" "svc_sqlagent" "P@ssw0rd!"',
    @on_success_action = 3,  -- 進入下一步
    @on_fail_action = 2;     -- 失敗結束作業

-- 步驟2: T-SQL 業務邏輯
EXEC msdb.dbo.sp_add_jobstep
    @job_id = @jobId,
    @step_name = N'DispatchSMS',
    @subsystem = N'TSQL',
    @command = N'
        -- 從 Stage 讀取待發送資料
        DECLARE @msg TABLE (Phone VARCHAR(20), Content NVARCHAR(500));
        INSERT INTO @msg
        SELECT Phone, Content FROM [Stage_Server].[StageDB].[dbo].[PendingSMS] WHERE Status = 0;

        -- 逐筆呼叫 SMS Server SP
        DECLARE @phone VARCHAR(20), @content NVARCHAR(500);
        DECLARE cur CURSOR FAST_FORWARD FOR SELECT Phone, Content FROM @msg;
        OPEN cur;
        FETCH NEXT FROM cur INTO @phone, @content;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN TRY
                EXEC [SMS_Server].[SMSDB].[dbo].[usp_SendSMS] @phone, @content;
                UPDATE [Stage_Server].[StageDB].[dbo].[PendingSMS] SET Status = 1, SentTime = GETDATE() WHERE Phone = @phone AND Content = @content;
            END TRY
            BEGIN CATCH
                -- 錯誤記錄
                INSERT INTO [Stage_Server].[StageDB].[dbo].[SMS_ErrorLog] (Phone, Content, ErrorMsg, ErrorTime)
                VALUES (@phone, @content, ERROR_MESSAGE(), GETDATE());
            END CATCH
            FETCH NEXT FROM cur INTO @phone, @content;
        END
        CLOSE cur; DEALLOCATE cur;
    ',
    @database_name = N'master',
    @on_success_action = 3,
    @on_fail_action = 2;

-- 步驟3: 掛斷VPN
EXEC msdb.dbo.sp_add_jobstep
    @job_id = @jobId,
    @step_name = N'HangupVPN',
    @subsystem = N'CmdExec',
    @command = N'rasdial "CorpVPN" /disconnect',
    @on_success_action = 1,  -- 成功結束作業
    @on_fail_action = 2;

-- 排程
EXEC msdb.dbo.sp_add_jobschedule
    @job_id = @jobId,
    @name = N'Daily_0900',
    @freq_type = 4, @freq_interval = 1, @active_start_time = 90000;

-- 套用至本地伺服器
EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)';

總結與最佳實踐

1. VPN 連線不穩定： 在 CmdExec 步驟中加入重試邏輯（如 PowerShell 腳本替代 rasdial），並在 T-SQL 步驟前檢查 VPN 介面狀態。
2. 分散式交易效能： 若頻繁跨伺服器呼叫，考慮改用 Service Broker、Kafka 或 HTTP API 非同步解耦。
3. 安全性： 避免在作業步驟明文寫入密碼，改用 cmdkey 儲存憑證或 Azure Key Vault 整合。
4. 監控告警： 配置作業失敗郵件通知，並將關鍵步驟耗時、VPN 連線狀態納入監控儀表板。

此架構雖屬「曲線救國」，但在無法直接開放防火牆、需隔離網段的合規場景下，是一個經實戰驗證的可行方案。