Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















How to setup linked servers for SQL Server and Oracle 64 bit client

MSSQLTips author Tim Ford By:   |   Read Comments (23)   |   Related Tips: 1 | 2 | 3 | More > Linked Servers

Problem
We have two SQL 2005 64-bit clusters in our organization:  a four-node production cluster and a two-node dev/test cluster.  We recently migrated a database from SQL 2000 that relied on an Oracle linked server for some of it's computations.  No big deal, right?  Install the Oracle client software and create the linked server just like in SQL 2000 right?  Be sure to repeat the process on both nodes for failover-sake, right?  Wrong.  In the case of 64-bit SQL instances we have a few extra steps we need to take to get an Oracle linked server configured and working properly.

Solution
64-bit computing is coming of age.  In most cases, our installation and configuration processes do not change from 32-bit to 64-bit: setting up logins, creating SQL Agent jobs; nothing inherently different there.  Inconsistencies do exist however.  Take for example linked servers - more specifically linked servers to Oracle databases.  In 32-bit environments we simply install the Oracle client software, create the linked server, add remote logins and move on to the next project.  In the 64-bit world the process is slightly more difficult - and in the case of one critical step it impacts any existing users on the instance!

Process Creating Oracle Linked Server on a 64-bit SQL Instance:

  • Install Oracle 10g Release 2 64-bit client software (available from Oracle website)
  • Install Oracle 10g Release 2 64-bit ODAC software (available from Oracle website)
  • Restart SQL services
  • Configure OraOLEDB.Oracle provider
  • Create linked server
  • Add remote logins for linked server

The complete process is presented below:


Step One:  Installation of the Oracle 10g Release 2 64-bit client software

I will leave it up to you to secure the software by either download from their site or media available via any licensing agreements you may have with Oracle.  The important steps are outlined below.  You only need the barest install of the client.  Simply go ahead and select the InstantClient option from the opening dialog form and click Next.

The following screens simply require you to click Next when enabled.  These are in place to present the internal checks and summaries of work to be completed once you accept the install parameters.  Proceed through the screens, click Install, and then Finish once the installation is complete.


Step Two:  Installation Oracle 10g Release 2 64-bit ODAC software

Once you have an installed 64-bit Oracle Home you can proceed with installing the ODAC drivers for a 64-bit environment.  The OraOLEDB.Oracle driver installed in this step is what you need to set up your linked server.  You'll find the setup.exe for this installation located in the unzipped Install folder.  I strongly suggest you review your previous installation of the client software via selecting the Installed Products button.  You want to verify that you have a valid 64-bit Oracle Home that you'll be installing against during this process.  We would not be DBAs if we were not overly cautious.  It's quite Darwinian:  the brash and risky don't last long as Database Administrators.

If you accepted the defaults during the client installation, your "Installed Products" Inventory should appear as follows.  If so, then close the dialog and click Next to continue.  If not, then I suggest you revisit your installation of the client software, possibly de-installing and performing all steps presented previously once again.

We will be installing the OraOLEDB.Oracle driver that is included in the Oracle Data Access Components option below.  Select Next to continue.

Using the drop-down box provided, select the existing Oracle Home you just verified was created as a part of your 64-bit Oracle client installation.  The Path will automatically change accordingly.  Continue by clicking Next.

You will be presented with the following sub-components.  I've selected all three (you never know when these items may be useful and they are extremely lightweight) though you only really need to install the first two items.

If you do decide to install the Oracle Services for Microsoft Transaction Server component, you'll be prompted for the port number to use.  The default is 2030. 

Click Next and you'll be presented with the familiar summary screen, listing all components to be installed.  Review, make changes if necessary and then click Install.  Once completed we can move back to familiar territory:  SQL Server Management Studio.


Step Three:  Restart SQL Service

This is the most unfortunate step in the entire process.  This is usually the last thing a DBA wants to do.  It is the option of last resort whenever there is an issue on a SQL Server instance.  However, it is necessary in this case, so be sure to follow your corporate procedures for downtime notifications and process.  That being said, let's move past this horrid step!


Step Four:  Configure OraOLEDB.Oracle provider

I confess.  I was not aware of this process existing until I had to do this the first time.  In Object Explorer, navigate down the tree of the SQL instance we've been working with until you're able to view the list of Providers under Linked Servers.  Select OraOLEDB.Oracle and right-click.

SQL Server Management Studio.

The Provider Options dialog box appears.  Enable the "Allow inprocess" option and close the dialog.  Now ask yourself:  "Self, what did I just do?  Some Internet Guy said click this and it will work."  Let's take a quick look at exactly what enabling this option does for a provider.  By default, OLE DB providers are instantiated outside of the SQL Server process.  While this protects SQL from any OLE DB errors, Microsoft SQL Server requires that the OLE DB provider run in-process for handling BLOB-ish data types (text and images).


Step 5:  Create Linked Server and Add Logins

Finally, something we're all familiar with.  The T-SQL is simple, and identical to the 32-bit platform. 

--Create the Oracle Linked Server: 
EXEC sp_addlinkedserver @server@srvproduct@provider@datasrc

--For example: If I want to create a linked server called LK_TIPS to the ORATIPS 
--database on the SRVORA1 server, listening on the default port of 1521 my query 
--would look like this:
EXEC sp_addlinkedserver 'LK_TIPS''Oracle''OraOLEDB.Oracle''SRVORA1:1521/ORATIPS'

A quick explanation about the @datasrc parameter.  Since our environment is clustered I do not like relying on an Oracle TNSname.ora file for our connection information.  It is far cleaner to specify the data source in terms of the SERVER:PORT/DB_NAME format.

As for creating an associated login the format is:

--Create the Remote Login for the Oracle Linked Server:
EXEC
sp_addlinkedsrvlogin @rmtsrvname, @useself, @rmtuser, @rmtpassword

Briefly, the @useself parameter determines whether the current user in connects to the linked server source under its current context or via impersonation.  By setting the value to FALSE you're stating that you wish to connect to the linked server with a supplied login/password combination that is being identified in the @rmtuser and @rmtpassword parameters respectively.  A value of TRUE would suggest that a login will use it's own (current) credentials to connect to the linked server.

Creating a login for the LK_TIPS linked server we just created with a login of 'sa' and a password set to 'password' would look like this:

EXEC sp_addlinkedsrvlogin 'LK_TIPS', 'FALSE', 'sa', 'password'

Before you get any crazy ideas, I don't suggest using sa/password as a combination for any system!

 

Next Steps

  • Tweak T-SQL code provided to fit your environment 
  • If this is being configured in a cluster, be sure to install the Oracle client and ODAC components identically on all nodes in the cluster.
  • Review related tips on linked servers on MSSQLTips.com.


Last Update: 2/13/2008


About the author
MSSQLTips author Tim Ford
Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Thursday, February 14, 2008 - 6:57:19 PM - dba.kishan Read The Tip

Hi,

Urgent need help from anybody reg.SSIS Packages on 64 bit

We are currently facing the problem with building 64 Bit SSIS pacakages(located on DB Server..which is 2003 Server Edition, 64 Bit) with connecting 32 bit oracle(loaded on the App Server..Windows 2003, 32 bit) in Business Intelligent Development Studio(Integration Services) with Sql Server 2005 64 Bit. We have installed 64 bit Oracle client and network tools and drivers on Database Server(DB Server), but we are still unable to connect from to Oracle from the BIDS for making the 64 bit SSIS packages.

Please give guide me towards the steps for testing SSIS packages which are in 64 bit Using XML files?

how to proceed towards making these packages??

 Thanks

 

 


Thursday, February 14, 2008 - 7:40:15 PM - dbajohnny Read The Tip

Hi ,

I got same problem anybody help

???

Thanks

 


Monday, February 18, 2008 - 6:12:20 PM - timmer26 Read The Tip

A Few questions:

1.  Were you able to complete the steps outlined in this tip successfully?

2. Can you successfully set up and query the linked server from within SSMS, not BIDS?

3. Are you getting a specific error in BIDS?

4. Are you running BIDS from the server you installed the driver on or are you doing your BIDS work from a different workstation/server/laptop?  If so, you'll need to install Oracle on that instance and configure a connection from there during your build before you deploy it.


Friday, March 21, 2008 - 7:11:25 PM - dbajohnny Read The Tip

HI, 

1.  Were you able to complete the steps outlined in this tip successfully?

Yes

2. Can you successfully set up and query the linked server from within SSMS, not BIDS?

Is it compulsory to do set up linked server from SSMS.

Without that linked server can't we connect  using BIDS?

3. Are you getting a specific error in BIDS?

yes, like..

"Test connection failed because of an error in initializing provider.
The 'OraOLEDB.Oracle.1' provider is not registered on the local machine."

I used different providers also getting same error

4. Are you running BIDS from the server you installed the driver on or are you doing your BIDS work from a different workstation/server/laptop?  If so, you'll need to install Oracle on that instance and configure a connection from there during your build before you deploy it.

I am running BIDS from different m/c. Here i installed client s/w of Oracle.

How can i configure the connection? pl can you give steps?

 

Thanks

John

 

 

 


Tuesday, March 25, 2008 - 5:35:59 PM - dbajohnny Read The Tip

HI, 

1.  Were you able to complete the steps outlined in this tip successfully?

Yes

2. Can you successfully set up and query the linked server from within SSMS, not BIDS?

Is it compulsory to do set up linked server from SSMS.

Without that linked server can't we connect  using BIDS?

3. Are you getting a specific error in BIDS?

yes, like..

"Test connection failed because of an error in initializing provider.
The 'OraOLEDB.Oracle.1' provider is not registered on the local machine."

I used different providers also getting same error

4. Are you running BIDS from the server you installed the driver on or are you doing your BIDS work from a different workstation/server/laptop?  If so, you'll need to install Oracle on that instance and configure a connection from there during your build before you deploy it.

I am running BIDS from different m/c. Here i installed client s/w of Oracle.

How can i configure the connection? pl can you give steps?

 

Thanks

John


Wednesday, March 26, 2008 - 7:42:38 AM - timmer26 Read The Tip

John, the linked server is an object on the  SQL server.  It is not workstation-dependent.  Are the data sources in the report using the 64-bit SQL instance that has the linked server configured?

 Also, just curious if you're able to natively connect to the Oracle database from SQLPlus on the m/c in question.


Thursday, July 10, 2008 - 3:20:19 AM - ThinkPad Read The Tip

Environment:
OS: Windows server 2008 64-bit
SQL: SQL Server 2005 64-bit SP2 plus hotfix KB949095-x64 (aka hotfix 3239)
Oracle: Oracle Client 10g (10.2.0.2.21) 32-bit (For server 2008 no 64-bit available yet)
We tried installing the Windows 2003 64-bit version but it won't install.
ODAC: ODAC software (10.2.0.2.21) 32-bit

Problem:

Cannot find provider OraOLEDB.oracle in SQL Server Management Studio/Server
Objects /Linked Servers/Provider

For Windows 2003 the above procedure works fine, but what to do in 2008

Any help would be appreciated


Thursday, July 10, 2008 - 5:00:24 AM - timmer26 Read The Tip

Unfortunately I don't have a Windows or SQL 2008 instance of either 32 or 64 bit to test through this.  Have you checked the Oracle support site?


Thursday, July 10, 2008 - 5:17:41 AM - ThinkPad Read The Tip

Hi Tim thank you for your answer.

Yes I have tried almost anything. It's Server 2008 and SQL 2005


Thursday, July 10, 2008 - 5:30:50 AM - timmer26 Read The Tip

I would guess that the driver is not Windows 2008 compatible.  Did the Oracle download site state is was for Windows 2008 64-bit?


Friday, March 20, 2009 - 11:49:26 AM - gwungfu Read The Tip

It's interesting issue and the fix I have for sql 2005 64 bit environment connecting to Oracle 9i is to install Oracle 11g 32 bit client as well as 64 bit client.

Oracle 32 bit client is used for SSIS connection manager

Oracle 64 bit client is used for Oracle Linked Server.

Claire

 

 


Friday, June 12, 2009 - 3:52:37 AM - mail2bindueldo Read The Tip

Hi Claire,

 

   I am trying to create a linked server from SQL server to Oracle. Could you please tell me the steps?

Its in a different network. But I am able to ping sql server IP from oracle machine.

I gave the host name of the service as IP address of Oracle machine.

When I try to create the Linked server by giving IPaddress/oracledatabasename , it is giving me the error TNS:No listener.

But I made sure that lsnrctrl is on in that machine.

Please help me.

 

thanks 

 


Wednesday, July 15, 2009 - 5:07:55 PM - sonomaRIK Read The Tip

I have a restricted Oracle 10g server.  The OS is limited to just a few users as it is a HRMS system.

I have a need to connect/link an MS-SQL 2005 server to the Oracle instance, but have run into a couple problems from your instructions.

 1. I did NOT install the Oracle on the box to be accessed.  I know the instance name, and the ID/PWords for the OS and the SYSDBA [sysoper too] on that Oracle instance.

2. I have tried to download just the client from Oracle and cannot get any thing you showed in your excellent post:  That is, I can only obtain, it seems the Oracle DATABASE download for 10g and have no options for a client portion only.  Searched quite a bit for the client and not getting there.   I do NOT want to install another Database Oracle or otherwise.  I also, understand I need to load that Client portion on the MS-SQL server that houses MS's instance I want to link to the Oracle instance.

 3.  The DMZ/Restricted Oracle server site, I believe, shouldn't be a problem if I provide the SYS/SYSDBA or equivilent IS/PW to the Oracle site.?

4.  Can you assist me in understanding what I am doing incorrectly.

5.  Lastly, can you help me understand the correct PORT to use, as the only PORT I use is connection to the Enterprise Manager via a Remote Desktop connection.

s/

SonomaRik


Monday, March 22, 2010 - 9:45:45 AM - peterb Read The Tip

What level of encryption is implemented between these servers?. Can I set it to say, 128 , 256 or higher?. We need to be assured of a level of security.


Thursday, July 14, 2011 - 9:02:03 AM - Andreas Mohr Read The Tip

The article is excellent, but I semm to have problems installing the ODACs

We are Running SQL Server 2008 on Windows 2008 R2 Server with 64bit

Oracle Client is already installed:
TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0

A tip for people who cannot find the Oracle Client Software: don´t go to whe Oracle Dowload Pages and look for the software in the list, even worse: the oracle search wont retrieve the downloads either!!! The best bet is to use Google an search for "Oracle 10g Release 2 64-bit client software". Among the search results I find "64-bit Oracle Data Access Components (ODAC) for Windows", but they are only offered as "64-bit Oracle 10g Release 2 ODAC 10.2.0.3 for Windows x64".

My Problem after unpacking the zip and starting setup.exe I will only get error messages.
Java(TM) 2 Standard Platform Edition binary has stopped working.

I am missing a readme with the files I downloaded, but the real pain is that I couldnt downlioad the "ODP.NET documentation" from the Oracle website... wow, and I hear everybody complaining about Microsoft, this is getting a real pain in the behind!

So I made a guess: I started a command prompt, changed directory to the downloaded and unzipped files and ran access_setup.bat
A couple of DLLs were copied and I was asked to restart Jaws. Whatever Jaws is... I did not find anything useful on the first page of the search results, so I rebooted the server.
So now I tried running the ODAC setup using command prompt... was a good idea for the access_setup... only to receive the same error...

Can anyone help?

Is this a compatibility issue between 10.2.0.4.0 client and 10.2.0.3 ODAC? I installed the most current Java jre-6u26-windows-x64...

 

 


Thursday, July 14, 2011 - 12:27:05 PM - Andreas Mohr Read The Tip

I have 10.2.0.4.0 and 10.2.0.3 ODAC installed, everything is fine at the moment. I guess the error occured because ODAC was already installed , but I have no way to verify that.

Linked server is working. Dont forget to set the option for the oracle provider under linked servers as described in the article. Obviously in a misconfigured environment the database times out and you need to reboot the server when accessing the linked server. I was very cautious when creating the linked server this time.

I manually configured the linked server in SQL Server Management Studio. this is my configuration:
Server: the name I entered here matches the name of the tsnames.ora entry, you might be able to enter whatever you pleas, but in case youre experiencing problems give it a try.
Provider: Oracle Provider for OLE DB
Product Name: Oracle
Data Source: I entered the "fully qualified" way in the notation server:port/databasename
Povider String: OraOLEDB.Oracle

I cant tell if using the sp_addlinkedsrvlogin statement is a problem. Under the security tab I entered my Windows User with his domain and mapped the oracle user without selecting "Impersonate" Additionally I marked to sign in any user that is not contained in the list to use the oracle user (those are the radios and the two fields at the bottom of the window in the seurity tab)


Friday, August 19, 2011 - 2:10:35 PM - Cynthia Read The Tip

I found your article precisely because I was asking myself the question "Just because someone on the internet says to click 'allow inprocess' on the OraOLEDB.Oracle provider, what exactly does that do, and why should I do it?"  And you answered my question.  Thanks!


Thursday, March 01, 2012 - 7:47:34 AM - Greg Read The Tip

I found this article whilst trying to research problems with @datsrc=N'server:port/database'. I have an oracle linked server working successfully but it uses plink encryption that runs non the localhost so that the TNS entry is:
TUNNEL = (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=15211))
                (CONNECT_DATA=(SERVICE_NAME=oradb02))
    )

If I use @datsrc=TUNNEL all is fine but I have tried everything without success to by pass TNSNames

e.g @datasrc=N'localhost:15211/oradb02' or N'n.n.n.n:15211/oradb02'

Any ideas?


Monday, June 04, 2012 - 9:13:01 AM - John Read The Tip

Using this article as a starting point, I have developed a slightly different method that is working well in my environment.  

I developed this method because using the article's suggestion for specifying the @datasrc parameter of the linked server definition yielded unreliable results.  On some SQL Servers it worked just fine, on others it did not.  In my environment, the revised step 5 below works reliably on all SQL Servers on which it has been implemented.

Steps 1 through 4 remain the same (note that the Oracle 11 client install is a single step replacing the steps 1 & 2 in the original article.

-------

Step 5a. 

Contact the Oracle DBA and ask for a tnsnames.ora file.  This is where the FQDN for the Oracle server is defined.  Mine looks like this:

 

# Generated client tnsnames.ora from /usr/local/scripts/config/generate_tnsnames.cfg

#

 

[DATABASE_NAME]=

  (DESCRIPTION=

    (ADDRESS=

      (PROTOCOL=TCP)

      (HOST=[ORACLE_SERVER_FQDN])

      (PORT=1521)

    )

    (CONNECT_DATA=

      (SERVER=dedicated)

      (SERVICE_NAME=[DATABASE_NAME])

    )

  )

  

Where [DATABASE_NAME] and [ORACLE_SERVER_FQDN] are values for the specific Oracle instance to which you are connecting.

-------

Step 5b. 

Ask the DBA about any parameters that need to be set in the sqlnet.ora file.  Mine looks like this:

 

# sqlnet.ora Network Configuration File: D:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora

# Generated by Oracle configuration tools.

 

# This file is actually generated by netca. But if customers choose to 

# install "Software Only", this file wont exist and without the native 

# authentication, they will not be able to connect to the database on NT.

 

NAMES.DIRECTORY_PATH=(TNSNAMES)

SQLNET.INBOUND_CONNECT_TIMEOUT=420

SQLNET.ENCRYPTION_CLIENT=required

 

The NAMES.DIRECTORY_PATH parameter is important!

-------

Step 5c. 

Assuming you installed the Oracle client in C:\Oracle, copy tnsnames.ora and sqlnet.ora into C:\Oracle\product\11.2.0\client_1\network\admin\ replacing any existing files (make a backup first).

-------

Step 5d. 

Create the linked server using the following script:

 

EXEC sp_addlinkedserver 

    @server = 'LINKED_SERVER_NAME',

    @srvproduct = 'Oracle',

    @provider = 'OraOLEDB.Oracle',

    @datasrc = 'DATABASE_NAME'

EXEC sp_addlinkedsrvlogin 

    @rmtsrvname = 'LINKED_SERVER_NAME',

    @useself = 'False', 

    @locallogin = NULL,

    @rmtuser = 'ACCOUNT_USERNAME',

    @rmtpassword = 'ACCOUNT_PASSWORD'

    

Where:

LINKED_SERVER_NAME is the name of your linked server object

DATABASE_NAME is the value from the tnsnames.ora file

ACCOUNT_USERNAME and ACCOUNT_PASSWORD are your Oracle server credentials.


Thursday, May 30, 2013 - 6:05:19 AM - Albert Read The Tip

I read somewhere else about changing the values in some of the registry settings, but your article does not mention any of this. What do you think - is it required?

 

The changes were:

 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI

 

OracleOciLib

Original: oci.dll

New: oci.dll

 

OracleSqlLib

Original: SQLLib80.dll

New: orasql11.dll

 

OracleXaLib

Original: xa80.dll

New: oraclient11.dll


Friday, July 12, 2013 - 1:42:07 PM - Lava Read The Tip

Tim -

Great article, thank you so much for the clear steps to follow. It would be great if you please update your article to include Windows vesion you are using for your steps since these steps can be more complex when installing 10g in Windows 2008 R2 server. The other thing would be greeat is to iclude the file links from Oracle site to do the installation. I was not able to install 10g ODAC in Win2008, so I had to use 11g and run it from command line.

Once again, thanks a lot.


Thursday, August 15, 2013 - 8:54:18 AM - Doug Purnell Read The Tip

Great article Tim.  I was able to follow your steps with the 11G R2 64bit client and everything worked.  It's nice to be able to not have to manage the TNS files for linked servers.


Friday, October 25, 2013 - 1:57:34 AM - Irene Read The Tip

Wonderful article. This has fixed my issue after i install and uninstall oracle 32bit and oracle 64bit clients. I am glad that we don't need the 32bit Oracle client after all (as in other blog indicated). I have apply this to SQL2012 x64 with Oracle 11gr2 x64.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.