Connecting to Oracle with SQL Server 2005 x64

Posted in: Technical Track

Using OLE DB to get SQL Server to connect to Oracle servers can be done quite easily, but there are a few little tricks you should know to make it go smoothly. Once it’s working it seems to work quite well. I hope this blog post will save you a few headaches.

Recently a client asked me to create a simple SSIS package that would connect to Oracle, pick up some data with queries they provided, import it to SQL Server, and eventually export the data as flat, delimited text files.

With SSIS you can use the OLE DB provider that Oracle provides. If your SQL Server is 32-bit, you can install the 32-bit Oracle client and stop there.

If it’s 64-bit, there are a couple different ways to get the Oracle providers working. The method I’ve had the most success with, is to install both the 32- and 64-bit Oracle clients (in separate directories). I’m not sure why you have to have both clients. The only explanation I’ve seen is that part of SQL Server 64 is still 32-bit. I’m not sure if this is true, but if you look at the shortcuts, SSMS and Visual Studio’s EXEs reside in the 32-bit Program Files folder:

SQL Server Management Studio and DTEXECUI.exe can be found in: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\

Visual Studio: C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\Devenv.exe

If you look at SQL Server and SQL Agent, they both reside in the 64-bit folder: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\

Once both clients were installed, I was able to access the provider in SSIS, and also successfully schedule and run package. The trouble started when the requirements for the project changed, and we decided to use SQL in a stored procedure with a linked server instead of SSIS. I thought this would be very simple to get working, since I already had the providers installed and working in SSIS. I was wrong.

We created the linked server and used OPENROWSET to pass in the queries that the client had written and tested in SQLPlus running against their test system. Now it was time to try out our linked server. I passed it a simple query and instead of seeing some records, got this error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “OraOLEDB.Oracle” for linked server [Oracle Server Name Withheld] reported an error. Access denied.

Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface (“IID_IDBCreateCommand”) from OLE DB provider “OraOLEDB.Oracle” for linked server [Oracle Server Name Witheld]

Naturally, I thought something had changed with our credentials used to access Oracle, so we tested the query and credentials in SQLPlus, which worked. Thinking something had gotten messed up on the SQL Server, I decided to check the providers, and ran the query from SSIS against the provider/Oracle data source. This worked.

We tried creating the linked servers with different settings, and even tried creating them on different servers. Nothing worked.

At this point, I was very frustrated, and thinking about how best to do what we wanted in SSIS. I did some research on the web and eventually stumbled on OPENQUERY – Access denied which suggested the “Allow InProcess” option be enabled on the provider. I enabled this option (instructions below), closed all open query windows, opened a new one and then re-tested my simple/test query using


and everything worked like a charm.

Happy cross-platform querying.

Here are the step by step instructions to enable this:

  1. Expand Linked Servers | Providers.
  2. Right click on the Ora provider and go to Properties.
  3. Find the “Allow InProcess” option and tick it.
  4. Then make a new connection window and run your query (I had to close my open connection windows).

Interested in working with Chris? Schedule a tech call.

About the Author

Chris Presley loves order—making him a premier Microsoft SQL Server expert. Not only has he programmed and administered SQL Server, but he has also shared his expertise and passion with budding DBAs as SQL Server instructor at Conestoga College in Kitchener, Ontario. Drawing on his strong disaster-recovery skills, he monitors production environments to swiftly detect and resolve problems before they arise. A self-described adrenaline junkie, Chris likes tackling the biggest database problems and putting out the toughest fires—and hitting the road on his motorcycle.

10 Comments. Leave new

Hi Chris,

Thank you for your solution. For information, my problem is to connect to Oracle using Visual Studio on SSIS server with Win 2008 x64 bits give ORA-12154. Plan to have a try.

This means that I need to download both :-
1) Oracle Database 11g Release 1 Client (
for Microsoft Windows 2008 Server (32-bit)

2) Oracle Database 11g Release 1 Client (
for Microsoft Windows Server 2008 x64

and install it right ? 32 bit or 64 bit here is both for Oracle and window or..?

Thank you for your help and reply.



Hi Azhary,

Sorry for the very late reply. Yes you will need both clients. Were you able to get this working?



here in my case i m designing the same package
but on my developement server only sql server 2005 64 bit client & integration service is installed ,not database engine(from where i m designing & running SSIS)
my question is how can i enable Allow inprocess option for provider


I had the same issue connecting to Oracle with SQL Server 2008 64-bit from Windows 2008 R2 64-bit.

Changed “Allow InProcess” option and recreated the linked server – and it worked fine even without the 32-bit client installed.

I’m going to install the 32-bit client as well now though to make sure everything is fully accessible in SSIS, etc…




The “Allow in process” option can be found in SSMS by expanding the server | Server Objects | Linked Servers | Providers then right clicking on the ORAOLEDB provider and choosing “Properties”.




Without the other provider, you may find you have a problem with your package when you run it through the SQL Agent as a scheduled job.



My question was,
how to enable allow inprocess for ORAOLEDB provider as i don’t have database engine installed?
i have only client component install for sql server 2005 64 bit


Hi Sanket,

The “allow inprocess” option is set on the SQL Server you will be using. By using the SQL Server Management Studio.

Does that help?



VERY nice article, still valid 2 years later. Thank you!

In my case, I had moved a linked server object to a new database installation. Scripting out the linked server object does NOT (obviously in retrospect) bring over provider specific settings. Nor is there an option to script those items.


Perfect solution. Thanks!


Leave a Reply

Your email address will not be published. Required fields are marked *