How to get up and running with Oracle and Linked Servers

  • Article

I have had more Linked Server cases that are setup for an Oracle database than any other non-SQL Server database in SQL. Being in Business Intelligence Support we deal with plenty of connectivity issues and this is one topic of connectivity that does not get touched on a lot.

In less than a month I got 4 Oracle Linked Server cases that all had different issues. The one thing that really got me was I did not really understand how the Oracle side of things worked for me to better troubleshoot the issue. For example, in one case I did not really have a good understanding of the ODAC Providers (Oracle’s Providers for connecting to different tools and applications) and the tnsnames.ora file and how they related to the whole setup. By having the whole picture I feel we can really help understand Oracle Linked Server setups better.

Walkthrough:

So for me to understand how the Oracle side worked I needed to get an Oracle server up and running.

As such, I decided to create an Oracle 11G server. You can download the bits using the following link.

Oracle Database Software
https://www.oracle.com/technetwork/database/enterprise-edition/downloads/index-092322.html

After all that, I created a table in the system (default) schema.

Then I needed to create a listener which I learned is very important from an Oracle’s standpoint to make the database run properly.

What’s a table without any data? I added some test data so I can compare the results between the Oracle database and the Linked Server results. Then after creating the table I added data to it so I can compare the results between the Oracle database and the Linked Server results.

image

Once I had the Oracle side all up and ready I started to create my Linked Server in SSMS.

Now after I got my Oracle server up and operational I needed to find a very distinct file as this is the file that deals with the connectivity between Oracle and SQL. This file is called the tnsnames.ora file. I need to make sure I can locate it on the Oracle database server itself. More normal default Location is

C:\<database folder>\product\11.2.0\dbhome_1\ NETWORK\ADMIN\tnsnames.ora

ex: C:\OracleDatabase\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

The Service ID you have setup will be the connection information you will need when creating the Linked Server in SSMS. In this case I am going to use SPORTS.

image

Now that we know the Oracle server is setup and we have our tnsnames.ora information ready, we need to start setting up the SQL Server to have the ability to create a Linked Server that connects to an Oracle database.

So at this point we would need to download and install the proper ODAC provider from ORACLE to get that process started. REMEMBER – BITNESS MATTERS!

Listed below are the sites on where to download the proper provider needed:

For 64-bit providers
https://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

For 32-bit providers
https://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

For this example we are using 64 bit Oracle version 11g

image

For a quick test to verify you have it downloaded and installed properly you can do a quick UDL test. On the desktop create a new text file (make sure to show extensions so you can see the .txt part of the name). Then rename the entire file including the extension to Test.udl and press OK. Once you go to the Provider tab at the top left you should see something like “Oracle Provider for OLE DB” listed.

image

Now once you have confirmed you have installed the provider, search for the tnsnames.ora file on the SQL Server. Normally the default location is – C:\<folder chosen to save it in>\app\oracle\product\11.2.0\client\network\ADMIN.

Example location we are going to use will be: D:\app\sql2012\product\11.2.0\client_1\network\ADMIN.

image

What we would add to the SQL Server TNSNames file:

SPORTS =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE11GMG.markg.local)(PORT = 4977))

(ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE11GMG.markg.local)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = Sports)

)

)

Once you have the tnsnames.ora file correctly filled in on the SQL Server machine, you can now setup the Linked Server in SQL Server Management Studio.

Using SQL Server Management Studio to create a linked server to another instance of SQL Server Using SQL Server Management Studio
https://technet.microsoft.com/en-us/library/ff772782(v=sql.110).aspx#SSMSProcedure

Before we start going through the actual steps you need to make sure the “OraOLEDB.Oracle” Provider is listed under Linked Server > Providers.

image

Also make sure that under properties for the provider you select Allow inprocess.

image

When you use the “Allow in-process” option for Linked Server providers, SQL loads the COM DLL in its own memory process. We do not normally recommend it because it can lead to stability issues in SQL Server, but some providers require it such as the Oracle one. If it crashes, it will also crash SQL Server.

When running “Out of Process”, SQL launches the MSDAINITIALIZE process and that process loads the COM server (in this case, OLE DB Provider). If it is idle for x minutes or if the driver crashes the process, it unloads and the next linked server request loads in a new MSDAINITIALIZE process. You can see MSDAINITIALIZE by running dcomcnfg and working your way down Component Services.

Generally only Administrators or the local system account can launch this, so if SQL is running under a domain account, you should add it to the local Administrators group or have it run as Local System.

Now we can start creating the Oracle Linked Server in Management Studio.

Go to Server Objects > Linked Servers > right click and select New Linked Server…

image

Then start filling in the necessary information to continue to create an Oracle Linked server

General Tab:

Linked server - Name of your Linked Server

Server Type - Choose “Other data source” when using Oracle or any other Non-SQL Server database

Provider – Oracle Provider for OLE DB (downloaded from the Oracle site)

Product name – Oracle

Data source – MSORATEST (this comes from the information in the TNSNames.ora file you added onto the SQL machine)

Provider String – “leave blank”

Sample image of what it would look like once completed.

image

Then you will need to go to the Security Tab.

Select the option – Be made using this security context. The credentials you need to add are the ones that get you logged into your Oracle database.

Do Note: this is probably not the safest option. Mapping logins would be more secure. By doing this, it means that every user hitting this linked server will connect to Oracle using that context. I did it this way because it was easier for me and I am my own admin.

image

Then open up the Linked Server in Management Studio and search for the system tables:

image

Test that it works by running a 4 part query.

<Linked server name> <Database name> (if no specific database name then just use “..”) <Schema> <Table Name>

Ex: [SPORTS]..[MARK].[SPORTSDALLAS]

image

image

If you get this error when trying to create a Linked Server - “Cannot create an instance of OLE DB provider” - after filling in all the information follow this BLOG.

Troubleshooting “Cannot create an instance of OLE DB provider”
https://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/09/28/troubleshooting-cannot-create-an-instance-of-ole-db-provider.aspx

Mark Ghanayem
Microsoft Business Intelligence Support

Comments

  • Anonymous
    December 11, 2015
    The Allow In-process detail really helped me out thanks!
  • Anonymous
    June 06, 2016
    My understanding is that using Oracle Provider for OLE DB (x64) on a Linked Server is not supported, but now is it supported?OLE DB Providers Tested with SQL Serverhttps://technet.microsoft.com/en-us/library/ms187072(v=sql.105).aspx** Support for the Oracle Provider for OLE DB is as follows:Oracle 32-bit : Out-of-proc onlyOracle 64-bit : None
  • Anonymous
    June 29, 2016
    Gracias, por su ayuda, excelente Blog.
  • Anonymous
    October 19, 2016
    I was able to create a linked server to Oracle while working at another company. At my current assignment, I am unable to create the link. Is there a property that has to be enabled on the Oracle server that allows this connection?Thanks.
  • Anonymous
    November 29, 2016
    Thank you Mark for the step-by-step guidance. Very helpful.
  • Anonymous
    April 22, 2017
    I disagree. Look at that http://www.google.com/search?q=Get+Up+And+Running
  • Anonymous
    May 16, 2017
    TITLE: Microsoft SQL Server Management Studio------------------------------The linked server has been created but failed a connection test. Do you want to keep the linked server?------------------------------ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "GDS-IPD".OLE DB provider "OraOLEDB.Oracle" for linked server "GDS-IPD" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.5000&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476------------------------------BUTTONS:&Yes&No------------------------------
    • Anonymous
      June 14, 2017
      So I have my linked server working and the query returns the expected results. The problem I have is getting those results into a table I have created on the SQL server (2016).Is there a trick to the insert .... from OPENQUERY (ORCL, 'select...')?Is this even possible?
  • Anonymous
    June 14, 2017
    So I have my linked server working and the query returns the expected results. The problem I have is getting those results into a table I have created on the SQL server (2016).Is there a trick to the insert …. from OPENQUERY (ORCL, ‘select…’)?Is this even possible?
  • Anonymous
    June 29, 2017
    Awesome. Was looking for something like this. Worked in the first instance
  • Anonymous
    October 14, 2017
    This assumes that somehow we magically have a tnsnames.ora file. From Oracle Database 12c Release 1 (12.1.0.2.0)http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html Oracle Database Client (12.1.0.2.0) for Microsoft Windows (x64) Download winx64_12102_client.zip (64-bit) (925,039,944 bytes)Select Installation Type (•) Administrator ("Select this option to connect to an Oracle database ...")After installation, I run sqldeveloper.exe, put in Basic connection parameters (the ones our supplier provided). The connections work and sqldeveloper remembers them (somehow), but it does not store it in any tnsnames.ora file I can find. The only tnsnames.ora I find is the sample one app\client\product\12.1.0\client_1\network\admin\sample\tnsnames.oRASo some steps missing ...?
    • Anonymous
      October 18, 2017
      Resolved using 'netca.bat' (Windows) to configure tnsnames.oradocs.oracle.com/cd/E11882_01/server.112/e10897/network.htm#ADMQS004Thank you for this useful article.
  • Anonymous
    April 13, 2018
    I kept running into 12504 errors trying to use tnsnames.ora.The simple trick is to just provide the Oracle server name, port and TSNname in the 'Data source' field in the following format;//servername:port/TSNnameexample in your case://ORACLE11GMG.markg.local:1521/Sports