Call now: (800) 766-1884  


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA

 SQL Server Scripts
 Scripts Menu





Accessing SQL Server from an Oracle Database

Expert Oracle Tips by Steve Callan

October 27, 2010

Setting up a connection to Oracle Database from SQL Server is fairly easy, but the opposite is not so true. Steve Callan walks you through a complete example of how to access SQL Server from Oracle Database using, named Database Gateways (Oracle's new and improved method).

Setting up a connection to Oracle Database from SQL Server is fairly easy, but the opposite is not so true. Connecting Oracle Database to external data sources was formerly handled using OHS, or Oracle Heterogeneous Services. The new and improved method is named Database Gateways, and the name reference is commonly seen as DG4ODBC (or dg4odbc). The key part of this connection architecture is based on ODBC.

A new (since Oracle 11gR1) piece of this setup involves downloading and installing DG4ODBC resources into an existing Oracle RDBMS installation. The product title for Oracle 11gR2 on Windows (32-bit), as an example, is “Oracle Database Gateways 11g Release 2 ( for Microsoft Windows (32-bit)” and is found under the download section for Databases at OTN. The documentation for database gateways includes seven guides, covering various flavors of UNIX, plus Windows. Of interest for us in this article is the Database Gateway Installation and Configuration Guide for Microsoft Windows guide, given that most users are using Windows to learn Oracle.

What do you need for a test environment? Obviously Oracle database, and although several ODBC data sources are covered in the guide (Sybase et al), the external RDBMS here is SQL Server. How do you get SQL Server? Download it from MSDN and sign up for a 180-day trial. Or, as covered before, spring for a copy of the developer edition and get an Enterprise Edition installation for a fraction of the commercial retail price. In addition to the SQL Server installation, you’ll also need some SQL Server databases.

Once the database systems are in place (Oracle 11gR2 and SQL Server 2008 for this example), it becomes a fairly simple matter (well, almost) of following instructions regarding the DG4ODBC installation and configuration. There are three sources of information on how to configure Oracle to access SQL Server, and you can pick and choose among them to get the final answer. The sources include the instructions in the guide, plus two documents on My Oracle Support:

"How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit (Doc ID 466225.1)"

"How to Setup DG4MSQL (Database Gateway for MS SQL Server) on Windows 32bit (Doc ID 466267.1)"

The differences include how to specify the HS_FDS_CONNECT_INFO parameter in the initdg4msql.ora file, which is probably the single most important parameter in the entire file. One document shows use of a colon when specifying a port number, the other uses a comma. Both of them use a sample select statement against “systables” when the actual table is sys.tables. So, one thing you’ll want to know ahead of time is a valid table in the SQL Server database you’re trying to connect to.

You’ll need to collect the following information (for the OUI) regarding the target database system:

SQL Server database server host name – if running Oracle and MSSQL on the same server, this is simply your computer’s name.

SQL Server database server port number – just as Oracle uses 1521 as a standard port, MSSQL uses 1433 as its typical port. To find the port, run the following:

DECLARE @tcp_port nvarchar(5)
EXEC xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@value_name = 'TcpPort',
@value = @tcp_port OUTPUT

select @tcp_port

Note: When you get to the actual installation of Gateways, the OUI never asks for the port number (despite what the guide shows), so what you do need is the server name, instance name, and a database name. Also, the installer never starts the Net Configuration Assistant (as shown in the documentation).

SQL Server database name – either of the AdentureWorks or AdventureWorksDW databases are suitable.

Download and unzip (not in your Oracle software installation) the gateway file, and run setup.exe. Given that Oracle 11g is already installed, it is highly likely the Oracle Universal Installer for Gateways will start up with no problem. You’ll see the typical welcome screen for 11g.

The documentation states that you don’t need to edit the Oracle home value or path, but which Oracle home were the writers referring to? Installation of this add-on is where having a CSI for Oracle support comes in handy: the tech notes tell you that installing in an existing Oracle home is okay. The caveat on this is that if your Oracle installation is patched, then you will have to re-patch the installation as the DG4ODBC install may overwrite patched files.

I’ll use my existing Oracle home, going from this: this:

We don’t need all of the gateways, so only the one for SQL Server is selected.

Here’s where the SQL Server information comes into play. My computer has SQL Server 2005 installed as the default instance, and SQL Server 2008 installed as a named instance, so that’s why you see WIN2003\SQL2008 (where SQL2008 is the named instance).

Confirm the details of the Summary window and click Install.

The installation process starts (mine took a few moments before the progress bar started to show any progress) and overall, takes about three minutes. The end of installation window shows that this was a success.

A new folder (dg4msql) was added to my 11gR2 installation.

  1. Configure the Gateway Initialization Parameter File

  2. Configure Oracle Net for the Gateway

  3. Configure the Oracle Database for Gateway Access

  4. Create Database Links

  5. Configure Two-Phase Commit

  6. Create SQL Server Views for Data Dictionary Support

  7. Encrypt Gateway Initialization Parameter Values

  8. Configure the Gateway to Access Multiple SQL Server Databases

Shown below are the steps to configure the gateway.

You can define your own SID for a gateway, but it is easier to accept the default SID Oracle creates for you: dg4msql. The benefit of using dg4msql is that you get a pre-configured initialization parameter file. If you have multiple connections, you’ll need multiple init files. The contents of the default file are shown below.

# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
# HS init parameters

I added several other examples in my file, and wound up using the comma-port format:

# alternate connect format is hostname/serverinstance/databasename

The next step is to configure the listener. No doubt, you already have a listener.ora file on hand, so all that is necessary here is to add an entry to the SID list, using the format below:


As a bonus, Oracle creates a sample listener.ora (and tnsnames.ora) file for you in the dg4msql\admin folder, so all you need to do is cut and paste the list entry into your main listener.ora file (and then reload the listener). The status output in lsnrctl should show the new entry:

Service "dg4msql" has 1 instance(s).
  Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...

You have to edit the tnsnames.ora file manually, but again, you can use the sample file in the dg4msql\admin folder. Just add the sample entry (adjusting for port number if necessary) into the main tnsnames.ora file: 

dg4msql  =

Create a database link, using double-quoted identifiers for the username and password, and single quotes for the tnsnames value.

"Oracle" IDENTIFIED BY "oracle" USING 'dg4msql';

Skipping the two-phase commit part, the next step includes running a script that creates Oracle-like data dictionary views in MSSQL. Open a New Query in SQL Server Management Studio (while connected as sa or as an Administrator), open the file (which pastes it into the query window) and run it. There will be several error messages as the script contains drop statements against objects that don’t exist (yet).

At this point, you should be able to query against SQL Server. The output of select * from all_users@dg4 is shown below.

USERNAME                        USER_ID      CREATED
--------------------            ----------  ---------
public                          0            08-APR-03
dbo                             1            08-APR-03
guest                           2            08-APR-03
INFORMATION_SCHEMA              3            14-OCT-05
sys                             4            14-OCT-05
Oracle                          5            19-OCT-10
db_owner                        16384        08-APR-03
db_accessadmin                  16385        08-APR-03
db_securityadmin                16386        08-APR-03
db_ddladmin                     16387        08-APR-03
db_backupoperator               16389        08-APR-03
db_datareader                   16390        08-APR-03
db_datawriter                   16391        08-APR-03
db_denydatareader               16392        08-APR-03
db_denydatawriter               16393        08-APR-03

A query against a table in the AdventureWorks database:

select * from "AWBuildVersion"@dg4;

SystemInformationID Database Version VersionDa    ModifiedD
------------------- ---------------- ---------    ---------
                  1    26-APR-06    26-APR-06

Note that these two statements are not the same:

select * from "AWBuildVersion"@dg4;
select * from "awbuildversion"@dg4;

The double-quoted identifier is important as the case matters when selecting from Oracle into SQL Server. If the table is in a namespace, such as HumanResources.Department, then double-quote the namespace and table. For example:

select * from “HumanResources”.”Department”@dg4;

If selecting columns by name, double-quote the column names as well.

SQL> select "DepartmentID", "Name", "GroupName"
  2  from "HumanResources"."Department"@dg4;

DepartmentID   Name                      GroupName
------     ----------------            ---------------------
     1     Engineering                 Research and Development
     2     Tool Design                 Research and Development
     3     Sales                       Sales and Marketing
     4     Marketing                   Sales and Marketing
     5     Purchasing                  Inventory Management
     6     Research and Development    Research and Development
     7     Production                  Manufacturing
     8     Production Control          Manufacturing
     9     Human Resources             Executive General and Administration
    10     Finance                     Executive General and Administration
    11     Information Services        Executive General and Administration
    12     Document Control            Quality Assurance
    13     Quality Assurance           Quality Assurance
    14     Facilities and Maintenance  Executive General and Administration
    15     Shipping and Receiving      Inventory Management
    16     Executive                   Executive General and Administration

The other configuration options can be tested in your environment (encryption and multiple databases) as needed. Now you have a complete, from start to finish, example of how to access SQL Server from Oracle.


Get the Complete
Oracle Tuning Details 

The landmark book "Oracle Tuning: The Definitive Reference Second Edition" has been updated with over 1,500 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle 11g performance and you can buy it for 40% off directly from the publisher.



Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.

Burleson Consulting
SQL Server database support


Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.



Burleson Consulting Remote DB Administration