Connecting SQL Server and Oracle Using Linked Servers

Running multiple database platforms is a way of life for many businesses, and SQL Server and Oracle are without a doubt the two most common enterprise database platforms. For most organizations, each database platform supports its own applications. However, you sometimes need to integrate the two databases and perform lookups, queries, and other operations on one database that incorporates data from the other database.

Related: Troubleshooting Oracle Client Connections

If your SQL Server database needs ad-hoc database access to Oracle, the answer is linked servers. SQL Server’s linked servers functionality lets you seamlessly integrate your SQL Server applications with tables, indexes, and views stored in Oracle. In this article, I’ll show you how to configure and use a SQL Server 2008 linked server connection to an Oracle 11g database.

Installing the Oracle Client Software

Before you can create a SQL Server 2008 linked server to Oracle, you need to install the Oracle client software on the SQL Server 2008 system. The Oracle client software provides the network libraries that are required to establish connectivity to the Oracle database. In this case, the Oracle 11g database server is already installed and functional. Installing the Oracle client software on the SQL Server system enables it to connect to the existing Oracle server.

You can download the Oracle client software from technet.oracle.com. When selecting the Oracle client software you need to be sure to get the appropriate software for your SQL Server system’s processor architecture. If your SQL Server system is 64-bit, you need to download the Oracle x64 client. If your SQL Server system is 32-bit, you need to download the Oracle x86 client. The 64-bit Oracle client software is in the 11107_w2k8_x64_production_client.zip file. The 32-bit Oracle client software is in the win321_11gR1_client.zip file. After selecting the correct client software, install the Oracle client by running the setup.exe program on your SQL Server system. The first Oracle client setup screen is the Oracle Universal Installer Welcome dialog box, which lets you install and uninstall Oracle products. Then click Next to display the Select Installation Type dialog box, which you can use to determine what type of installation you’ll be performing, as shown in Figure 1.

You can choose to install the InstantClient, the Administrator, or the Runtime components. To create an Oracle linked server, select the Runtime option that’s shown in Figure 1. This option installs Oracle’s OLE DB Provider and ODBC driver, as well as SQL Plus, the Configuration Assistant, and the Net Manager. Note that the Runtime option requires a total of 483MB of hard disk space. Then click Next to display the Install Location dialog box.

The Install Location dialog box prompts you for the directory where you want to install the Oracle client software. I chose to install to the C:ORACLE directory, which makes it easier for me to find the Oracle configuration files in case I need to check them later. Click Next to display the Summary dialog box in which you can confirm your choices. Clicking Install installs the Oracle client software.

Configuring the Oracle Client Software

Once the installation process is complete you need to tell the Oracle client software how to connect to the Oracle server. To configure the Oracle client, run the Oracle Net Configuration Assistant by selecting the Start, All Programs, Oracle – OraClient11g_home, Configuration and Migration Tools, Net Configuration Assistant menu option. The first screen displayed by the Oracle Net Configuration Assistant is the Welcome dialog box, shown in Figure 2.

Click Next to display a second Welcome dialog box where you can select the type of client configuration that you want to perform.

To configure the Oracle client for a linked server connection, select the Local Net Service Name configuration option where you can enter the Oracle server’s host and service names Clicking Next displays the Net Service Configuration dialog box. The Net Service Name Configuration dialog box lets you add a new Oracle Net Service configuration, as well as edit delete, or test an existing Net Service Name configuration. To create a new configuration, select Add and click Next. Doing so will display the Net Service Name Configuration dialog box, which is shown in Figure 3.

In the configuration dialog box (shown in Figure 3), you can enter the Service Name for the Oracle database. This name is created during the Oracle 11g server installation. The default value is ORCL, but this name can be changed. It’s essentially analogous to a SQL Server instance name. If you don’t know the Oracle service name, you can find it by going to the Oracle server and selecting the Start, All Programs, Oracle – OraDb11g_home1, Configuration and Migration Tools, Administration Assistant for Windows option. When the Administration Assistant for Windows starts, expand the Computers node, then the local host node, and click the Databases node. The Oracle database service names will be displayed in the right pane as Figure 4 shows.

After entering the Oracle database service name into the Oracle Net Configuration Assistant, click Next to display the Select Protocol dialog box in which you can select the network protocol that you’ll use to connect the client to the Oracle server. Select TCP from the list of protocols. Click Next to display the Oracle Net TCP/IP Protocol configuration dialog box, shown in Figure 5.

In the Host name text box you need to enter either the TCP/IP host name for the Oracle server or its TCP/IP address. In Figure 5 you can see that the value of OR-PORT-VORA11G has been entered. This is the DNS name that’s use to identify the Oracle server. Next, the TCP/IP port number is specified. This value must match the port that the Oracle Database Listener is using. In Figure 5 you can see that a default port of 1521 will be used. If the Oracle server isn’t using the default port, you can choose a different TCP/IP port value by clicking the Use another port number radio button and entering the alternate value in the text box.

After all of the database server and network protocol information has been entered you can test your connection to the Oracle database server using the dialog boxes displayed by the Oracle Net Configuration Assistant. If all of your Net Service configuration parameters are correct, you’ll see a Connecting… Test successful message. If the test fails, you can click the Back button to step your way back through the Oracle Net Configuration Assistant dialog boxes to check and change any incorrect values. For more information about possible connection problems, refer to the sidebar "Troubleshooting Oracle Client Connections."

Configuring the Linked Server

After the Oracle client software has been installed and configured you’re ready to use the SQL Server tools to create your linked server. You can create a linked server using either SQL Server Management Studio (SSMS) or by executing T-SQL statements. The sidebar “T-SQL Commands for Linked Servers” lists of some of the T-SQL commands you can use to configure and manage linked servers. To create the linked server using SSMS, open SSMS, expand the Server Objects node, right-click the Link Servers node, and select New Linked Server from the context menu. Doing so will display the New Linked Server screen that Figure 6 shows.

The linked server name is a descriptive name for the linked server and can be any valid SQL Server name. In Figure 6 you can see that I chose the system name of OR-PORT-VORA11G. You can select the OLE DB provider that you want to use from the Provider drop-down list. In Figure 6 you can see that I selected the Oracle Provider for OLE DB. I could have also selected the Microsoft OLE DB Provider for Oracle. The Product name text box accepts the OLE DB provider’s product name. If you don’t know this name, you can expand the Server Objects, Linked Server, Provider node to see a list of the installed OLE DB providers. The product name for the Oracle OLE DB Provider is OraOLEDB. In the Data source text box, enter the Oracle service name. In Figure 6 you can see that I used the value of ORCL.

Next, click the Security link to provide the authentication information that you need to connect to the Oracle database. You can see the linked server Security page in Figure 7.

You have several options for authenticating the linked server connection to the Oracle database. The example shown in Figure 7 uses the Be made using this security context option to map all linked server queries to the Oracle scott/tiger login, which has access to the sample SCOTT database. This type of one-for-all configuration is adequate for testing but you’ll want to use a more granular approach for most product applications. The best options are typically either to map local SQL Server logins to Oracle logins or to use current security context. To map SQL Server logins to Oracle logins, click the Add button and enter the login credentials for each system. The Be made with the login’s current security context option works best when the Oracle system is using Windows authentication.

Running Queries with the Oracle Linked Server

Once you’ve created and configured your linked server you can begin using it to run queries on the Oracle server. Queries that are directed to linked servers use the following four-part naming syntax: linked_server_name.catalog_name.schema_name.table_name. For example, to select all of the rows from the EMP table you would use the following SQL statement:

SELECT * FROM [OR-PORT-VORA11G]..SCOTT.EMP.

OR-PORT-VORA11g is the linked server name. It’s enclosed in brackets because the name contains dashes. When using the Oracle OLE DB provider, the catalog name should be left blank. The schema name is SCOTT. This corresponds to what SQL Server users would consider the database name. The table name is EMP. You can see the results of running the linked server query in Figure 8.

One important point to note regarding linked servers is that the SQL Server distributed query processor is responsible for optimizing the commands that are sent to the linked server to retrieve data. SQL Server will first query the linked server to determine the level of SQL dialect that it supports and attempt to push operations such as joins, sorts, and grouping to the remote server. For more information about how SQL Server optimizes distributed queries, go to msdn.microsoft.com/en-us/library/ms180972.aspx.

Seamlessly Connect SQL Server and Oracle

SQL Server’s linked server feature lets you create a nearly seamless connection between SQL Server and Oracle. Linked servers can be used for queries and database insert, update, and delete operations. If you need to retrieve and use Oracle data in your SQL Server applications, then linked servers could be just the ticket you’ve been waiting for.