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

By:   |   Comments (29)   |   Related: 1 | 2 | 3 | > Linked Servers


Problem

We have two SQL 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 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 the other SQL version 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.

installation type

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.

prerequisite checks
summary
end of installation

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.

welcome

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.

inventory

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

oracle universal installer

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.

specify home details

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.

available product components

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. 

microsoft transaction server

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.

summary

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.

oracle

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

provider options

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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, April 8, 2019 - 6:21:49 AM - ankush Back To Top (79497)

can we add Oracle server  using  sp_addlinkedserver in sql server 2012?


Wednesday, December 12, 2018 - 4:50:53 PM - Charles Spence Back To Top (78473)

 Do you have an update for recent SQL Mgmt studio and later versions of Oracle


Wednesday, March 21, 2018 - 6:22:28 AM - [email protected] Back To Top (75489)

Dear Mr. Ford,

we need for project the Oracle 10g 64Bit Odac client components. Do you know or anybody else where we can download this nearly 20 year old pice of software ?

Sincerely

 

mdi

 


Friday, November 10, 2017 - 1:40:09 AM - NUR Back To Top (69503)

Thank You!!!


Tuesday, August 11, 2015 - 12:11:55 AM - RAJESH Back To Top (38417)

Hi Claire,


 i have done linked server like above artical only but here i am geting linked server but  i am not geting data from destination .  whilw opening the table folder i am geting error like  " cannot initalize the data source object from ole db provider "Ora.OLEDB.Oracle" for linked server "SAP"(MICROSOFT SQL SERVEE,Error :7303)"

can you please give soluyion for this asp 


Thanks

RAJESH.


Tuesday, January 27, 2015 - 8:24:04 AM - Sid Back To Top (36068)

Great Article. This help us to resolve Oracle Link server creation issue. By using the above commands we are able to create Oracle Link server with different port.


Friday, October 25, 2013 - 1:57:34 AM - Irene Back To Top (27275)

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.


Thursday, August 15, 2013 - 8:54:18 AM - Doug Purnell Back To Top (26323)

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, July 12, 2013 - 1:42:07 PM - Lava Back To Top (25820)

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, May 30, 2013 - 6:05:19 AM - Albert Back To Top (25195)

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


Monday, June 4, 2012 - 9:13:01 AM - John Back To Top (17793)

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, March 1, 2012 - 7:47:34 AM - Greg Back To Top (16228)

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?


Friday, August 19, 2011 - 2:10:35 PM - Cynthia Back To Top (14450)

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, July 14, 2011 - 12:27:05 PM - Andreas Mohr Back To Top (14171)

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)


Thursday, July 14, 2011 - 9:02:03 AM - Andreas Mohr Back To Top (14170)

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

 

 


Monday, March 22, 2010 - 9:45:45 AM - peterb Back To Top (5093)

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.


Wednesday, July 15, 2009 - 5:07:55 PM - sonomaRIK Back To Top (3740)

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


Friday, June 12, 2009 - 3:52:37 AM - mail2bindueldo Back To Top (3546)

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 

 


Friday, March 20, 2009 - 11:49:26 AM - gwungfu Back To Top (3047)

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

 

 


Thursday, July 10, 2008 - 5:30:50 AM - timmer26 Back To Top (1390)

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


Thursday, July 10, 2008 - 5:17:41 AM - ThinkPad Back To Top (1389)

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:00:24 AM - timmer26 Back To Top (1388)

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 - 3:20:19 AM - ThinkPad Back To Top (1387)

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


Wednesday, March 26, 2008 - 7:42:38 AM - timmer26 Back To Top (784)

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.


Tuesday, March 25, 2008 - 5:35:59 PM - dbajohnny Back To Top (780)

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


Friday, March 21, 2008 - 7:11:25 PM - dbajohnny Back To Top (766)

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

 

 

 


Monday, February 18, 2008 - 6:12:20 PM - timmer26 Back To Top (302)

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.


Thursday, February 14, 2008 - 7:40:15 PM - dbajohnny Back To Top (290)

Hi ,

I got same problem anybody help

???

Thanks

 


Thursday, February 14, 2008 - 6:57:19 PM - dba.kishan Back To Top (289)

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

 

 















get free sql tips
agree to terms