Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
I need step-by-step instructions for installing an Oracle database server and setting up a SQL Server Linked Server that can query the data within the Oracle database server. Please include potential issues and work-arounds with both configuring the Oracle database server after installation and the SQL Server Linked Server. Also, include at least one query that I can run to verify the Linked Server can run a SELECT statement.
Both Oracle and Microsoft have been enhancing their database technology in ways that obsolete some prior documentation and examples about how to setup a SQL Server Linked Server that enables queries from an Oracle database. The instructions for this tip are for SQL Server 2014 and Oracle Database 11g Release 2. Both releases are relatively recent, but not likely to change materially going forward (because there have been major new versions in both cases). Therefore, the tip about creating a SQL Server Linked Server to query Oracle data will likely be stable going forward - at least for the referenced versions.
Here's an overview of the steps that will be covered:
- Download the files for the Oracle database server.
- Copy downloaded files and run the setup program.
- Configure the Oracle database server for queries of its sample database.
- Run code to set up and configure Linked Server with a special focus on the
- SQL Server Oracle provider
- SQL Server Linked Server
- SQL Server linked login
This tip assumes that you have already installed SQL Server 2014. Learn more - click here.
Download the files for the Oracle database server
You can download Oracle 11g Release 2 from this link. You need to download two zip files:
Copy downloaded files and run the setup Oracle program
The two zip files each contain collections of folders, which in turn contain files. Before running the setup program from within the win64_11gR2_database_1_of_2.zip file, you need to copy the contents of each zip file folder from Downloads to one common folder, such as c:\download.
The folders within the win64_11gR2_database_1_of_2.zip file include the directory path of ..\database\stage\Components\. The win64_11gR2_database_2_of_2.zip file also contains the directory path of ..\database\stage\Components\.
Start by copying all folders and files from win64_11gR2_database_1_of_2.zip. Next, copy all folders from within the ..\database\stage\Components path of the win64_11gR2_database_2_of_2.zip file to the ..\Components folder within the c:\download directory. After the files from both zip files from Downloads are in the same directory (c:\download) outside of Downloads, you can invoke setup.exe to wherever you copied the files, such as c:\download.
Next, follow the instructions in this URL about how to respond to the Oracle GUI for installing Oracle 11g. Step 3 is especially important for the sample database because it names the sample Oracle database; this name is referenced by the code to add a Linked Server in SQL Server pointing at the sample Oracle database. All critical steps for the Oracle database server installation are covered in the URL. I did not take screen shots during my installation of the Oracle database server, but all I did was follow the instructions at the URL. As a sidebar, it may be worth noting that uninstalling the Oracle database server is substantially more involved that installing it.
After the installation process completes, reboot your computer. If your installation went like mine, then your Services listing will include lines like those in the following screen shot.
Configure the Oracle database server for queries
The sample database for Oracle 11g includes a schema owner named hr. However, the account for the schema owner is locked initially. Also, the account is not connected to the database server. Without remedying these issues, there is no way to query the sample database from a SQL Server Linked Server.
The following screen shot shows how to unlock and connect the account for the hr schema owner in the sample database for the Oracle database server.
- First, launch SQL*Plus, an Oracle command-line utility, as the system dba. The Oracle database server includes SQL*Plus.
- After entering SQL*Plus, invoke the alter user command to unlock the account
for the hr schema owner
- The name of the account is hr
- The password for the account is initially hr
- Run the conn command so the hr account connects to the server. This allows a confirmation that the account can connect to the server.
- Close SQL*Plus and the command-line session (for example, click X to close the cmd window).
Run code to set up and configure Linked Server to sample Oracle database
After installing and configuring the Oracle database server, you can create a Linked Server in SQL Server that points at the Oracle sample database and the hr account. It is possible to do this manually with settings that you make from the Object Explorer or with T-SQL code. The issues that you address are the same either way. This tip focuses on how to implement the Linked Server with T-SQL code, but it also shows the results of the code from Object Explorer and the Linked Server properties dialog box.
Your Linked Server in SQL Server connects to an Oracle database through the ORAOLEDB.Oracle provider. This provider is available by default with a SQL Server 2014 installation. You need to initialize the provider so that the Linked Server using the provider runs in process within SQL Server. The following line of T-SQL accomplishes this.
-- Configure ORAOLEDB.Oracle provider so that runs in process within SQL Server exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1
HHere's a screen shot that shows the result of the setting made in the T-SQL code.
The next step is to add a Linked Server with the sp_addlinkedserver system stored procedure. The sample below sets several key parameters:
- @server is the name for the Linked Server within SQL Server; in this case the name OrclDB was arbitrarily designated (any other name would work equally well)
- @srvproduct designates the name of the product to which the Linked Server connects; in this case Oracle is appropriate
- @provider points to the provider that makes a connection to Oracle from SQL Server; you already know that we are using the OraOLEDB.Oracle provider
- @datasrc is a name to reference the remote data source; in the context of this demonstration, it is the database name specified during the installation of the Oracle database server (step 3 from the Oracle installation guide)
-- Add a Linked Server sp_addlinkedserver @server = 'OrclDB' ,@srvproduct = 'Oracle' ,@provider = 'OraOLEDB.Oracle' ,@datasrc = 'ORCL'
TThe preceding code adds a new Linked Server named OrclDB to the Linked Servers collection under Server Objects in Object Explorer. The settings also affect both the General page for the Linked Server properties dialog box. The following two screen shots show the outcomes of these settings.
After adding the Linked Server, you will need to configure it for remote procedure calls so that your queries from within SQL Server will return results obtained from the Oracle database. The following T-SQL code shows how to configure this setting.
-- Configure the server for remote procedure calls exec master.dbo.sp_serveroption @server=N'OrclDB' ,@optname=N'rpc out' ,@optvalue=N'true'
TThe outcome of this code can be seen on the Server Options page of the OrclDB Properties dialog window.
Finally, you need to add and configure a linkedsrvlogin so that the current SQL Server account can connect to and query the sample Oracle database. The following sample code lets any SQL Server account connect to the sample database with the hr account that was previously unlocked and confirmed to be able to connect to the sample database.
-- Add a local SQL Server login that connects to the Oracle database sp_addlinkedsrvlogin @rmtsrvname = 'OrclDB' ,@useself = 'false' ,@locallogin = NULL ,@rmtuser = 'hr' ,@rmtpassword ='hr'
TThe outcome of this code can be seen on the Security page of the OrclDB Properties dialog window.
Verify queries from the SQL Server instance work against the sample database in the Oracle database server
The purpose of this tip is to create a Linked Server in SQL Server that points to an Oracle database that enables the return of Oracle database values in SQL Server. The following query statement requires upper case characters for both the schema and table names. Also, a four-part name is used as an argument for the FROM clause. The first part is the name of the Linked Server. The second part for the database name is blank, but the name is implicitly specified by the reference to the Linked Server. Aside from these special design issues, the query syntax is similar to other SQL Server query statements for extracting the top three rows from a table.
-- Required caps for schema and table names SELECT TOP 3 * FROM OrclDB..HR.EMPLOYEES
TThe following screen shot shows the result set from the preceding query statement.
I encourage you to enhance your understanding of Linked Servers within SQL Server for Oracle databases using this tip as a starting point. For your convenience, a script file with the code in this tip as well as a database backup is available for download. Some specific additional areas that may be worthy of your exploration include the following.
- Use a Linked Server to insert, update, and delete data in tables on an Oracle table. Examine T-SQL templates for implementing these capabilities by opening in Object Explorer the Tables collection under the default collection of the Catalogs collection for a Linked Server. By right-clicking a table name and choosing Script Table as, you can automatically generate T-SQL code for selecting, inserting, updating, and deleting data in the Oracle database to which the Linked Server points.
- The example presented in this tip permits any SQL Server user to access the Oracle database using one specifically designated account on the Oracle database server. However, the Security page of a Linked Server enables you to designate a specific list of one or more local accounts to a SQL Server instance that can access the remote database using one or more accounts on the remote Oracle server.
- Contrast Oracle 12c with prior Oracle database versions. Oracle 12c introduces a new multitenant architecture that permits one or more pluggable databases to reside within one container database. Prior Oracle versions, such as Oracle 11g, include just one Oracle database per Oracle database instance. An introduction to the multitenant architecture is available at this link. Some adjustment(s) to the steps in this tip for Oracle 11g databases will likely be required when working with multiple pluggable databases in Oracle 12c and beyond. The current Microsoft Linked Server focuses on accessing just one database per Oracle database server.
Last Update: 2016-07-29
About the author
View all my tips