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