OPENROWSET – Query Oracle from SQL Server using a linked server

Posted by

Query Oracle from SQL Server using a linked server

This post describes the basics of querying an Oracle database from Microsoft SQL Server.

Oracle data can be queried from SQL Server via a linked server defined in Microsoft SQL Server

Install oracle client on the SQL Server Database Machine

The Oracle client is required to expose the OraOLEDB Oracle provider and for subsequent connections to Oracle databases via OracleNet – Oracle transparent network substrate (tnsnames etc.)</p

Create an entry for the Oracle database in the tnsnames.ora Oracle Net File

ORCL=(DESCRIPTION =  (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)   (SERVICE_NAME = ORCL)))

Create a linked server in SQL Server

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ORCL', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'ORCL'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORACLE_ORCL',@useself=N'False',@locallogin=NULL,@rmtuser=N'orcl_user',@rmtpassword='orcl_pwd'
GO

Query the Oracle data

Grant the Oracle user “select any dictionary” privileges for test purposes. oracle data can be queried using “openrowset” or directly in a SQL statement using the linked server

-- using openrowset
select * from openrowset('OraOLEDB.Oracle','ORCL';'orcl_user';'orcl_pwd','select * from dual')

-- direct sql using [&lt;LINKED_SERVER&gt;]..[OWNER].[OBJECT_NAME]
select * from [ORCL]..[SYS].[DUAL]

Provider Properties

SQL queries can be executed “inprocess” or not “inprocess”.

“inprocess” may cause sql server crash if there are issues with the provider, so it is recommended to execute “out of process”. To run “out of process”, permissons are required on the MSDAINITIALIZE DCOM component, to initialize the provider.

Run dcomcnfg from cmd

Navigate to MSDAINITIALIZE component

 

Edit the permissions to allow groups or specific users to use the provider according to your requirements.

Ad-Hoc Distributed Queries

If there is an error reported when the “openrowset” sql statement is executed, as follows:

Msg 15281, Level 16, State 1, Line 3
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.

The use of ‘Ad Hoc Distributed Queries’ can be enabled by an administrator as decribed im the error message.

sp_configure 'Ad Hoc Distributed Queries',1  
reconfigure

Or use SSMS GUI, right-click on instance, select Facets, navigate to the “Surface Area Configuraion in the Facet drop-down list, and set Facet property “AdHocRemoteQueriesEnabled” to true”

Add User to DCOM Users Group