Linked Severs on MS SQL Part 4, Oracle
January 6, 2004
In previous articles, we created Linked Servers to Excel, Access and MS SQL. Several stored procedures were introduced for creating links, and the security profiles that accompany them. For this article, sp_AddLinkedServer and sp_AddLinkedSrvLogin should be familiar. Several different access methods have been introduced, primarily Open Query and Direct Access, as well as the different security configurations used to connect with them such as Windows Integrated Security and SQL security.
This article will be concerned with using Oracle as a Linked Server target from MS SQL. Because the previous articles in this series have been written from a Microsoft product point of view, we will continue this way assuming familiarity with MS SQL, and relatively little experience with Oracle.
From the number of forum and news group posts with questions on SQL to Oracle linking, there appears to be a high level of Oracle confusion. A couple of main reasons seem to be responsible for this.
Basic Oracle terms, like database and instance, are different from what the MS SQL DBA would expect, leading to syntactical errors. The other unusual piece is the fact that third party software is required to complete the link. An Oracle client, not available from Microsoft, is required. BOL tells you that it is needed, but because it is from a third party, it does not tell you what to do with it.
This article was written using Oracle 9i Enterprise Edition Release 220.127.116.11.0 on Windows 2000 Server as the target, with Microsoft SQL 2000 Developer Edition on Windows 2000 Professional as the parent.
The first step in an Oracle Linked Server is to obtain the Oracle Client. SQL will hand off all Oracle information requests to this client. The path is SQL, to the Oracle Client, to Oracle. Then back from Oracle, to the Oracle Client, to SQL.
If you have the Oracle media, you are all set. If not, it can be downloaded from Oracle at http://otn.oracle.com/software/products/oracle9i/index.html. Joining the free Oracle Technology Network may be required.
Once you have the media, run setup.exe to start the Oracle Universal Installer. If space permits, install the "Runtime" version. In addition to the required Oracle Client, some helpful tools will be installed.
With the client installed, go to Programs, Oracle - OraHome92, Configuration and Management Tools, Net Manger. The purpose of the Net Manager will be to create an Oracle server name mapping. Think of this as WINNS, or an Oracle DNS host file creation tool. The end result of the Net Manger will be a file called TNSNames.ORA. The Oracle client will use the information in this file to find the correct Oracle server when SQL gives it a server name.
From within Net Manager, highlight Service Naming, and then click the plus sign on the left. The first dialog will ask for the service name. This will be the alias name that SQL uses for Oracle. In this example, we will call it OracleTest
For Page 2, select TCP/IP as the protocol.
On page 3, the host name is the standard Windows machine name. Port Number is usually 1521. Net Manager on the Oracle server can verify the port and protocol. Check under Local, Listeners.
On Page 4, the real Oracle Service Name is needed. This is a Global Database Name consisting of the Oracle database and domain names. An Oracle database is the equivalent of an SQL instance. In my case, the database is InfoOra and the domain is PCC.INT. Making the Service Name INFOORA.PCC.INT.
If you are unsure of the service name and have access to an Oracle Enterprise Manger, under databases, navigate to the correct database and right click the name. Select "View, Edit Details." At the bottom of the general tab that will appear on the left, click "All Initialization Parameters." There will be an entry for "service_names." This is the correct service name needed.
Page 5 is the test. You should get a successful result. There are two main reasons you may not: a configuration error on pages 1 through 4, or the Oracle user "Scott" with password "tiger" is incorrect. This user is the Oracle equivalent to SQL pubs or Northwind, with an accompanied built in user name. If the user name is the problem, use the "Change Login" button on page 5 and try the test again.
From the top menu, select File, Save Network Configuration.
Here is our end result:
Through the GUI, a file called TNSNames.ORA was created in the Oracle directory of the client. This file can also be made by hand and placed into the Ora92\network\admin directory.
ORACLETEST.PCC.INT = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = infoora)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = INFOORA.PCC.INT) ) )