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

步驟:
A. 安裝ODACB. 系統設定與重啟C. 新增Oracle的Linked ServerD. 設定權限; E. 執行搜尋

錯誤訊息:

TOP

A. 安裝ODAC

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

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

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

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

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

B. 系統設定與重啟

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

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

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

8. 新增系統變數:在 ⌜ 系統變數⌟, ⌜ 按新增⌟。

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

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

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

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

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

14. 編輯tnsnames.ora:用 ⌜ 記事本 ⌟ 開啟 ⌜ tnsnames.ora ⌟ ,將Oracle資訊修改如下(黃色請調整)

ORADB =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
  (CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = Oracle_name)
  )
)

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

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

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

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

TOP

C. 新增Oracle的Linked Server

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

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

20. 新增Linked Server:切換⌜安全性⌟後,點選⌜使用此安全性內容建立⌟,輸入⌜遠端登入⌟和⌜指定密碼⌟。

21. 新增Linked Server:切換⌜伺服器選項⌟後,⌜RPC⌟和⌜RPC輸出⌟皆選擇⌜True⌟,按⌜確定⌟。

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

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

上述第21步驟執行後,出現⌜無法建立連結伺服器 “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位元版本:參考上述步驟1。
  3. SQL Server的環境變數設定不正確或未設定:參考上述步驟7~12。
  4. tnsnames.ora的Oracle設定不正確:到Oracle確認database service name與服務的IP。
  5. SQL服務沒重啟:參考上述步驟15。
  6. Oracle Provider的設定沒有允許Inprocess:參考上述步驟17。
  7. Linked Server的帳密錯誤:參考上述步驟20。
  8. Linked Server的RPC設定未啟用:參考上述步驟21。

TOP

D. 設定權限

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

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

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

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

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

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

但是這樣設定,account可以執行所有的Linked Server,無法設定個別Linked Server給特定帳號執行。

目前我沒有找到方法設定個別Linked Server給特定帳號執行。

TOP

E. 執行搜尋

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’

F. 參考資料

發表迴響

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

WordPress.com 標誌

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

Google photo

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

Twitter picture

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

Facebook照片

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

連結到 %s

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