Karush Logo
BlogAbout

Oracle Linked Server

Create a Linked Server from SQL Server to Oracle

There was not any good, current documentation anywhere regarding how to do this. Lots of old, vague, or incomplete information exists. This documentation will be a detailed and tested sequence of steps for configuring a linked server from SQL Server to Oracle.

The below steps were tested on Windows Server 2016 and SQL Server 2019.

Oracle Client Download

The first thing you need to do is download and install the "Oracle Client" on your SQL Server. Of course there are 10 different things at oracle.com that are similar to "oracle client", so here is the specific link:

Oracle Client 19c

There are still many different things that can be downloaded on this page. Download the file named: WINDOWS.X64_193000_client.zip (for installation on a 64-bit Windows server/PC)

You will need to have or create an oracle.com account in order to download files from oracle.com.

Apparently, the client should match your database version and this link is appropriate for the database version used in my case. The DBA for your Oracle server should be able to direct you to the right download link for your Oracle version.

Oracle Client Install

Next, make a folder on your server at: C:\ORACLE
We'll use this in a minute.

Unzip the download (WINDOWS.X64_193000_client.zip) and run setup.exe as administrator.

In the installation wizard:

  • Select Installation Type: select "Administrator".
  • Oracle Home User Selection: select the default "Use Windows Built-in Account"
  • Specify Installation Location:
    • Oracle Base: set this to: "C:\Oracle\app\client"
    • Software Location: set this to: "C:\Oracle\app\client\product\19.0.0\client_1"

Click Next and Prerequisite Checks will be performed. On the Summary screen, click "Install".

After a few minutes the installation will complete. Click Close.

Reboot.

Open Powershell and type: "sqlplus" You should some sqlplus version information and not an error message. This indicates the Oracle Client installed correctly.

Oracle Client Configuration - TNSNAMES file

Navigate to this folder using File Explorer: C:\Oracle\app\client\product\19.0.0\client_1\network\admin

Create an empty file in that location named: tnsnames.ora

Copy the contents from APPENDIX A below into the tnsnames.ora file. Your Oracle DBA will need to give you the proper values for HOST and SERVICE_NAME.

Open Powershell and enter: tnsping ORACLEPROD

You should get a quick response that says "Used TNSNAMES adapter to resolve the alias". If you get "failed to resolve name" something isn't correct.

Oracle Client Configuration - ORACLE_HOME

Open Powershell enter: dir env: to show all environment variables. If you don't see "ORACLE_HOME" listed, you will need to add it.

Enter: $env:ORACLE_HOME = "C:\Oracle\app\client\product\19.0.0\client_1"

(note this can also be added via File Explorer -> This PC -> Properties -> Advanced System Settings -> Environment Variables)

Again enter: dir env: to confirm ORACLE_HOME is now listed.

Oracle Client Configuration - System DSN

In the Windows search bar, search for and open "ODBC Data Sources (64-bit)".

Click on the System DSN tab. Click Add... Select the Oracle driver, named like "Oracle in OraClient 19Home 1". Click Finish.

Enter:

  • Data Source Name: ORACLEDS
  • Description: ORACLEDS
  • TNS Service Name: ORACLEPROD (note this must match the value in the first line of your tnsnames.ora file)
  • User ID: [the Oracle User ID value for the persistent linked server connection. this Oracle User ID must be configured in Oracle with connect and select permissions]

Click "Test Connection". Enter the password for the Oracle User ID. Make sure that you see a connection successful message. Click Ok.

SQL Server Configuration - Linked Server

Connect to your SQL Server instance from a PC that has SQL Server Managment Studio (SSMS) installed using an account with sysadmin permissions.

Under Server Objects select New -> Linked Server

Enter (on General page):

  • Linked Server: ORACLEPROD (can be any name)
  • Server type: select "Other Data Source"
  • Provider: select "Microsoft OLEDB Provider for ODBC Drivers" (not Oracle...!)
  • Product Name: Oracle (can be any name)
  • Data Source: ORACLEPROD (note: this must match the name of the System DSN configured earlier)
  • Provider String and Catalog can be left blank

Enter (on Security page):

  • Click Add to add a login mapping.
  • Local Login: select a user that should have access to the Oracle server
  • Impersonate: leave unchecked
  • Remote User: recommend using the same Oracle User ID used earlier under System DSN
  • Remote Passord: password for Oracle User ID
  • Click Add again to add the row. Enter more users if needed.
  • Click Ok when done.

SQL Server Configuration - Linked Server - Testing

Get a schema and table name from the Oracle DBA.

Open a new query window and run a select query using this syntax: SELECT * FROM OPENQUERY(ORACLEPROD, 'select * from [schema].[table]')

You should see rows of data returned from the Oracle database.

Conclusion

Making a linked server connection to Oracle is not nearly as straight foward as to SQL Server or MYSQL, but it is possible.

APPENDIX A:

ORACLEPROD=
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=[your server IP or name])(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=oracleprod))
)