Connecting Oracle to SQL Server over a secure connection

The SQL Server ODBC driver enables you to connect Oracle on Linux and UNIX platforms to SQL Server or SQL Azure. If you decide to encrypt the network connection from your client applications to SQL Server, or you are using SQL Azure, you need to use the TLS version of the driver, which is included in the SQL Server ODBC driver distribution.

If you do not use the TLS version of the SQL Server ODBC driver to connect to a SQL Server instance that has been configured to request an encrypted connection, you will get this error:

Client unable to establish connection: SSL requested but not supported

If you are already using the Easysoft SQL Server ODBC driver with Oracle:

1. In the SQL Server data source pointed to by HS_FDS_CONNECT_INFO in your initSID.ora, make the following alterations:
   1. Alter the Driver entry from:

      Driver = Easysoft ODBC-SQL Server

      to:

      Driver = Easysoft ODBC-SQL Server SSL

   2. Add these lines:

      Encrypt = Yes
      TrustServerCertificate = Yes

2. If you point to the driver library rather than the Driver Manager library in initSID.ora, alter the HS_FDS_SHAREABLE_NAME value to:

   install_dir/easysoft/sqlserver/lib/libessqlsrv_ssl.so

3. Restart the Oracle listener.

If you are a new user:

1. Install and license the SQL Server ODBC driver on the machine where Oracle is installed.

   For installation instructions, refer to the ODBC driver documentation.

   Note You need the unixODBC Driver Manager installed on your machine. The Easysoft distribution includes a version of the unixODBC Driver Manager that the Easysoft SQL Server ODBC driver has been tested with. The Easysoft driver setup program gives you the option to install unixODBC.

2. Create an ODBC data source in /etc/odbc.ini that connects to the SQL Server database you want to access from Oracle. For example:

   [SQLSERVER_SAMPLE]
   Driver                 = Easysoft ODBC-SQL Server SSL
   Server                 = my_machine\SQLEXPRESS
   User                   = my_domain\my_user
   Password               = my_password
   # If the database you want to connect to is the default
   # for the SQL Server login, omit this attribute
   Database               = Northwind
   Encrypt                = Yes
   TrustServerCertificate = Yes

3. Use isql to test the new data source. For example:

   cd /usr/local/easysoft/unixODBC/bin
   ./isql.sh -v SQLSERVER_SAMPLE

   At the prompt, type help to display a list of tables. To exit, press Return in an empty prompt line.

   If you are unable to connect, refer to this article for assistance.

4. Create a DG4ODBC init file. To do this, change to the hs/admin subdirectory. For example:

   cd $ORACLE_HOME/product/11.2.0/xe/hs/admin

   Create a copy of the file initdg4odbc.ora. Name the new file initmssql.ora.

5. Ensure these parameters and values are present in your init file:

   HS_FDS_CONNECT_INFO = var>my_mssql_odbc_dsn

   Replace my_mssql_odbc_dsn with the name of a SQL Server ODBC driver data source that connects to the target SQL Server instance. For example

   HS_FDS_CONNECT_INFO = "SQLSERVER_SAMPLE"

6. Comment out the line that enables DG4ODBC tracing. For example:

   #HS_FDS_TRACE_LEVEL = <trace_level>

7. Add an entry to listener.ora that creates a SID_NAME for DG4ODBC. For example:

   SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC=
        (SID_NAME=mssql)
        (ORACLE_HOME=$ORACLE_HOME)
        (PROGRAM=dg4odbc)
      )
    )

8. Add a DG4ODBC entry to tnsnames.ora that specifies the SID_NAME created in the previous step. For example:

   MSSQL =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_host)(PORT = 1521))
       (CONNECT_DATA =
         (SID = mssql)
       )
       (HS = OK)
     )

   Replace oracle_host with the host name of your Oracle machine.

9. Start (or restart) the Oracle Listener. For example:

   lsnrctl stop
   lsnrctl start
   

10. Connect to your Oracle database in SQL*Plus.
11. In SQL*Plus, create a database link for the target SQL Server instance. For example:

    CREATE PUBLIC DATABASE LINK mssqllink CONNECT TO
    "my_sqlserver_user" IDENTIFIED by "my_password" USING 'mssql';
    SELECT * from suppliers@mssqllink
    

If you have problems connecting to SQL Server from Oracle, enable DG4ODBC tracing and check the trace files written to the hs/log directory. To enable DG4ODBC tracing, add the line HS_FDS_TRACE_LEVEL = DEBUG to initmssql.ora and then start or restart the Oracle listener. If the log directory does not exist, create it. For example:

mkdir log
chmod +w log