首页 > 数据库

SQL Server 使用OPENROWSET访问ORACLE遇到的各种坑总结

2016-08-18 19:00:13

在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.

 

clipboard

 

出现这个错误,只需要开启数据库"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)".

 

clipboard[1]

 

解决这个也比较简单,使用SSMS连接到数据库后,在“Server Objects”->"Linked Servers"->"OraOLEDB.Oracle"下勾选“Allow inprocess"选项。注意,如果不重启,无法使之生效,依然会报上面错误。

 

clipboard[2]

 

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.

clipboard[3]

 

解决方法,在服务器打开注册表,在HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\Providers\OraOLEDB.Oracle下(当然不同版本或命名实例的数据库,这个注册表路径有啥不同,根据实际情况找到OraOLEDB.Oracle),新建DisallowAdHocAccess选项即可解决问题。

 

clipboard[4]

clipboard[5]

 

DisallowAdHocAccess属性设置为 1,SQL Server 不允许特别通过 OPENROWSET 和 OPENDATASOURCE 函数根据指定的 OLE DB 提供程序访问。如果您尝试调用这些函数中的特殊查询,您会收到类似于以下内容的错误消息

 

 

参考资料:

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