Connecting to Oracle from SQL Server

In: SQL Server
Written by: Taylor Gerring

5 Jan 2009

Update: I have posted a revised guide for connecting with SSIS using newer components.

Unfortunately, setting up a linked server to Oracle is not as easy as it should be. In fact, you’ll have to install some software on your server to make things work. I’ve put together a short guide on connecting to Oracle from SQL Server. The process doesn’t take very long but there are several hoops to jump through.

Install Oracle Database 10g Client Release 2
1. Install using the InstantClient option
Install Oracle 10g Release 2 ODAC (64-bit, 32-bit)
1. Select the Oracle Data Access Components option (not .NET!)
Edit TNSNAMES.ora
1. <install directory>Product10.2.0ClientnetworkADMINTNSNAMES.ora (i.e. C:\Oracle\Product10.2.0\Client\network\ADMIN\TNSNAMES.ora)
2. There is a very specific format to the network configuration file. Here is an example to get you started (just copy & paste multiple entries if necessary):
```
DMDEV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = FRIENDLYNAME)
    )
  )
```
  There are a couple of things you need to change:
  - HOST = SERVERNAME. The value SERVERNAME should be changed to reflect the actual address or hostname of the target system.
  - SERVICE_NAME = FRIENDLYNAME. FRIENDLYNAME is the name by which you refer to this actual connection.
REBOOT!
Configure provider in SQL Server
1. Databases→DBName→Server Objects→Linked Servers→Providers→OraOLEDB.Oracle→Properties→Enable “Allow inprocess”
Create a linked server to the Oracle Database
1. General
  1. Linked Server: A name of your choosing which you will use when querying using four-part naming conventions.
  2. Provider: Oracle Provider for OLE DB
  3. Product Name: “Oracle” is fine here
  4. Data Source: This should match the HOST you defined in TNSNAMES.ora
2. Security
  1. Select Be made using this security context and supply the remote login and password

Query the linked server:

SELECT TOP 10 * FROM LINKEDSERVERNAME..SYS.HELP

Tags: Database, HOWTO, Oracle, Software, SQL Server

30 Responses to Connecting to Oracle from SQL Server

HOWTO: Setup SQL Server Linked Server to MySQL | Idea Excursion

February 25th, 2009 at 3:54 pm

[...] Server the ability to connect to a wide range of other systems. I’ve already written about how to connect Oracle and SQL Server. In this post, I’m going to go through the steps necessary to connect SQL Server and MySQL. [...]

Adebimpe

April 15th, 2009 at 9:42 am

this is a really cool guide…it sorted out the problem i had with linked servers, forgot to tick the allow inprocess for the provider i created

Jay

April 29th, 2009 at 4:08 pm

I’ve tried using both the Oracle provider OraOLEDB.Oracle and the SQL Provider MSDAORA.

Unable to get the OraOLEDB.Oracle provider to work at all. The MSDAORA provider return the error message ‘provider ran out of memory’ when trying to do an update to Oracle.

Suggestions?

Taylor Gerring

April 29th, 2009 at 5:20 pm

The first things that come to mind to test are:

1. Do SELECT statements work?
2. How much data is the UPDATE statement modifying?

Jason

April 20th, 2010 at 2:03 am

Hi,

I followed the instructions to the dot but still had the ORA 12154 error coming up. This was after I could successfully TNSping and valid the connection from Oracle Net Manager.

I finally did find a resolution after I put the TNSName in the Data source of the Linked Server instead of the Host name. I’m not sure how come but it worked. The TNSName in the above example would be DMDEV.

zaid

July 8th, 2010 at 7:55 am

I have installed sql server 2008 on a win server 2008 mashine, and I installed the Oracle Client.
my problem is the Provider “OraOLEDB.Oracle” is missing. and I cant make a link with the Oracle server.
can anyone helo thanks

Mike

August 4th, 2010 at 7:03 am

Doh! The inprocess flag was killing me. Thanks.

Dmitri Kuznetsov

October 18th, 2010 at 12:53 pm

Is this procedure for setting up 2008 SQL 64bit and 32 bit Oracle or some other configuration?

thanks…

Fernando Rodriguez

October 18th, 2010 at 4:57 pm

I’ve a windows 2003 server 32 bits with sql server 2005 and OraOLEDB.Oracle installed. I didn’t reboot the machine after install the oracle client.

In the other side a windows 2008 server 64 bits with oracle 11g.

I created a linked server to the Oracle Database and configured it correctly. SELECT statement runs correctly. That’s great!!…

…but when I try to do a BEGIN TRANSACTION and into it a INSERT statement among one table of the oracle server I receive the following error:

7391 The operation could not be performed because OLE DB provider “OraOLEDB.Oracle” for linked server “servername” was unable to begin a distributed transaction.

Any idea?

Thanks

Dmitri

October 19th, 2010 at 10:28 am

Fernando,
can you query data from SQL2008 64bit from Oracle 32 bit?

I have to setup a linked server between 64bit sql08 and 32 bit oracle.
thanks…

October 19th, 2010 at 10:35 am

@Fernando
I double that distributed transactions would be supported over a linked server. Remove the “BEGIN TRAN” and let implicit transactions take over.

@Dmitri
The editions or versions of the servers should have no effect on the linked server.

peje

October 19th, 2010 at 4:37 pm

can i used this method to link sql server -oracle in in two different computer?
eg .. PC a use sql server
PC b use oracle

Matt

November 3rd, 2010 at 5:07 pm

Thanks for this nice article, Taylor.

Do I need to install the Oracle 10g/11g Client or is this only required if I want to run a server myself?

1.5 Gigabytes of software seem a lot just for having an ODAC driver for SQL Management Studio to be able to connect to an Oracle instance on a remote server. The actual driver can’t be more than a few megs?

Is there a more light-way solution? Extract the driver somehow?

kiruthika

December 1st, 2010 at 4:30 am

i have delveloped reports in SQL server 2008 with SQL server as datasource. now i hav the same datasource in oracle aswell .so instead of using my SQL server DB i want to use the Oracle DB as datasource. if am doing so Queries written in SQL is not matching for oracle DB.what can i do for this.
please suggest me

Farooq

December 3rd, 2010 at 12:56 am

hii, is any dll which converts oracle queries to Mssql queries, i found Squirrel.dll but its not working properly

sohail

March 8th, 2011 at 1:54 am

very good article

Jimbo James

April 13th, 2011 at 10:21 am

I didn’t read all the comment, but I simply wanna say that you don’t need to reboot the server. You only have to restant de “SQL Server” service.

May be usefull to know if you have many instance on the same server.

x_dogan

April 28th, 2011 at 3:54 am

This helps me a lot,
Thanks Taylor

Roger

May 13th, 2011 at 10:02 am

Hi, followed this and the ‘inprocess’ flag had been my achilles!!

Wish I’d found your article earlier……

ojdana

May 17th, 2011 at 4:55 am

Thank you very much. The article is very simple and useful.

Working with Oracle from SqlServer 20008 with Services–the Architecture part 1 « Kaidanov Tzvi Gregory Technical Issues & Solutions

June 5th, 2011 at 8:27 am

[...] Add sources of Oracle to SQL SEVRER . [...]

Jason Puchyr

July 26th, 2011 at 1:36 pm

You can also “by pass” the TNSNames.ora file (essentially a TNSless oracle linked server) by putting the TNSNames entry details as the Data Source.

Linked server: friendly name you want to use for the OracleDB
Server type: other
Provider: Oracle Provider for OLE DB
Product Name: whatever you want
Data source: full tns entry – with brackets ie:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=fakeHost.org)(PORT=234))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=oraServiceName)))
Provider string: leave blank

(at least this works for me connecting a SQL Server 2008 R2 to an Oracle 11g database using the 11g client (11.2.0.1.0) 64-bit)

Andrzej

August 25th, 2011 at 6:16 am

Thank you – it’s just what I needed

Rando

September 21st, 2011 at 8:26 am

Great Info — just set up a linked server at work, and it worked wonderfully.

Alecsandra

October 10th, 2011 at 11:36 am

This is great. It helped me a lot in troubleshooting my issue with the SQL server 2008 to Oracle 9i connection. Thanks!

dmcmillan

November 21st, 2011 at 11:46 am

I set up a link from SQL Server 2008R2 to an Oracle 10g database. Using “be made using this security contex” entered a login name and password.
Do you actually connect to the database as that user? Or are you connected as the user you are logged into SSMS as?
Thatnks very much.

November 21st, 2011 at 11:58 am

@dmcmillan

By selecting “Be made using this security context”, you’re requesting that the credentials used are those ones you’re providing in that dialog box.

If you want the connection to be attempted with the same credentials as you’re logging in, select “Be made using the login’s current security context”, though I find this often doesn’t work, especially with Windows Authentication.

November 21st, 2011 at 1:14 pm

Ok, that’s what I thought. Select is working ok, but I’m having trouble getting an update to work. Am getting the following error, but I’m sure the permission is correct for the user I set:

Msg 7357, Level 16, State 2, Line 1
Cannot process the object “update test.employee set email_address = “dmcmillan@mgh.org”
where employee = 888888″. The OLE DB provider “OraOLEDB.Oracle” for linked server “lawtest” indicates that either the object has no columns or the current user does not have permissions on that object.

Thanks very much.

November 21st, 2011 at 1:29 pm

I have figured out that this has to do with using update with openquery, I just need to figure out the right syntax. Thanks very much.

Michael Kaufman

December 7th, 2011 at 3:29 pm

I am connecting a SQL Server to an Oracle 11g sever. When I try to query data from Oracle through the SQL Server I get error messages:

Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider ‘OraOLEDB.Oracle’ supplied invalid metadata for column ‘DTTM_STAMP_SEC’. The data type is not supported.

and

Server: Msg 7318, Level 16, State 1, Line 1
OLE DB provider ‘MSDASQL’ returned an invalid column definition.

Which message I get depends upon whether I am connecting through the Oracle Provider for OLE DB or the Microsoft OLE DB Provider for ODBC.

Does anyone know what I am doing wrong?

KALImaR:Im having the same 11.10 problem with this irritating disaster...Solutions please [...]

Balkan:Hi All, I have similar problem as Dwarakesh, I set the automatic login on, and after that my passwo [...]

Creating an automated database backup by means of SQL Server Agent Job « Placko's SQL KB:[...] Configuring SQL Server Agent Operators by Taylor Gerring [...] [...]

Dwarakesh:yeah even I'm getting the same error on my sony e series laptop!!!! I am currently running ubuntu 1 [...]

Phil:Thanks - this article was a great help. [...]

Connecting to Oracle from SQL Server

30 Responses to Connecting to Oracle from SQL Server

Comment Form

@TaylorGerring

Blogroll

Self