圖解 SQL Server 2005 連結 Oracle 11g 完整步驟：從 Client 安裝到 Linked Server 測試

架構概覽

SQL Server 透過 Linked Server 機制，使用 OLE DB Provider (MSDAORA) 連接 Oracle Client，進而存取 Oracle 11g 資料庫。

步驟 1：安裝 Oracle 11g Client

• 下載對應作業系統的 Oracle 11g Client (Runtime 或 Administrator 安裝類型)
• 安裝過程選擇 Custom，確保勾選 Oracle Net、OLE DB Provider、ODBC Driver
• 安裝完成後設定 ORACLE_HOME、PATH 環境變數

步驟 2：Oracle Net Manager 設定客戶端連線 (TNS 別名)

1. 啟動 Oracle Net Manager (Start → Oracle → Configuration and Migration Tools → Net Manager)
2. 展開 Local → Service Naming，點擊 + 新增
3. Net Service Name：輸入別名 (例：TEST)
4. 選擇 TCP/IP 協定
5. 填寫 Oracle Server Hostname/IP 與 Port (預設 1521)
6. Service Name：輸入 Oracle 實例 Service Name (非 SID)
7. 完成後 File → Save Network Configuration，產生 tnsnames.ora

步驟 3：測試 Oracle Client 連線

SQLPLUS USER/PASS@TEST

確認能正常登入，驗證 TNS 別名、網路、防火牆、帳號密碼無誤。

步驟 4：SQL Server 建立 Linked Server

1. SSMS 連接 SQL Server 2005，展開 Server Objects → Linked Servers，右鍵 New Linked Server
2. General 頁籤：
   • Linked Server：自訂名稱 (例：ORACLELINK)
   • Server Type：Other Data Source
   • Provider：Microsoft OLE DB Provider for Oracle (MSDAORA)
   • Product Name：Oracle
   • Data Source：步驟 2 設定的 TNS 別名 (TEST)
   • Provider String：MSDAORA
3. Security 頁籤：
   • 選擇 Be made using this security context
   • 輸入 Oracle Remote Login (用戶名) 與 Password
4. 點擊 OK 建立

步驟 5：連線測試 (OPENQUERY)

SELECT * FROM OPENQUERY(ORACLELINK, 'SELECT OBJNAME FROM OADEV.TABLENAME');

注意： Oracle 物件名稱 (Schema、Table) 建議全大寫，避免大小寫敏感問題。

常見錯誤與解決

錯誤：MSDAORA 無法提供連線

編輯 $ORACLE_HOME/NETWORK/ADMIN/SQLNET.ORA，註解掉：

# SQLNET.AUTHENTICATION_SERVICES = (NTS)

Windows 驗證 (NTS) 在跨機器 Linked Server 常導致認證失敗，改用資料庫帳號密碼驗證即可。

總結檢查清單

• [ ] Oracle Client 安裝含 OLE DB Provider
• [ ] Net Manager 建立 TNS 別名並測試通
• [ ] Linked Server Provider 選 MSDAORA，Data Source 填 TNS 別名
• [ ] Security 設定 Oracle 專用帳號密碼
• [ ] OPENQUERY 測試，物件名全大寫
• [ ] 連不通時檢查 SQLNET.ORA 的 NTS 設定