Connecting to an Oracle Database from SQL Server and Microsoft BI Tools

When it comes to relational databases, most organizations run heterogeneous environments. SQL Server DBAs, IT professionals, and business intelligence (BI) practitioners will likely need to connect to an Oracle database at some point in their career. In this article, I'll show you how to establish Oracle connectivity from SQL Server and Microsoft BI tools, as well as how to avoid common pitfalls along the way. (A special thanks to my colleague, David Browne, for helping me with this article.)

Getting Started: Identify Oracle Database and Download Oracle Data Access Components

Before getting starting, determine the server name (or IP address), port number, service name, and version number of the Oracle database you want to connect to. If you don't have an Oracle database to use, see "Installing an Oracle Database for Testing Purposes." Next, we'll download the Oracle database drivers.

Oracle relational database drivers are available through several free downloads on the Oracle Technology Network website. (Note that you must register with Oracle before you can download any software.) In this article, we'll use the Oracle Data Access Components (ODAC) download. ODAC is available without any additional client tools, making it a relatively small and suitable deployment option for both development and production servers. ODAC also has an Xcopy installation option.

Related: Connecting SQL Server and Oracle Using Linked Servers

ODAC is available in both 64-bit and 32-bit flavors. Today, most organizations use 64-bit operating systems and 64-bit versions of SQL Server. However, if you need to connect to Oracle from Visual Studio (which includes SQL Server Developer Tools—SSDT), you'll need to install the 32-bit package because Visual Studio is a 32-bit application. Likewise, if you need to connect to Oracle from a 32-bit version of Microsoft Excel, you'll need 32-bit ODAC. If your machine has both Visual Studio (and/or Excel 32-bit) and a 64-bit instance of SQL Server, you'll need to install both packages.

As of April 2014, the latest ODAC release is 12c (corresponding to the latest Oracle database release). Similar to SQL Server client drivers, the ODAC 12c drivers are backward-compatible for two releases. In other words, ODAC 12c can connect to Oracle 10g Release 2 or later. If you need to connect to an older release of Oracle, you can download an older ODAC package.

Assuming you need both the 64-bit ODAC package and the 32-bit ODAC package, download both Xcopy versions and then unzip each file. The location where you unzip the files shouldn't be the final location; we'll run an install script to copy the files to a target folder. On my machine, I unzipped the packages to a folder named C:odac_installfiles (see Figure 1), which will be deleted after the installation is finished.

Figure 1: Downloading the ODAC Packages

Installing Oracle Data Access Components

As you can see in Figure 1, I created a target folder named C:odac with the subfolders odac32 and odac64. Now we can begin the installation; we'll start with the 64-bit package. Open a command prompt and navigate to the unzipped files (e.g., C:odac_installfilesodac64). I recommend being logged on as an administrator; otherwise, you'll be prompted several times when registry settings are applied. Enter the following command to run the ODAC installation batch script:

install.bat oledb c:odacodac64 odac

The first parameter, oledb, installs the Oracle Provider for OLEDB provider, along with its dependency files, to the C:odacodac64 folder. The third parameter, odac, is the Oracle home name that's used for the registry keys. If you want to learn more about the various installation options (and other providers you can install), refer to the readme.htm file located with the unzipped files.

To install the 32-bit package, navigate to the 32-bit location (e.g., C:odac_installfilesodac32) and enter the following command:

install.bat oledb c:odacodac32 odac

Per the readme.htm file, we now need to add the installation directory (e.g., C:odacodac64) and install the directory's bin subdirectory (e.g., C:odacodac64bin) to the system path environment variable. You can view or edit the path variable from the operating system System Properties dialog box, as Figure 2 shows. After you change the path variable, reboot your machine; certain services, such as SQL Server, won't see environment variable changes until after the machine restarts.

Figure 2: Viewing the ODAC Path

Oracle Connection Strings: TNSNames.ora and EZConnect

You have two main options for building a connection string when connecting to Oracle. The first approach involves the use of a file that resides on the client machine (i.e., the machine where the connection is being established). Specifically, the Oracle provider extracts connection details from a file named tnsnames.ora (by default, this file is located in the %ORACLE_HOME%etworkadmin directory; e.g., C:odacodac64etworkadmin). You can find an example tnsnames.ora file after you install ODAC in the %ORACLE_HOME%etworkadminsample directory.

In theory, I like the idea of using tnsnames.ora. It lets you consolidate multiple connections to a single file, which also means you can make changes in a single place. In reality, though, I find tnsnames.ora to be a bit tricky to use and difficult to troubleshoot. With that in mind, I recommend that you use the second approach: EZConnect. EZConnect, which is available with ODAC 10g and later, includes all connection information (e.g., server name, port number, database name) in the connection string itself. Note that all of my examples use this approach.

Establishing Connectivity to Oracle from a SQL Server Linked Server

Let's start by creating a linked server definition to Oracle from an instance of SQL Server. A linked server can be created in SQL Server Management Studio (SSMS) by right-clicking Server Objects, Linked Servers, then selecting the New Linked Server menu item. You need to specify the following settings, which you can see in Figure 3:

Figure 3: Creating a Linked Server Definition to Oracle from a SQL Server Instance

You also need to specify a login on the Security tab before saving the Linked Server definition. Unless the Oracle server is configured to use Windows Authentication, you'll likely enter a remote (i.e., Oracle-specific) login and password. Note that you have the option of using a single login/password for all connections, or you can map local (i.e., SQL Server) logins to specific remote login/passwords.

To leverage a linked server from a T-SQL query, use the syntax [Linked_Server_Name]..[Schema_Name].[Object_Name]. Figure 4 shows an example of using a linked server definition from a T-SQL query in SSMS.

Figure 4: Using a Linked Server Definition from a T-SQL Query in SSMS

Configuring SQL Server Data Tools

SSDT provides Visual Studio project templates for SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS). (Prior to SQL Server 2012, SSDT was referred to as Business Intelligence Development Studio, or BIDS.) As I mentioned previously, Visual Studio (even the most recent Visual Studio 2013) is a 32-bit application. This is somewhat confusing because Visual Studio can be used to design and debug 64-bit applications. But in the context of establishing Oracle connectivity, Visual Studio needs to be treated as a 32-bit app.

With this in mind, if you install both the 64-bit and 32-bit versions of ODAC, you need to start Visual Studio from a command prompt for everything to work correctly. Assuming your environment path variable references the 64-bit ODAC package, instantiate Visual Studio using the following two-line .bat file:

set path=c:odacodac32;c:odacodac32bin;%path%"C:Program Files (x86)Microsoft Visual Studio 11.0Common7IDEdevenv.exe"

With this approach, establishing Oracle connectivity from within SSDT is fairly straightforward. Let's look at a few specific examples.

Oracle and Server Analysis Services

Figure 5 shows the Advanced Properties dialog box for a connection to Oracle within an SSAS Tabular project. Notice there are several Oracle providers to choose from in the Providers drop-down list. My recommendation is to stick with the Oracle Provider for OLE DB provider; this recommendation goes for all BI project types. Although you can use other providers, I've typically seen better reliability and performance when using the Oracle Provider for OLE DB provider. For pure .NET application development, there's a new provider from Oracle that you might want to consider; see "Oracle Data Provider for .NET (ODP.NET)."

Figure 5: Connecting to Oracle within an SSAS Tabular Project

Both the 32-bit and 64-bit provider can be invoked as you're designing a cube. For example, after establishing a connection in an SSAS Tabular project, you use the Table Import wizard to import tables/views. As you walk through the wizard, the 32-bit provider is used. Once the wizard completes, SSAS (not Visual Studio) does the actual data import. Assuming you're using 64-bit SSAS, the 64-bit driver is now invoked (the same behavior applies to processing cubes in both tabular and multidimensional projects). Without knowing about this behavior, it can be very frustrating trying to understand what's going on when a wizard fails (or when the data import fails).

Oracle and SQL Server Reporting Services

The default Oracle connection in SSRS uses something called the .NET Framework Data Provider for Oracle, which is a deprecated library within the .NET Framework. I suggest changing the connection type to OLEDB and then selecting the Oracle Provider for OLE DB provider.

Connecting to Oracle from Excel and Power Pivot for SharePoint

After the Oracle Provider for OLE DB provider is installed, connecting to Oracle from an Excel workbook connection, Power Pivot data model, or the new Power Query add-in is uneventful. You just need to determine whether Excel is running in 32-bit or 64-bit mode. In Excel 2013, select Account, About Excel from the File Ribbon. In Excel 2010, simply select File, Help.

For Power Pivot–enabled workbooks that are deployed to SharePoint, you need to perform some steps if you want to enable server-side data refresh. You need to deploy the ODAC 64-bit package to the application server(s) where Power Pivot for SharePoint is installed, and you must add the Oracle Provider for OLE DB provider as a trusted data provider in Excel Services.