Connecting to Oracle with SQL Server 2005 x64

Jul 3, 2009 / By Chris Presley

Tags: ,

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

OPENROWSET

and everything worked like a charm.

Happy cross-platform querying.
Chris.

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

9 Responses to “Connecting to Oracle with SQL Server 2005 x64”

  • Azahary says:

    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 (11.1.0.7.0)
    for Microsoft Windows 2008 Server (32-bit)

    2) Oracle Database 11g Release 1 Client (11.1.0.7.0)
    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.

    Regards
    Azahary

  • Chris Presley says:

    Hi Azhary,

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

    Chris.

  • Sanket says:

    Hi
    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

  • Chris Collins says:

    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…

    Thanks!

  • Chris Presley says:

    Sanket,

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

    Chris.

  • Chris Presley says:

    Chris,

    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.

    Chris.

  • Sanket says:

    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

  • Chris Presley says:

    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?

    Chris.

  • Dave says:

    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.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>