前言
本文將討論三項主題,分別是使用 OPENDATASOURCE、OPENROWSET 資料表函數或連結伺服器(Linked Server) 來與 Oracle 執行個體連接的設定與相關注意事項。
示範環境:
1. Windows Server 2008 R2 x64 + SQL Server 2008 R2 SP1 x64 版本。
2. Oracle 11g R2 x64 版本。
準備工作:
1. 請先安裝與組態好 Oracle Client x64 版本,並確認可以連接到此 Oracle 執行個體。
2. 重新啟動 SQL Server 服務。
3. 先啟用「特定分散式查詢(Ad Hoc Distributed Queries)」選項,請參考以下的文章:
SQL Server 2008 介面區組態(Surface Area Configuration,SAC):
啟用 Ad Hoc Distributed Queries。訊息 15281,SQL Server 已封鎖元件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 之存取,因為此元件已經由此伺服器的安全性組態關閉。
http://sharedderrick.blogspot.com/2011/02/sql-server-2008-surface-area.html
(一) 使用 OPENROWSET 資料表函數
包含所有從 OLE DB 資料來源存取遠端資料所需的連接資訊。
這個方法是存取連結伺服器資料表的另一個方法,而且是使用 OLE DB 來連接和存取遠端資料的單次特定方法。
對於更常用到的 OLE DB 資料來源參考,請改用連結的伺服器。您可以依照資料表名稱的相同方式,在查詢的 FROM 子句中參考 OPENROWSET 函數。
根據 OLE DB 提供者的能力而定,OPENROWSET 函數也可以被當做 INSERT、UPDATE 或 DELETE 陳述式的目標資料表加以參考。
雖然查詢可以傳回多個結果集,但是 OPENROWSET 只能傳回第一個。
語法:
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ <bulk_options> ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
</bulk_options>
範例程式碼:
-- 01_使用 SYSDATA 函數查詢 Oracle 執行個體上的系統日期
SELECT *
FROM OPENROWSET ('OraOLEDB.Oracle', 'test01'; 'SYSTEM'; 'Pa$$w0rd',
'SELECT SYSDATE FROM dual')

圖1:使用 SYSDATA 函數查詢 Oracle 執行個體上的系統日期
-- 02_查詢 Dynamic Performance (V$) Views:v$version 取得 Oracle 執行個體的相關版本資訊
SELECT *
FROM OPENROWSET ('OraOLEDB.Oracle', 'test01'; 'SYSTEM'; 'Pa$$w0rd',
'SELECT * FROM v$version')

圖2:取得 Oracle 執行個體的相關版本資訊
-- 03_若是有安裝 Oracle 範例資料庫,可以查詢資料表:HR.DEPARTMENTS
SELECT *
FROM OPENROWSET ('OraOLEDB.Oracle', 'test01'; 'SYSTEM'; 'Pa$$w0rd',
'SELECT * FROM HR.DEPARTMENTS')

圖3:若是有安裝 Oracle 範例資料庫,可以查詢資料表:HR.DEPARTMENTS
(二) 使用 OPENDATASOURCE 函數
提供特定連接資訊做為四部分物件名稱,而不使用連結伺服器名稱。
語法:
OPENDATASOURCE ( provider_name, init_string )
範例程式碼:
-- 01_若是有安裝 Oracle 範例資料庫,可以查詢資料表:HR.DEPARTMENTS
SELECT * FROM OPENDATASOURCE
('OraOLEDB.Oracle','Data Source=test01; User ID=SYSTEM; Password=Pa$$w0rd')
..HR.DEPARTMENTS

圖 4:若是有安裝 Oracle 範例資料庫,可以查詢資料表:HR.DEPARTMENTS
(三)實作練習:
使用連結伺服器(Linked Server) 連接到 Oracle;以 SQL Server 2008 R2 與 Oracle 11g R2 為例
準備工作:
1. 請先安裝與組態好 Oracle Client x64 版本,並確認可以連接到此 Oracle 執行個體。
2. 重新啟動 SQL Server 服務。
工作1:檢視 SQL Server 上是否有偵測到 Oracle Client 資料提供者
步驟01. 使用 SSMS 管理工具,連接到目標的伺服器。
步驟02. 展開「伺服器物件」\「連接的伺服器」,展開「提供者」節點:

圖1:尚未安裝 Oracle Client

圖2:已經安裝 Oracle Client
在上圖 2 中,可以看到已經增加了 OraOLEDB.Oracle
步驟03. 點選此「OraOLEDB.Oracle」提供者,滑鼠右鍵,選擇「屬性」。

圖3:檢視「OraOLEDB.Oracle」提供者的屬性
工作2:建立連結伺服器
步驟01. 使用 sp_addlinkedserver 建立「連結伺服器」:
建立連結伺服器。連結伺服器可讓您對 OLE DB 資料來源存取分散式異質性查詢。
當您使用 sp_addlinkedserver 建立連結伺服器之後,即可對這部伺服器執行分散式查詢。
如果連結伺服器被定義為 SQL Server 的執行個體,就可以執行遠端預存程序。
-- 建立連結伺服器
語法:
sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]
[ , [ @provider= ] 'provider_name' ]
[ , [ @datasrc= ] 'data_source' ]
[ , [ @location= ] 'location' ]
[ , [ @provstr= ] 'provider_string' ]
[ , [ @catalog= ] 'catalog' ]
-- 範例:
IF EXISTS (SELECT srv.name FROM sys.servers srv
WHERE srv.server_id != 0 AND srv.name = N'Oracle_test01')
EXEC master.dbo.sp_dropserver @server=N'Oracle_test01', @droplogins='droplogins'
GO
EXEC sp_addlinkedserver
@server ='Oracle_test01', @srvproduct = 'Oracle',
@provider = 'OraOLEDB.Oracle', @datasrc = 'test01 '
GO

圖4:檢視Oracle連結伺服器一般頁籤

圖5:檢視Oracle連結伺服器的安全性頁籤
在上圖 5 中,可以觀察到在「安全性」頁籤,目前使用的設定值是:「使用登入的目前安全性內容建立」。

圖6:檢視Oracle連結伺服器的伺服器選項頁籤
步驟02. 使用 sp_addlinkedsrvlogin 建立與遠端伺服器的安全性帳戶之對應關係:
建立或更新 SQL Server 本機執行個體登入與遠端伺服器安全性帳戶之間的對應。
-- 語法:
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'TRUE' | 'FALSE' | 'NULL']
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]
-- 範例:
EXEC sp_addlinkedsrvlogin
@rmtsrvname ='Oracle_test01', @useself ='FALSE',
@rmtuser ='SYSTEM', @rmtpassword = 'Pa$$w0rd'
GO

圖7:檢視Oracle連結伺服器的安全性頁籤
在上圖 7 中,可以觀察到目前使用的設定值是:「使用此安全性內容建立」,並且有設定「遠端登入」與「指定密碼」。
步驟03. 點選先前所建立的「連結伺服器」:Oracle_test01,滑鼠右鍵,選擇「測試連結」。

圖8:點選「測試連接」
步驟04. 在「連結的伺服器」視窗,點選「確定」。

圖9:連接測試_成功
若是輸入的帳戶或是密碼不正確時,所遭遇的錯誤訊息如下:
與連結伺服器的連接測試失敗。
無法初始化連結伺服器 "Oracle_test01" 的 OLE DB 提供者 "OraOLEDB.Oracle" 的資料來源物件。
連結伺服器 "Oracle_test01" 的 OLE DB 提供者 "OraOLEDB.Oracle" 傳回訊息 "ORA-01017: invalid username/password; logon denied"。
(Microsoft SQL Server, 錯誤: 7303)
如需說明,請按一下: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

圖10:若帳戶或密碼不正確時的錯誤訊息
步驟04. 調整「連結伺服器」的安全性,請參考以下的範例程式碼:
-- 設定:指定的 Windows 帳戶與遠端伺服器的安全性帳戶之對應關係
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'Oracle_test01', @locallogin = N'SQL2K8R2BI\Administrator',
@useself = N'False', @rmtuser = N'SYSTEM', @rmtpassword = N'Pa$$w0rd'
GO
-- 設定:指定的 SQL 帳戶與遠端伺服器的安全性帳戶之對應關係
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'Oracle_test01', @locallogin = N'sa',
@useself = N'False', @rmtuser = N'SYSTEM', @rmtpassword = N'Pa$$w0rd'
GO
-- 使用 sp_droplinkedsrvlogin 移除在執行 SQL Server 之本機伺服器的登入與連結伺服器的登入之間的現有對應。
EXEC master.dbo.sp_droplinkedsrvlogin
@rmtsrvname = N'Oracle_test01', @locallogin = NULL
GO

圖11:設定本機伺服器登入與遠端伺服器登入對應
在上圖 11 中,可以觀察到設定的是「不建立」。
僅能使用指定的 Windows 帳戶與 SQL 帳戶來登入本機伺服器後,才能連接到遠端的伺服器。
步驟05. 測試與「連結伺服器」,執行以下的範例程式碼:

圖12:SYSDATA 函數查詢 Oracle 執行個體上的系統日期
-- OPENQUERY 函數:查詢 Dynamic Performance (V$) Views:v$version 取得 Oracle 執行個體的相關版本資訊
SELECT * FROM OPENQUERY(Oracle_test01,'SELECT * FROM v$version')

圖13:Oracle 執行個體的相關版本資訊
-- 若是有安裝 Oracle 範例資料庫,可以查詢資料表:HR.DEPARTMENTS
SELECT * FROM Oracle_test01..HR.DEPARTMENTS
-- OPENQUERY 函數:若是有安裝 Oracle 範例資料庫,可以查詢資料表:HR.DEPARTMENTS
SELECT * FROM OPENQUERY(Oracle_test01,'SELECT * FROM HR.DEPARTMENTS')

圖14:查詢範例資料表HR.DEPARTMENTS
若是使用 SSMS 管理工具來組態「連結伺服器」與 Oracle 執行個體連線,可能會遭遇到以下的錯誤訊息:
執行 Transact-SQL 陳述式或批次時發生例外狀況。 (Microsoft.SqlServer.ConnectionInfo)
不允許對系統目錄的特定更新。 (Microsoft SQL Server, 錯誤: 259)
如需說明,請按一下: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=259&LinkId=20476
圖15:不允許對系統目錄的特定更新之錯誤訊息
不知道為何使用 SSMS 管理工具執行設定「連結伺服器」的組態設定時,
SSMS 管理工具會自動產生以下修改系統資料表:[master].[dbo].[sysservers] 的範例程式碼範例程式碼:
update [master].[dbo].[sysservers] set [datasource] = 'test01' where [srvname] = 'Oracle_test01'
GO
--
訊息 259,層級 16,狀態 1,行 1
不允許對系統目錄的特定更新。
--
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.

圖16:訊息259_不允許對系統目錄的特定更新
這功能是用來更新系統資料表的相關資料,但系統內定是不得更新系統資料表。
可能的解決方案:
仍是可以使用 SSMS 管理工具來組態。
但是,不按下「確定」,而是改以產生「指令碼」的方式後。
再自行刪除上述修改系統資料表:[master].[dbo].[sysservers] 的範例程式碼,就可以正常的執行後續的指令碼。
結語
在本期中,介紹了使用 OPENDATASOURCE、OPENROWSET 資料表函數或連結伺服器(Linked Server) 來與 Oracle 執行個體連接的設定與相關注意事項。