解決 SQL Server 「Ad Hoc Distributed Queries」停用導致 OPENROWSET/OPENDATASOURCE 失敗

啟用腳本 (立即生效，無需重啟)

-- 1. 顯示進階選項
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- 2. 啟用 Ad Hoc Distributed Queries
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

用畢關閉 (安全最佳實務)

-- 1. 關閉 Ad Hoc Distributed Queries
EXEC sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;

-- 2. 隱藏進階選項
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

驗證目前狀態

EXEC sp_configure 'Ad Hoc Distributed Queries';
-- run_value = 1 表示啟用，0 表示停用

替代方案：連結伺服器 (更安全、可重用)

若頻繁存取同一外部資料源，建議建立 連結伺服器，不需啟用 Ad Hoc 選項：

EXEC sp_addlinkedserver 
    @server = 'ExcelData', 
    @srvproduct = 'ACE', 
    @provider = 'Microsoft.ACE.OLEDB.12.0', 
    @datasrc = 'E:\class.xlsx', 
    @provstr = 'Excel 12.0;HDR=YES';

-- 查詢
SELECT * FROM OPENQUERY(ExcelData, 'SELECT * FROM [Sheet1$]');

優點：權限可精細控制 (只授予特定登入存取連結伺服器)、連線字串集中管理、不需開放 Ad Hoc 風險。

常見連接字串參考

注意事項

• 64 位元 SQL Server 需安裝 64 位元 ACE/OLEDB Provider (Microsoft Access Database Engine 2010/2016 Redistributable)。
• 啟用 Ad Hoc 後，任何具備權限的登入皆可任意存取檔案系統資料，務必用完即關。
• 生產環境建議：永久停用 Ad Hoc，全面改用連結伺服器或 SSIS/BCP 匯入。

結語

Ad Hoc Distributed Queries 是便利與風險的雙面刃。臨時查詢可短暫啟用；長期存取請採連結伺服器或 ETL 流程，落實最小權限與最小攻擊面原則。