Configuring Connection from SQL Server to Oracle using Linked Server

SQL Server Linked Servers technology allows you to access non-SQL Server databases from a SQL Server database using OLE DB providers.

You can connect to SQL Server and execute commands against OLE DB data sources on remote servers.

OLE DB Provider for Oracle

To create a linked server, you need an OLE DB provider and OLE DB data source.

  • Microsoft OLE DB Provider for Oracle (MSDAORA)

Microsoft provides OLE DB Provider for Oracle with SQL Server installation but you also need to install Oracle Client software and configure connection to Oracle. The name of this provider is MSDAORA.

  • Oracle OLE DB Provider (OraOLEDB.Oracle)

OLE DB provider provided by Oracle that also requires Oracle Client software. The name of this provider is OraOLEDB.Oracle.

Creating Linked Server

Use sp_addlinkedserver procedure to create the linked server, specifying MSDAORA as provider_name, and the SQL*Net alias name for the Oracle database instance as data_ source.

   sp_addlinkedserver @server = 'ORASRV', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'ORCL'

@server specifies the server name that will be used in the SQL statements.

@datasrc is an alias from tnsnames.ora file (%ORACLE_NOME%\network\admin):

    ORCL =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = dbsrv)(PORT = 1521))
       )
       (CONNECT_DATA =
         (SERVICE_NAME = orcl.domain.com) ) )

Then use sp_addlinkedsrvlogin to create a login to Oracle:

   sp_addlinkedsrvlogin @rmtsrvname = 'ORASRV', @useself = 'False', @locallogin = NULL, 
       @rmtuser = 'SCOTT', @rmtpassword ='TIGER'

After you have created the linked server and login, you can query an Oracle table from SQL Server:

   SELECT * FROM ORASRV..SCOTT.DEPT

Note. If the table and column names were created in Oracle without quoted identifiers, use them in uppercase. Otherwise you will receive “The table either does not exist or the current user does not have permissions on that table” error.

Creating Linked Server using OraOLEDB.Oracle Provider

You can also create a linked server using Oracle OLE DB provider.

Use sp_addlinkedserver procedure and specify OraOLEDB.Oracle as provider_name, and the SQL*Net alias name for the Oracle database instance as data_ source.

   sp_addlinkedserver @server = 'ORASRV2', @srvproduct = 'Oracle', @provider = 'OraOLEDB.Oracle', 
       @datasrc = 'ORCL'

@server specifies the server name that will be used in the SQL statements. @datasrc is an alias from tnsnames.ora file (%ORACLE_NOME%\network\admin).

Then use sp_addlinkedsrvlogin to create a login to Oracle:

   sp_addlinkedsrvlogin @rmtsrvname = 'ORASRV2', @useself = 'False', @locallogin = NULL, 
       @rmtuser = 'SCOTT', @rmtpassword ='TIGER'

Note that before you can use Oracle OLE DB provider, you have to set Allow inprocess option (SSMS→Instance→Server Objects→Linked Server→Providers→OraOLEDB.Oracle) and restart the SQL Server instance.

Resources

Author

Dmitry Tolpeko, dmtolpeko@sqlines.com - July 2012 (Updated)

Please contact me if you are interested in Oracle to SQL Server migration services, consulting, tools or training.

You could leave a comment if you were logged in.