SQL Server 存取遠端資料庫完整指南：Linked Server、OPENROWSET、OPENDATASOURCE、OPENQUERY

前置準備：啟用 Ad Hoc Distributed Queries

-- 僅需一次，開啟後建議用畢關閉以減少攻擊面
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;

-- 用畢關閉
EXEC sp_configure 'Ad Hoc Distributed Queries', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;

四種存取方式對比

1. Linked Server 建立與使用

-- 建立
EXEC sp_addlinkedserver 
    @server = 'LNK_PROD', 
    @srvproduct = '', 
    @provider = 'SQLNCLI',  -- SQL 2005+ 建議 SQLNCLI / MSOLEDBSQL
    @datasrc = '192.168.10.50';  -- IP 或主機名

-- 登入對應 (本地登入 → 遠端登入)
EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = 'LNK_PROD', 
    @useself = 'FALSE', 
    @locallogin = NULL,  -- NULL 表所有本地登入
    @rmtuser = 'remote_user', 
    @rmtpassword = 'StrongPassword';

-- 查詢 (四部分命名)
SELECT * FROM [LNK_PROD].[SalesDB].[dbo].[Orders];

-- 匯入
SELECT * INTO LocalOrders FROM [LNK_PROD].[SalesDB].[dbo].[Orders];

-- 更新遠端
UPDATE r SET r.Status = l.Status
FROM [LNK_PROD].[SalesDB].[dbo].[Orders] r
JOIN LocalOrders l ON r.OrderID = l.OrderID;

-- 移除
EXEC sp_dropserver 'LNK_PROD', 'droplogins';

2. OPENROWSET 即時查詢 (免建立 Linked Server)

-- SQLNCLI (SQL 2005+ 推薦)
SELECT * FROM OPENROWSET(
    'SQLNCLI', 
    'Server=192.168.10.50;UID=remote_user;PWD=StrongPassword;', 
    'SELECT OrderID, CustID, Amount FROM SalesDB.dbo.Orders WHERE OrderDate > ''2024-01-01'''
) AS t;

-- 信任連線 (Windows 驗證)
SELECT * FROM OPENROWSET(
    'SQLNCLI', 
    'Server=192.168.10.50;Trusted_Connection=yes;', 
    'SalesDB.dbo.Orders'
) AS t;

-- 匯入/更新/刪除 (需資料來源支援)
INSERT OPENROWSET('SQLNCLI', 'Server=...;Trusted_Connection=yes;', 'SELECT col FROM DB.dbo.T')
SELECT col FROM LocalT;

UPDATE OPENROWSET('SQLNCLI', '...', 'SELECT col FROM DB.dbo.T WHERE id=1')
SET col = 'new';

DELETE FROM OPENROWSET('SQLNCLI', '...', 'SELECT col FROM DB.dbo.T WHERE id=1');

3. OPENQUERY (需先有 Linked Server，遠端執行)

-- 查詢
SELECT * FROM OPENQUERY(LNK_PROD, 'SELECT * FROM SalesDB.dbo.Orders WHERE Amount > 1000');

-- 匯入遠端
INSERT OPENQUERY(LNK_PROD, 'SELECT col FROM SalesDB.dbo.T')
SELECT col FROM LocalT;

-- 更新遠端
UPDATE OPENQUERY(LNK_PROD, 'SELECT col FROM SalesDB.dbo.T WHERE id=1')
SET col = 'new';

4. OPENDATASOURCE (單次連線字串)

SELECT * FROM OPENDATASOURCE(
    'SQLNCLI', 
    'Data Source=192.168.10.50;User ID=remote_user;Password=StrongPassword'
).SalesDB.dbo.Orders;

安全性最佳實務

• **最小權限**：遠端登入布號僅授予必要資料庫的 `SELECT/INSERT/UPDATE/DELETE`，嚴禁 `sysadmin`/`db_owner`。
• **避免明文密碼**：優先使用 Windows 驗證 (`Trusted_Connection=yes`) 或 Linked Server 登入對應 (`sp_addlinkedsrvlogin`)，勿在程式碼/腳本中硬編碼密碼。
• **防火牆限制**：遠端 SQL 連接埠 (預設 1433) 僅允許已知應用伺服器 IP 存取。
• **稽核**：啟用 `LOGON`、`EXECUTE` 稽核追蹤跨伺服器存取。

常見錯誤排查