[MS SQL] SQL Server建立Oracle的Linked Server

最近在處理MS SQL和Oracle之間的異質資料庫資料交換事宜,遇到很多問題,找了不少資料,最後整理出該篇文章,有關Oracle設定DB Link連線到MS SQL,請參考:[Oracle] Oracle建立MS SQL的DB Link

本文摘要

錯誤訊息:


範例說明

以【Microsoft SQL 2017】Linked Server到【Oracle 11g R2】為例

【Microsoft SQL 2017】

  • MS SQL Server IP:10.0.1.2
  • 預設的服務Port:1433
  • 設定Linked Server的資料庫名稱:MSSQL_DB1
  • DB owner:MSSQL_user
  • DB owner的password:MSSQL_pw
  • Linked Server to DB 的alias Name:ORADB
  • Linked Servrt Name:ORA_TEST

【Oracle 11g R2】

  • ORACLE_HOME:/u01/app/oracle/product/11.2.0.4/db1
  • Oracle Server IP:10.0.2.4
  • 預設的服務Port:1521
  • SID Name:sid
  • SERVICE NAME:standby.abc.com.tw
  • Schema名稱:ORA_DB1
  • 其授權的帳號:ORA_user
  • 帳號的密碼:ORA_pw

TOP


建置MS SQL Linked Server環境

1. 安裝ODAC

1-1. 下載ODAC安裝檔:依Oracle版本下載ODAC 64bit版本的壓縮檔。

本篇文章範例之環境與需求:MS SQL Server 2017(Windows Server 2016 X64),Oracle 11g R2

1-2. 檔案上傳並解壓縮:上述壓縮檔上傳到 MS SQL Server主機,解壓縮到 ⌜ D:\tmp\ODAC112040Xcopy_64bit ⌟。

1-3. 啟用命令提示字元:在⌜開始⌟上面按右鍵,選擇⌜命令提示字元 (系統管理員)⌟。

1-4. ODAC安裝:切換到ODAC資料夾,執行install.bat,語法如下:

C:\Windows\system32>cd D:\tmp\ODAC112040Xcopy_64bit
C:\Windows\system32>D:
D:\tmp\ODAC112040Xcopy_64bit>install.bat oledb C:\oracle_odac ODAC

TOP

2. 系統設定與重啟

2-1. 確認是否安裝完成:到 ⌜C:\oracle_odac⌟ ,如果有檔案即完成。

2-2. 設定環境變數:搜尋⌜環境變數⌟,點選⌜編輯環境變數⌟。

2-3. 設定環境變數:點選⌜進階>環境變數⌟。

2-4. 新增系統變數:在 ⌜ 系統變數⌟, ⌜ 按新增⌟。

2-5. 新增系統變數:新增變數名稱為⌜ORACLE_HOME⌟,變數值為⌜C:\oracle_odac⌟,按 ⌜確定⌟ 。

2-6. 修改變數值:找到 ⌜Path⌟ 的系統變數,按 ⌜編輯⌟ 。

2-7. 修改變數值:新增 ⌜C:\oracle_odac⌟ 和 ⌜C:\oracle_odac\bin⌟後,按 ⌜確定⌟ 。

2-8. 關閉環境變數視窗:按 ⌜確定⌟ 關閉所有視窗。

2-9. 設定ora檔:到 ⌜C:\oracle_odac\network\admin⌟,複製 ⌜sample⌟資料夾底下的 ⌜sqlnet.ora⌟、 ⌜tnsnames.ora⌟。

2-10. 編輯tnsnames.ora:用 ⌜ 記事本 ⌟ 開啟 ⌜ tnsnames.ora ⌟ ,將Oracle資訊修改如下

ORADB =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.4)(PORT = 1521))
  (CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = sid)
  )
)

2-11. 重啟SQL服務:開啟 ⌜SQL Server 2017組態管理 ⌟

點選⌜SQL Server服務>SQL Server⌟,按右鍵選擇⌜重新啟動⌟。

2-12. 調整設定:開啟SSMS後,點選⌜伺服器物件>連結的伺服器>提供者>OraOLEDB.Oracle⌟,按右鍵選擇⌜屬性⌟。

2-13. 調整設定:勾選⌜允許Inprocess⌟後,按⌜確定⌟。

TOP


建立公共的Linked Server

1. 新增Oracle的Linked Server

1-1. 新增Linked Server:點選⌜伺服器物件>連結的伺服器⌟,按右鍵選擇⌜新增連結的伺服器⌟。

1-2. 新增Linked Server:點選⌜伺服器物件>連結的伺服器⌟,⌜連結的伺服器⌟輸入⌜ORA_TEST⌟ ( 自訂名稱 ),⌜提供者⌟選擇⌜Oracle Provider for OLE DB⌟,⌜產品名稱⌟輸入⌜Oracle⌟ ( 自行輸入,可以固定輸入Oracle ),⌜資料來源⌟輸入⌜ORALinkName⌟( 以tnsnames.ora 內設定的alias修改 )。

1-3. 新增Linked Server:切換⌜安全性⌟後,點選⌜使用此安全性內容建立⌟,輸入⌜遠端登入⌟:【ORA_user】;⌜指定密碼⌟:【ORA_pw】。

1-4. 新增Linked Server:切換⌜伺服器選項⌟後,保留預設設定,按⌜確定⌟。

(補充說明:若需在SQL Server A中透過Link Server方式去執行 SQL Server B的預設程序時,才需要開啟RPC設定SQL Server B,此處"RPC"和"RPC輸出"才要改為True)

1-5. 如果成功,會顯示其設定;如果失敗,會出現7302的錯誤訊息,如下一個項目。

錯誤訊息: 7302 無法建立連結伺服器的 OLE DB 提供者 “OraOLEDB.Oracle" 的執行個體

上述第1-5步驟執行後,出現⌜無法建立連結伺服器 “XXX" 的 OLE DB 提供者 “OraOLEDB.Oracle" 的執行個體。 (Microsoft SQL Server, 錯誤: 7302)⌟,代表Linked Server不通。

原因:解決方法

  1. SQL Server的防火牆(windows Server的系統Firewall和IP Firewall):開通port 1521的TCP。
  2. ODAC版本不支援Oracle或Windows Server不是64位元版本:參考⌜建置MS SQL Linked Server環境⌟步驟1-1。
  3. SQL Server的環境變數設定不正確或未設定:參考⌜建置MS SQL Linked Server環境⌟步驟2-4~2-13。
  4. tnsnames.ora的Oracle設定不正確:到Oracle確認database service name與服務的IP。
  5. SQL服務沒重啟:參考⌜建置MS SQL Linked Server環境⌟步驟2-11。
  6. Oracle Provider的設定沒有允許Inprocess:參考⌜建置MS SQL Linked Server環境⌟步驟2-13。
  7. Linked Server的帳密錯誤:參考⌜建立Linked Server⌟步驟1-3。
  8. Linked Server的RPC設定未啟用:參考⌜建立Linked Server⌟步驟1-4。

TOP

2. 設定權限

給特定帳號Linked Server的執行權限

前項是透過administrator設定Linked Server,所以需要設定權限給其他使用者。

2-1. 新增對應資料庫master的public角色:點選⌜安全性>登入>MSSQL_user⌟,按右鍵選擇 ⌜屬性⌟。

2-2. 新增對應資料庫master的public角色:點選⌜使用者對應⌟,右方勾選 ⌜master⌟,下方勾選 ⌜public⌟,按 ⌜確定⌟。

2-3. 授予XP_PROP_OLEDB_PROVIDER執行權限:新增查詢,執行下述語法:

USE master
GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [MSSQL_user]
GO

本節設定,所有有授權XP_PROP_OLEDB_PROVIDER的帳號都可以執行該Linked Server,設定個別帳號的Linked Server請參考下節【建立個別帳號的Linked Server】。

TOP


建立個別帳號的Linked Server

(2022/11/30更新) 以下範例這個Linked Server ⌜ORA_TEST2⌟只給帳號⌜MSSQL_user_A⌟使用,其他人都不行用

1. 新增Oracle的Linked Server

1-1. 新增Linked Server:點選⌜伺服器物件>連結的伺服器⌟,按右鍵選擇⌜新增連結的伺服器⌟。

1-2. 新增Linked Server:點選⌜伺服器物件>連結的伺服器⌟,⌜連結的伺服器⌟輸入⌜ORA_TEST2⌟ ( 自訂名稱 ),⌜提供者⌟選擇⌜Oracle Provider for OLE DB⌟,⌜產品名稱⌟輸入⌜Oracle⌟ ( 自行輸入,可以固定輸入Oracle ),⌜資料來源⌟輸入⌜ORALinkName⌟( 以tnsnames.ora 內設定的alias修改 )。

1-3. 新增Linked Server:切換⌜安全性⌟後,點選⌜不建立⌟,右上方按⌜新增⌟:⌜本機登入⌟輸入開放哪個帳號使用,例如【MSSQL_user_A】;⌜遠端使用者⌟輸入:【ORA_user】;⌜遠端密碼⌟:【ORA_pw】。

1.4 新增Linked Server:切換⌜伺服器選項⌟後,保留預設設定,按⌜確定⌟。

1.5 新增Linked Server:會彈出視窗,按⌜是⌟。

補充說明1-3. 新增Linked Server:切換⌜安全性⌟後,如果勾選【模擬】,遠端使用者和帳號都會使用MSSQL_user_A的帳號密碼來嘗試登入Oracle,如果兩個資料庫都一樣帳號和密碼才可以登入,即使是異質資料庫也可以。但是帳號不是SQL Server驗證帳號,而是Windows 登入帳號,則必須兩台都有連結伺服器上的有效登入帳號。

2. 設定權限

(同上節的第2項-設定權限)

給特定帳號Linked Server的執行權限

前項是透過administrator設定Linked Server,所以需要設定權限給其他使用者。

2-1. 新增對應資料庫master的public角色:點選⌜安全性>登入>MSSQL_user_A⌟,按右鍵選擇 ⌜屬性⌟。

2-2. 新增對應資料庫master的public角色:點選⌜使用者對應⌟,右方勾選 ⌜master⌟,下方勾選 ⌜public⌟,按 ⌜確定⌟。

2-3. 授予XP_PROP_OLEDB_PROVIDER執行權限:新增查詢,執行下述語法:

USE master
GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [MSSQL_user_A]
GO

TOP


前述Linked Server設定結果

MSSQL_user (伺服器角色為sysadmin):

  • Linked Server管理權限:有
  • 看得到所有Linked Server設定
  • ORA_TEST資料:可讀取
  • ORA_TEST2資料:不可讀取

MSSQL_user_A帳號:

  • Linked Server管理權限:無
  • 看得到公用Linked Server設定、個別設定的Linked Server
  • ORA_TEST資料:可讀取
  • ORA_TEST2資料:可讀取

MSSQL_user_A帳號

  • Linked Server管理權限:無
  • 看得到公用Linked Server設定
  • ORA_TEST資料:可讀取
  • ORA_TEST2資料:無法讀取

TOP


透過Linked Server之運用說明

1. Select資料

SELECT [column1] ,[column2], [column3], … FROM [Linked Server Name]..[Oracle Schema].[Table or View name] WHERE [column1]=’xxx’ AND …
GO

例如:

SELECT employee_ID ,name, dept_name, tel, zone FROM [ORA_TEST]..[HR].[VIEW_EMPLOYEE] WHERE dept_name=’information technology’
GO

錯誤訊息:連結伺服器的 OLE DB 提供者 “OraOLEDB.Oracle" 提供了不一致的資料行中繼資料

以上範例來說,如果發生錯誤訊息如下:

訊息 7356,層級 16,狀態 1,行 1
連結伺服器 “ORA_TEST" 的 OLE DB 提供者 “OraOLEDB.Oracle" 提供了不一致的資料行中繼資料。回報物件 “"HR"."VIEW_EMPLOYEE"" 的資料行 “zone" (編譯時間序數 5) 在編譯時間有 1 的 “LENGTH",而在執行時間有 2。

完成時間: 2021-04-26T09:18:00.7448426+08:00

網路上有人說明是權限問題,如果權限是給SQL Server帳號,無法依下述網路上提供的方式解決:

方法1:到"元件服務">"DCOM設定">"MSDAINITIALIZE",將相關主機帳號權限加入
方法2:在Select語法最後面加"OPTION (RECOMPILE)"
方法3:除了B的第17步驟設定Allow InProcess勾選,Dynamic Parameter也勾選

解決方法

SELECT * FROM OPENQUERY(Linked Server Name, ‘SELECT [column1] ,[column2], [column3], … FROM Oracle Schema.Table or View name) Where column1=’xxx’

例如:

SELECT * FROM OPENQUERY(ORA_TEST, ‘SELECT * FROM HR.VIEW_EMPLOYEE’) Where dept_name=’information technology’

TOP


參考資料

TOP

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com 標誌

您的留言將使用 WordPress.com 帳號。 登出 /  變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 /  變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 /  變更 )

連結到 %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.