萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

SQL Server Linked Server 連結 Oracle 完整設定圖解(含權限陷阱)

架構圖

SQL Server (Express) → **OLE DB Provider (MSDAORA)** → Oracle Client (10g/11g/19c) → Oracle Listener (1521) → Oracle DB

Step 1:建立 Linked Server

EXEC master.dbo.sp_addlinkedserver
  @server = N'ORA_LINK',        -- Linked Server 名稱
  @srvproduct = N'Oracle',
  @provider = N'MSDAORA',       -- Microsoft OLE DB Provider for Oracle
  @datasrc = N'ORCL_TNS';       -- tnsnames.ora 裡的別名
GO

Step 2:設定登入對應

EXEC master.dbo.sp_addlinkedsrvlogin
  @rmtsrvname = N'ORA_LINK',
  @locallogin = NULL,           -- 所有本地登入對應同一組遠端帳密
  @useself = N'False',
  @rmtuser = N'orauser',        -- Oracle 帳號
  @rmtpassword = N'orapwd';     -- Oracle 密碼
GO

Step 3:Oracle Client 端 tnsnames.ora 設定

路徑:`%ORACLE_HOME%\network\admin\tnsnames.ora`

ORCL_TNS =
  (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.210)(PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = ORCL))
  )

關鍵:SQL Server 機器**必須安裝 Oracle Client**,且 `tnsnames.ora` 可解析 `@datasrc` 指定的別名。

Step 4:測試查詢

-- 四部分命名:LinkedServer..Schema.Table
SELECT * FROM ORA_LINK..SCOTT.EMP;

輸出範例:

EMPNO ENAME      JOB              MGR HIREDATE  SAL       COMM DEPTNO
----- ---------- --------- ---------- --------- --------- ----- ------
 7369 SMITH      CLERK           7902 17-DEC-80  800                20
 7499 ALLEN      SALESMAN        7698 20-FEB-81 1600        300     30
...

權限陷阱:MSDAORA 找不到 Oracle Client

**現象**:建好 Linked Server,查詢報 `The OLE DB provider "MSDAORA" has not been registered` 或 `Provider cannot be found`。

**根因**:Oracle Client 安裝目錄權限不足,SQL Server 服務帳號(通常是 `NT Service\MSSQL$SQLEXPRESS` 或網域帳號)無法讀取 `ora*.dll`。

**解法**(以 `E:\oracle\product\10.2.0\client_1` 為例):

  1. 右鍵該目錄 → **內容** → **安全性** → **編輯** → **新增** → 輸入 `Everyone` → **完全控制**。
  2. 或精準給 SQL Server 服務帳號 `Read & Execute`、`List folder contents`、`Read`。
  3. **重啟 SQL Server 服務**(或重開機)生效。

進階:改用 Oracle OLE DB (OraOLEDB.Oracle) 取代 MSDAORA

MSDAORA 已停止更新,建議改用 Oracle 官方 Provider:

EXEC sp_addlinkedserver
  @server = 'ORA_LINK2',
  @srvproduct = 'Oracle',
  @provider = 'OraOLEDB.Oracle',
  @datasrc = 'ORCL_TNS';

支援更多資料型別、效能更好、支援 12c+ 新特性。

結語

SQL Server 連 Oracle 三步驟:`sp_addlinkedserver` → `sp_addlinkedsrvlogin` → `tnsnames.ora`。**權限設定是最常被忽略的關鍵**,給 Oracle Client 目錄正確權限並重啟服務,通常即可解決「找不到 Provider」問題。生產環境建議改用 `OraOLEDB.Oracle`。

copyright © 萬盛學電腦網 all rights reserved