前置作業:啟用 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;
四種存取方式比較
| 方式 | 適用場景 | 需建立物件 | 效能/安全性 |
| Linked Server | 頻繁、固定跨伺服器查詢 | 是 (永久) | 最佳化工具可用遠端統計資訊,支援分散式交易 |
| OPENQUERY | 複雜遠端邏輯、下推運算 | 需 Linked Server | 在遠端執行完整查詢,減少網傳資料量 |
| OPENROWSET | 臨時、一次性存取 | 否 | 即時連線,無法使用遠端統計,適合簡單查詢 |
| OPENDATASOURCE | 臨時、一次性,連線字串彈性高 | 否 | 同 OPENROWSET,語法更接近連線字串 |
Linked Server 建立與使用
-- 建立
EXEC sp_addlinkedserver @server = N'ITSV', @srvproduct = N'', @provider = N'SQLNCLI', @datasrc = N'RemoteIP';
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'ITSV', @useself = N'FALSE', @locallogin = NULL, @rmtuser = N'user', @rmtpassword = N'pwd';
-- 查詢
SELECT * FROM ITSV.DatabaseName.dbo.TableName;
-- 匯入
SELECT * INTO LocalTable FROM ITSV.DatabaseName.dbo.TableName;
-- 刪除
EXEC sp_dropserver 'ITSV', 'droplogins';
OPENROWSET 範例集錦
-- OLEDB (SQLNCLI 推薦 2005+)
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=RemoteIP;UID=user;PWD=pwd;Database=DB', 'SELECT * FROM dbo.Table');
-- 信任連線
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=RemoteIP;Trusted_Connection=yes;', 'SELECT * FROM dbo.Table');
-- 匯入建表
SELECT * INTO LocalTable FROM OPENROWSET('SQLNCLI', 'Server=RemoteIP;UID=user;PWD=pwd;', 'SELECT * FROM DB.dbo.Table');
-- 寫入遠端
INSERT OPENROWSET('SQLNCLI', 'Server=RemoteIP;UID=user;PWD=pwd;', 'SELECT Col1,Col2 FROM DB.dbo.Table')
SELECT Col1, Col2 FROM LocalTable;
-- 更新遠端
UPDATE r SET r.ColA = l.ColA
FROM OPENROWSET('SQLNCLI', 'Server=RemoteIP;UID=user;PWD=pwd;', 'SELECT ColA,PK FROM DB.dbo.Table') r
JOIN LocalTable l ON r.PK = l.PK;
OPENQUERY 範例(需先有 Linked Server)
SELECT * FROM OPENQUERY(ITSV, 'SELECT * FROM DB.dbo.Table WHERE Status=1');
INSERT OPENQUERY(ITSV, 'SELECT Col1,Col2 FROM DB.dbo.Table')
SELECT Col1, Col2 FROM LocalTable;
OPENDATASOURCE 範例
SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=RemoteIP;User ID=user;Password=pwd').DB.dbo.Table;
INSERT OPENDATASOURCE('SQLNCLI', 'Data Source=RemoteIP;User ID=user;Password=pwd').DB.dbo.Table
SELECT * FROM LocalTable;
安全與效能最佳實踐
- 最小權限:遠端登入帳號僅授予必要資料庫的 SELECT/INSERT/UPDATE。
- 優先 OPENQUERY:複雜篩選、聯結在遠端完成,僅傳回結果集。
- 避免 SELECT *:明確列出欄位,減少網路傳輸與鎖定範圍。
- 批次作業:大量資料移動用 SSIS/BCP/備份還原,勿用 T-SQL 循環。
- 防火牆/VPN:僅允許受信任 IP 存取 1433 埠。
常見錯誤排查
SQL Server 阻止了對組件 'Ad Hoc Distributed Queries' 的存取 → 未啟用選項。
登入失敗 → 檢查遠端 SQL 驗證模式、防火牆、帳號密碼、SPN。
MSDTC 交易失敗 → 分散式交易需啟用 MSDTC 並配置網路存取。