SQL Server 使用OPENROWSET訪問ORACLE遇到的各種坑總結

在SQL Server中使用OPENROWSET訪問ORACLE數據庫時，你可能會遇到各種坑，下面一一梳理一下你會遇到的一些坑。

?

1：數據庫沒有開啟"Ad Hoc Distributed Queries"選項，那么你就會遇到下面坑。

?

SELECT?TOP 10 * FROM?OPENROWSET('OraOLEDB.Oracle', 'ESCM_134';'test';'test', 'SELECT * FROM TEST.MY_SET')

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

?

?

出現這個錯誤，只需要開啟數據庫"Ad Hoc Distributed Queries"選項即可。如下所示

sp_configure? 'show advanced option',1;

GO

RECONFIGURE

sp_configure 'Ad Hoc Distributed Queries',1;

GO

RECONFIGURE

?

2：遇到“The OLE DB provider "OraOLEDB.Oracle" for linked server ....."這個坑

?

SELECT?TOP 10 * FROM?OPENROWSET('OraOLEDB.Oracle', 'ESCM_134';'test';'test', 'SELECT * FROM TEST.MY_SET')

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "(null)" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server "(null)".

?

?

解決這個也比較簡單，使用SSMS連接到數據庫后，在“Server Objects”->"Linked Servers"->"OraOLEDB.Oracle"下勾選“Allow inprocess"選項。注意，如果不重啟，無法使之生效，依然會報上面錯誤。

?

?

3：遇到“OLE DB provider "OraOLEDB.Oracle" for linked server "(null)" returned message "ORA-12154: TNS: 無法解析指定的連接標識符...."這個坑

SELECT?TOP 10 * FROM?OPENROWSET('OraOLEDB.Oracle', 'ESCM_134';'test';'test', 'SELECT * FROM TEST.MY_SET')

OLE DB provider "OraOLEDB.Oracle" for linked server "(null)" returned message "ORA-12154: TNS: 無法解析指定的連接標識符".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "(null)".

?

遇到這個問題有幾種情形：

1： 你沒有在TNS配置文件里面配置相關ORACLE實例信息。

2： SQL Server數據庫是64位的，你只安裝了32bit數據庫，配置了Oracle Client 32bit下的TNS，或者Oracle Client 32/64位都安裝了，但是你只配置了32位下的TNS。其實只需要配置64下的TNS即可。因為64位的SQL Server肯定調用64位的驅動程序。

?

4：普通賬號遇到“Ad hoc access to OLE DB provider 'OraOLEDB.Oracle' has been denied. You must access this provider through a linked server."錯誤， 具有sysadmin角色的賬號執行下面SQL正常，但是非常普通的賬號就一直報下面錯誤

?

?

SELECT TOP 10 * FROM OPENROWSET('OraOLEDB.Oracle', 'ESCM_134';'test';'test', 'SELECT * FROM TEST.MY_SET')

Msg 7415, Level 16, State 1, Line 1

Ad hoc access to OLE DB provider 'OraOLEDB.Oracle' has been denied. You must access this provider through a linked server.

?

解決方法，在服務器打開注冊表，在HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\Providers\OraOLEDB.Oracle下（當然不同版本或命名實例的數據庫，這個注冊表路徑有啥不同，根據實際情況找到OraOLEDB.Oracle），新建DisallowAdHocAccess選項即可解決問題。

?

?

DisallowAdHocAccess屬性設置為 1，SQL Server 不允許特別通過 OPENROWSET 和 OPENDATASOURCE 函數根據指定的 OLE DB 提供程序訪問。如果您嘗試調用這些函數中的特殊查詢，您會收到類似于以下內容的錯誤消息

?

• A change of the value of DisallowAdHocAscess from 1 to 0 would not require a restart of the SQL Service, whereas a change from 0 to 1 would have to have a SQL Service restart for the change that was made to become effective.
• With the DisallowAdHocAccess property set to 1, SQL Server does not allow ad hoc access through the OPENROWSET and the OPENDATASOURCE functions against the specified OLE DB provider. If you try to call these functions in ad hoc queries, you receive an error message that resembles the following:

  Server: Msg 7415, Level 16, State 1, Line 1 Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

  In other words, with the DisallowAdHocAccess property set to 1 for a specific OLE DB provider, you must use a predefined linked server setup for the specific OLE DB provider. You can no longer pass in an ad hoc connection string that references that provider to the OPENROWSET or the OPENDATASOURCE function.

?

參考資料：

https://support.microsoft.com/zh-cn/kb/327489

?