Connect AWS RDS Custom for Oracle to SQL Server

To connect an Oracle database(this time we use an AWS RDS Custom for Oracle) with an SQL Server (Cloud or On-Prem) you could make use of the Oracle database gateway for SQL Server.

In general the gateway serves as a link between Oracle and external systems.

On a high level the setup looks like the following

In general the setup will , with small adoptions, work on any Oracle database server.

We will focus on the technical steps how to configure a link between Oracle and SQL Server and highlight some lessons learned and issues :-)

Prerequisites

• AWS RDS Custom for Oracle

• SQL Server instance

• Network connection between the above

ODBC Driver installation

Connect to your instance via ssh or session manager

download the package which matches your operating system (we use RHEL 7.9 here) if you're not sure which version to choose check your os version with

cat /etc/os-release

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

install ODBC driver for SQL

sudo ACCEPT_EULA=Y yum install -y msodbcsql18

install ODBC dev packages (provides libraries etc)

sudo yum install -y unixODBC-devel

to have some test tools by hand install MSSQL tools

sudo ACCEPT_EULA=Y yum install -y mssql-tools18

(optional) add the tools to your .bashrc

echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc 
source ~/.bashrc

Configure the ODBC Gateway

we need 2 files to get the ODBC gateway to worl

• /etc/odbcinst.ini

• /etc/odbc.ini

here is an example of the odbcinst.ini

cat /etc/odbcinst.ini
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.4.so.1.1
UsageCount=1

check the driver path according to your system, it may differ from the above

now let's check the odbc.ini find a template below, the value in the square brackets is the name of your data source

[MSSQL]
Driver=ODBC Driver 18 for SQL Server
Description=Microsoft ODBC Driver 18 for SQL Server
Server=mysqlserver.onprem.local
Port=1433
Database=northwind
User=demouser
Password=23bonobo42
TrustServerCertificate=yes
QuotedId=YES
AnsiNPW=YES
Threading=1
UsageCount=1
AutoTranslate=No

full ref for the config above here https://learn.microsoft.com/en-us/sql/connect/odbc/dsn-connection-string-attribute?view=sql-server-ver16

it's highly recommended to encrypt the password instead of using a cleartext one as above. see this doc for instructions how to achieve this https://docs.oracle.com/en/database/oracle/oracle-database/19/heter/initialization-parameters.html#HETER4010

Test the connection with sqlcmd

/opt/mssql-tools18/bin/sqlcmd -D -S MSSQL -U demouser
Password:
1> select DB_NAME()
2> go
--------------------------------------------------------------------------------------------------------------------------------
northwind
(1 rows affected)

Create the DB Link

a) switch to rdsdb user (or Oracle Home owner if not on AWS RDS)

b) go to $ORACLE_HOME/hs/admin directory

c) create a file init$yourdatasource.ora, where $datasource is the name of the configured datasource in the odbc.ini from above. In our case we name the file initMSSQL.ora

d) open the initMSSQL.ora and configure as below

HS_FDS_CONNECT_INFO = MSSQL
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_TRACE_LEVEL = DEBUG
HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8
HS_KEEP_REMOTE_COLUMN_SIZE = LOCAL
HS_NLS_NCHAR = UCS2
set ODBCINI=/etc/odbc.ini
set LD_LIBRARY_PATH=/opt/microsoft/msodbcsql18/lib64/:/usr/lib64:/rdsdbbin/oracle.19.custom.r1.EE-CDB.1/lib
HS_FDS_SQLLEN_INTERPRETATION=64
HS_FDS_TIMESTAMP_MAPPING=DATE

some explanation: HS_FDS_CONNECT_INFO: refers to the name set in the odbc.ini, adapt to your setup if needed HS_FDS_SHAREABLE_NAME: check if the path is correct and adapt to your environment if needed HS_FDS_TRACE_LEVEL: enable for debugging, use DEBUG if you need detailed information what's going on, disable with OFF or enable with ON LD_LIBRARY_PATH: check the paths of your environment and adapt accordingly, above paths are taken from an AWS RDS Custom

HS_NLS_NCHAR and HS_LANGUAGE: this setting addresses character set incompatibility between Oracle and the ODBC driver. Without setting this parameters the connection did not work for me. so keep an eye on this

e) Configure tnsnames.ora

• using localhost here as we're running everything on the same machine.

• decided to go with a separate listener, therefore using port 1522 -specify HS=OKto ensure Oracle recognises the usage of Oracle heterogeneous services. IMPORTANT keep the HS=OKout of the CONNECT_DATA otherwise the connection won't work

MSSQL =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
   )
   (CONNECT_DATA =
     (SID = MSSQL)
   )
     (HS = OK)
 )

f) configure the listener.ora

I've decided to create a separate listener to not harm database connections while testing around.

add the following to listener.ora and adapt accordingly.

[...]
LISTENER_ODBC=
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
    )
  )

SID_LIST_LISTENER_ODBC =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = MSSQL)
      (ORACLE_HOME = /rdsdbbin/oracle.19.custom.r1.EE-CDB.1)
      (PROGRAM = /rdsdbbin/oracle.19.custom.r1.EE-CDB.1/bin/dg4odbc)
      (ENVS = "LD_LIBRARY_PATH=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.4.so.1.1:/opt/microsoft/msodbcsql18/lib64:/usr/lib64:/rdsdbbin/oracle.19.custom.r1.EE-CDB.1/lib")
    )
  )

g) start listener and check status

lsnrctl start LISTENER_ODBC
lsnrctl status LISTENER_ODBC

[...]
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
Services Summary...
Service "MSSQL" has 1 instance(s).
  Instance "MSSQL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

h) test connectivity with tnsping

result should be like the following

tnsping MSSQL
[...]
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))) (CONNECT_DATA = (SID = MSSQL)) (HS = OK))
OK (0 msec)

i) create the database link

create public database link demo connect to demouser identified by "23bonobo42" using 'MSSQL';

test the DB Link

select count(*) from dbo.mydemotable@demo; 

  COUNT(*)
----------
         2

lessons learned

Don't use HS=OKinside CONNECT_DATA in tnsnames.ora

• Error Ora-28500 and Sqlstate I or Displays a Square Bracket - [ - Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Non-Oracle Databases Using the UnixODBC Driver Manager (Doc ID 756186.1)

check for HS_NLS_NCHAR and HS_LANGUAGE settings

• ORA-28500: Connection From ORACLE To A Non-Oracle System Returned This Message: C (Doc ID 2325424.1)

• Error Ora-28500 and Sqlstate I or Displays a Square Bracket - [ - Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Non-Oracle Databases Using the UnixODBC Driver Manager (Doc ID 756186.1)

all time favourite, don't use a passwort >30

• ORA-00972: "identifier is too long" when using >30 characters / bytes for a user PASSWORD (Doc ID 2689690.1)