How to get up and running with Oracle and Linked Servers

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