Ask Learn
Preview
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
This post is a walkthrough of creating a Linked Server to Oracle from 64bit SQL Server. There's lots of information on doing this on the web, but much of it is out-of-date.
First, install the correct Oracle drivers. You want the latest version of the Oracle Data Access Components (ODAC), and you want the XCopy deployment. They are available here:
https://www.oracle.com/technetwork/database/windows/downloads/index-090165.html
One you download and unzip this into a folder run the following command from that folder:
C:\Users\dbrowne\Downloads\ODAC121010Xcopy_x64>.\install.bat oledb c:\oracle\odac64 odac64 true
Then you need to add two folders to your system path: c:\oracle\odac64 and c:\oracle\odac64\bin
Then you must reboot for the system path change to be visible by services like SQL Server.
After reboot you're ready to create and test the linked server.
First configure the Oracle OleDB provider to run inside the SQL Server process, and configure it to accept parameters.
exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1
exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1
Then create the linked server definition. Instead of a TNSNames alias, use an EZConnect identifier. Here I'm specifying an IP address and a SID to connecto to an Oracle Express instance running on a VM:
exec sp_addlinkedserver N'MyOracle', 'Oracle', 'ORAOLEDB.Oracle', N'//172.16.8.119/xe', N'FetchSize=2000', ''
exec master.dbo.sp_serveroption @server=N'MyOracle', @optname=N'rpc out', @optvalue=N'true'
exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle', @useself=N'FALSE', @rmtuser=N'system', @rmtpassword='xxxxxx'
Now you're ready to test. We configured the linked server for 'rpc out' so we can send a simple passthrough query to test connectivity:
exec ('select 1 a from dual') at MyOracle
That's it.
Anonymous
October 30, 2013
I checked the registry and none of the files listed in
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCMTxOCI
are in the folder indicated.
Anonymous
December 19, 2013
BEWARE: tested on several Systems, just doesnt work
somethings missing in this HowTo - it cannot create an instance Error 7302
Anonymous
January 08, 2014
Note that the SQL Server instance must be 64bit too. A 32bit SQL Server would need the 32bit OleDB provider.
Anonymous
January 10, 2014
Very Nice Explanation
Thanks
Anonymous
January 30, 2014
The comment has been removed
Anonymous
February 13, 2014
Thanks for the post!
I was using a Windows 7 64 bit system and connecting to an Oracle 11g on CentOS on a VM in the same system. When using this guidelines for 64bit, it did not work.
However, the same steps used with 32 bit ODAC and Oraclie Client 11g worked like a charm!
Thanks a lot!
Regards
Dev
Anonymous
March 10, 2014
The comment has been removed
Anonymous
April 13, 2014
you must copy your tnsnames.ora file to c:oracleodac64network
Anonymous
April 27, 2014
After two days of struggle with creating linked server on Windows Server 2012 R2 and Sql server 2012 - found solution from Troy 7's comment (Thanks Troy)
The Data Source I was providing was the SID as defined in the tnsnames - which worked fine on Windows Server 2008/Sql Server 2012 linked server
In Windows server 2012 - it's expecting
Data Source: //IPAddressOrHostnameOfOracleServer:PORT/SERVICE_NAME
In both cases I was creating the linked server to the same Oracle db on the same server. No idea why the detailed definition was needed ...
Anonymous
April 27, 2014
Thanks to Okan Tekeli too... I had to copy the tnsnams.ora into the c:oracleodac as well. I had it in the Oracle client networkadmin. This was fine for Windows Server 2008
Windows server 2012 R2 expects a copy of the tnsnames.ora in c:oracleodac
Anonymous
June 09, 2014
Thanks Dave, this worked great!
Anonymous
July 28, 2014
Thanks for a great writeup!
For those who have not been able to make this work, you can try to doublecheck if you have launched CMD as admin before running the install.bat command. Without this you might still get entries in the registry and files in the right places (if you accept regedit prompts during install) - but the provider will not be registrered with SQL
Anonymous
August 06, 2014
Can someone please explain me the second parameter "Oracle" in the "sp_addlinkedserver " query? I am little bit confuse over it.
Anonymous
August 19, 2014
Great topic. Straightforward. To the pont. You don't have to install Oracle Client. Only Oracle Driver will do. Everything works for first time. I wish only such instruction to be on internet.
Thank You very much.
Anonymous
September 23, 2014
SQL Server 2012 R2 on Windows Server 2012 R2 Datacenter
Followed your instructions and installed from ODAC121012Xcopy_x64.zip. When expanding all the way down to Tables in newly created linked server, error 'The OLE DB provider "ORAOLEDB.Oracle" has not been registered.(Microsoft SQL Server, Error:7403)' occurred.
On examination,the new folders and installed files properly show up in Windows Explorer, but data provider ORAOLEDB.Oracle is not in the Provider folder under Linked Servers in SSMS.
Checked and rebooted again and again, but still the same. What could be wrong? Your help would be greatly appreciated - Like you said, there were lots of postings on this, but most are out-of-date.
Thanks in advance!
David
Anonymous
October 27, 2014
Best set of instructions for this.
thanks.
Anonymous
November 09, 2014
Thanks for the help, it works.
Anonymous
November 17, 2014
the Error with 7403 can solved. You must aktivate the checkbox for "in Process allowed". in the linked server can you config the driver. there you must open OraOleDB.Oracle.
Anonymous
November 19, 2014
OLE DB provider "ORAOLEDB.Oracle" for linked server "MyOracle" returned message "ORA-03134: Connections to this server version are no longer supported.".
Trying to set up a linked server from 2014 sql express to a server running oracle 9.2.0.8.0
Anonymous
December 15, 2014
i doing follow instruction and complete all but when i tried to test connection my sql server auto stop service. Could you please help me to solve this problem?
Anonymous
January 10, 2015
The comment has been removed
Anonymous
January 10, 2015
@daniesh. Check the NTFS permissions of the SQL Server service account on the Oracle client folders.
Anonymous
February 02, 2015
thanks! been trying to get this working all day. your post did the trick.
Anonymous
March 20, 2015
Anonymous
May 15, 2015
Finally a simple guide, all steps worked fine.
I got ORA-12514 becasue I used a wrong oracle service name.
these commands (from oracle server) could be useful to get info:
lsnrctl status
lsnrctl service
ciao
Anonymous
May 29, 2015
The comment has been removed
Anonymous
June 15, 2015
Very detail to the point. First class
Anonymous
June 20, 2015
Thanks
exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1
exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1
exec sp_addlinkedserver N'OrawsvtestLinkedSVR', 'Oracle', 'ORAOLEDB.Oracle', N'OraTnsEntryName', N'FetchSize=8000', ''
exec master.dbo.sp_serveroption @server=N' OrawsvtestLinkedSVR ', @optname=N'rpc out', @optvalue=N'true'
exec sp_addlinkedsrvlogin @rmtsrvname=' OrawsvtestLinkedSVR', @useself=N'FALSE', @rmtuser=N'Oraid', @rmtpassword='Pwd'
Anonymous
June 30, 2015
Windows Server 2012 x64, SQL Server Ent x64. Completely removed any sign of oracle from the server, then downloaded the ODAC112040Xcopy_64bit.zip (as the remote oracle DB is v11.2.0.4.0) created a linked server with your instructions (but I used TNS instead of ezconnect) and everything works like a charm.
Thanks a lot
Anonymous
June 30, 2015
Sorry my SQL version is 2012 as well (11.0 SP1)
Anonymous
July 14, 2015
The comment has been removed
Anonymous
August 17, 2015
when i want to linked server test connection, I receive below error:
32-bit OLE DB provider "ORAOLEDB.Oracle" cannot be loaded in-process on 64-bit SQL Server (7438)
Anonymous
August 30, 2015
It worked only after put a path(variables) with
TNS_ADMIN = path of where tnsnames are
Anonymous
September 04, 2015
Great great topic. Things are so easy, if you know how...
It works fine for me on MSSQL Server 2014 SP1.
Anonymous
September 30, 2015
I agree with Radek together with www.orafaq.com/.../EZCONNECT I was able to solve my problem
Anonymous
October 12, 2015
Hello, This worked for me on MSSQL 2014, and I've tried before on 2008, so the problem I was getting was the Oracle driver is missing, but in 2014 it's the driver is available.
Botan from Somalia
Anonymous
October 12, 2015
The comment has been removed
Anonymous
October 14, 2015
Installing SqlSERVER 2008 R2 on Windows Server 2012. The ORAoleDB.oracle providers would not show up under providers in SSMS. This was solved by registering the oraoledb12.dll by running the cmd.exe as an admin(otherwise error 0080070005). Command: regsvr32.exe c:oracleodac64binOraOLEdb12.dll.
Anonymous
November 24, 2015
Thank you so much, it's work for me on sql server 2014 64bit
Anonymous
March 04, 2016
Works great, thanks.
Anonymous
March 09, 2016
The comment has been removed
Anonymous
March 09, 2016
This also worked for me. Thank you.One thing to note, the EZCONNECT string seems to only work with the Service Name of the database, not the SID.E.g. //server-name.domain:1521/service_name
Anonymous
May 05, 2016
If you get the following error because your are running a distributed transaction:ole db provider for linked server was unable to begin a distributed transactionrun the following command to install the OracleMTS.\install.bat oramts c:\oracle\odac64 odac64 falserun this after the other install command.
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign in