Note:

Create database links from Autonomous Database with Customer-Managed Heterogeneous Connectivity to Microsoft SQL Server on a Private Endpoint

Introduction

In an organization data is stored in multiple databases and lot of times while you are working on a database you need to access objects on another database. This is where Oracle Database Link can help, a database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

In this tutorial, we will discuss about how you can access a Microsoft SQL Server Database (non-Oracle database) which is on a Private Endpoint from Oracle Autonomous Database Serverless (ADB-S) on a private endpoint. To access non-Oracle databases from ADB-S, we need to create database links from an Autonomous Database to an Oracle Database Gateway.

There are two options to create database links from an Autonomous Database to an Oracle Database Gateway to access non-Oracle databases that are on a private endpoint:

• Create database Links with Customer-Managed Heterogeneous Connectivity to Microsoft SQL Server Database on a Private Endpoint without a wallet (TLS)

• Create database Links with Customer-Managed Heterogeneous Connectivity to Microsoft SQL Server Database on a Private Endpoint with a wallet (TCPS)

Objectives

Create database links from Autonomous Database with Customer-Managed Heterogeneous Connectivity to Microsoft SQL Server (Non-Oracle Database) on a Private Endpoint.

Prerequisites

To keep it simple, we have installed the Oracle Database Gateway for SQL Server and Microsoft SQL Server 2019 on the same OCI VM Instance with Windows Server 2019.

Architecture

Below is a sample high level architecture of the solution

Image 1

Description of the illustration adb-s-db-link-to-mssql.png

Task 1.1: Configure Oracle Database Gateway for SQL Server

Before we begin, we need to ensure that Oracle Database Gateway for SQL Server is configured correctly. While installing Oracle Database Gateway for SQL Server you will be configuring ORACLE_HOME, make note of ORACLE_HOME location. For this tutorial, the location is ‘C:\app\oracle\product\19.0.0\tghome_1’.

There are four important files that you need to ensure are configured correctly.

This is how the files look like in our test environment for this tutorial.

ORACLE_HOME\dg4msql\admin\initdg4msql.ora

# 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
#
HS_FDS_CONNECT_INFO=mssqlvm1:1433//AdventureWorks
HS_FDS_TRACE_LEVEL=ODBC
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

ORACLE_HOME\network\admin\listener.ora

# listener.ora Network Configuration File: C:\app\oracle\product\19.0.0\tghome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.  
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mssqlvm1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = mssqlvm1)(PORT = 2484))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4msql)
         (ORACLE_HOME=C:\app\oracle\product\19.0.0\tghome_1)
         (PROGRAM=dg4msql)
      )
  )

ORACLE_HOME\network\admin\sqlnet.ora

# sqlnet.ora Network Configuration File: C:\app\oracle\product\19.0.0\tghome_1\NETWORK\ADMIN\sqlnet.ora    
# Generated by Oracle configuration tools.    
# This file is actually generated by netca. But if customers choose to    
# install "Software Only", this file wont exist and without the native    
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NONE)

NAMES.DIRECTORY_PATH= (TNSNAMES)

ORACLE_HOME\network\admin\tnsnames.ora

dg4msql=    
(DESCRIPTION=    
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))    
(CONNECT_DATA=(SID=dg4msql))    
(HS=OK)    
)

Restart the Oracle Database Gateway service, for this tutorial it is ‘OracleOraGTW19Home1TNSListener’ using Services Console (Services.msc).

You can check the listener status using lsnrctl status command:

C:\Users\opc>lsnrctl status    
LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 19-JUL-2023 11:45:54    
Copyright (c) 1991, 2019, Oracle.  All rights reserved.    
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mssqlvm1)(PORT=1521)))    
STATUS of the LISTENER    
------------------------    
Alias  LISTENER    
Version  TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production    
Start Date  19-JUL-2023 09:14:23    
Uptime  0 days 2 hr. 31 min. 32 sec    
Trace Level  off    
Security  ON: Local OS Authentication    
SNMP  OFF    
Listener Parameter File  C:\app\oracle\product\19.0.0\tghome_1\network\admin\listener.ora    
Listener Log File  C:\app\oracle\diag\tnslsnr\MSSQLVM1\listener\alert\log.xml    
Listening Endpoints Summary...    
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MSSQLVM1)(PORT=1521)))    
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=MSSQLVM1)(PORT=2484)))    
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))    
Services Summary...    
Service "dg4msql" has 1 instance(s).    
Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...    
The command completed successfully

  1. Create a login on MS SQL Server that has access on the MS SQL Server database. For example, we created a SQL Login ORACLE on MS SQL Server which has access to the MS SQL Server database AdventureWorks.

  2. Connect to Autonomous Database and create credentials to access the MS SQL Server database. The username and password you specify with DBMS_CLOUD.CREATE_CREDENTIAL are the credentials for the MS SQL Server database used within the database link. For this tutorial, we provided credentials for SQL Login ORACLE.

    BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
  credential_name => 'DB_LINK_CRED',
  username => 'ORACLE',	--Ensure username is in UPPERCASE
  password => 'USER_PASSWORD'
  );
END;
/

  3. Now create the database link on Autonomous Database to the Oracle Database Gateway for SQL Server using DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.

     BEGIN
      DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
           db_link_name =>       'MSSQLLINK',
           hostname =>           'gatewayhostname',
           port =>               '1521',
           service_name =>       'dg4msql',
           ssl_server_cert_dn =>  NULL,
           credential_name =>    'DB_LINK_CRED',
           directory_name =>      NULL,
           private_target =>      TRUE,
           gateway_link =>        TRUE,
           gateway_params =>      NULL);
 END;
 /

    Note: The private_target parameter must be set to TRUE when the target non-Oracle Database is on a private endpoint.

  4. Query the MS SQL Server Database using the Oracle DB Link.

    SELECT * FROM HumanResources.Employee@MSSQLLINK;

You can query the MS SQL Server Database which is on a private endpoint using Oracle DB Link without wallet from ADB-S.

Task 2.1: Configure Oracle Database Gateway for SQL Server

Now before creating the database links with Customer-Managed Heterogeneous Connectivity to Microsoft SQL Server Database on a Private Endpoint with a wallet, we need to create a wallet. We can create a self-signed wallet using orapki.

We will be creating the Server and Client wallet on the same machine where Oracle Database Gateway for SQL Server is installed. Since the gateway was installed on this machine orapki is already present here ‘C:\app\oracle\product\19.0.0\tghome_1\bin’.

Let’s create two folders for Server and Client wallet:

Commands to create a server wallet and add server certificate:

orapki wallet create -wallet C:\app\oracle\product\19.0.0\tghome_1\walletserver -pwd Password@123 -auto_login

orapki wallet add -wallet C:\app\oracle\product\19.0.0\tghome_1\walletserver -pwd Password@123 -dn "CN=windows" -keysize 1024 -self_signed -validity 3650 -sign_alg sha256

Commands to create a client wallet and add client certificate:

orapki wallet create -wallet C:\app\oracle\product\19.0.0\tghome_1\walletclient -pwd Password@123 -auto_login

orapki wallet add -wallet C:\app\oracle\product\19.0.0\tghome_1\walletclient -pwd Password@123 -dn "CN=mssqlvm1client" -keysize 1024 -self_signed -validity 3650 -sign_alg sha256

Now we need to export the certificates from server wallet and import it to client wallet and also export the certificates from client wallet and import it to server wallet.

Commands to export the certificates from the wallets:

orapki wallet export -wallet C:\app\oracle\product\19.0.0\tghome_1\walletserver -pwd Password@123 -dn "CN=windows" -cert C:\app\oracle\product\19.0.0\tghome_1\walletserver\server.crt

orapki wallet export -wallet C:\app\oracle\product\19.0.0\tghome_1\walletclient -pwd Password@123 -dn "CN=mssqlvm1client" -cert C:\app\oracle\product\19.0.0\tghome_1\walletclient\client.crt

Commands to import the certificates to the wallets:

orapki wallet add -wallet C:\app\oracle\product\19.0.0\tghome_1\walletserver -pwd Password@123 -trusted_cert -cert C:\app\oracle\product\19.0.0\tghome_1\walletclient\client.crt

orapki wallet add -wallet C:\app\oracle\product\19.0.0\tghome_1\walletclient -pwd Password@123 -trusted_cert -cert C:\app\oracle\product\19.0.0\tghome_1\walletserver\server.crt

Now ensure the following four files are updated correctly:

This is how the files look like in our test environment for this tutorial.

ORACLE_HOME\dg4msql\admin\initdg4msql.ora

# 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
#
HS_FDS_CONNECT_INFO=mssqlvm1:1433//AdventureWorks
HS_FDS_TRACE_LEVEL=ODBC
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

ORACLE_HOME\network\admin\listener.ora

# listener.ora Network Configuration File: C:\app\oracle\product\19.0.0\tghome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.    

WALLET_LOCATION =
  (SOURCE =
    (METHOD = File)
    (METHOD_DATA =
      (DIRECTORY=C:\app\oracle\product\19.0.0\tghome_1\walletserver)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mssqlvm1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = mssqlvm1)(PORT = 2484))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4msql)
         (ORACLE_HOME=C:\app\oracle\product\19.0.0\tghome_1)
         (PROGRAM=dg4msql)
      )
  )

ORACLE_HOME\network\admin\sqlnet.ora

# sqlnet.ora Network Configuration File: C:\app\oracle\product\19.0.0\tghome_1\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.    
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SSL_SERVER_DN_MATCH= (ON)

WALLET_LOCATION =
  (SOURCE =
    (METHOD = File)
    (METHOD_DATA =
      (DIRECTORY=C:\app\oracle\product\19.0.0\tghome_1\walletserver)
    )
  )

SQLNET.AUTHENTICATION_SERVICES= (NONE)

NAMES.DIRECTORY_PATH= (TNSNAMES)

ORACLE_HOME\network\admin\tnsnames.ora

dg4msql=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
    (CONNECT_DATA=(SID=dg4msql))
    (ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=2484))
    (CONNECT_DATA=(SID=dg4msql))
    (HS=OK)
  )

  1. Create a login on MS SQL Server that has access on the MS SQL Server database. For example, we created a SQL Login ORACLE on MS SQL Server which has access to the MS SQL Server database AdventureWorks.

  2. Copy the client wallet (cwallet.sso) from C:\app\oracle\product\19.0.0\tghome_1\walletclient to OCI Object Store.

  3. Connect to Autonomous Database and let’s create credentials to access the Object Store where you copied the cwallet.sso file.

    • Refer to CREATE_CREDENTIAL for information about the username and password parameters for different object storage services.

    • Refer to Required Keys and OCIDs to get the required parameters information.

      BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL (
       credential_name => 'OCI_KEY_CRED',
       user_ocid       => 'ocid1.user.oc1..aaaa.....7igkq',
       tenancy_ocid    => 'ocid1.tenancy.oc1..aaaa....xo3da',
       private_key     => 'MIIEvgIBA.........S0EOOYN4ijczB',
       fingerprint     => 'f2:db:f9:18:a4:..:..:..:f4:f6:6c:39:96:16:aa:27');
END;
/

  4. Now we will create a directory on Autonomous Database for the wallet file cwallet.sso.

    CREATE DIRECTORY dblink_wallet_dir AS 'wallet_dir';

  5. We will use DBMS_CLOUD.GET_OBJECT to upload the target gateway self-signed wallet to the directory we created in the previous step, DBLINK_WALLET_DIR.

    BEGIN
  DBMS_CLOUD.GET_OBJECT(
    credential_name => 'OCI_KEY_CRED',
    object_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/<namespace>/b/<bucketname>/o/cwallet.sso',
    directory_name => 'dblink_wallet_dir');
END;

    To get the object_uri, refer to Cloud Object Storage URI Formats

  6. Create credentials to access the MS SQL Server database. The username and password you specify with DBMS_CLOUD.CREATE_CREDENTIAL are the credentials for the MS SQL Server database used within the database link. In this tutorial, we provided credentials for SQL Login ‘ORACLE’.

     BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'DB_LINK_CRED',
username => 'ORACLE',	--Ensure username is in UPPERCASE
password => 'USER_PASSWORD'
   );
 END;
 /

  7. Now create the database link on Autonomous Database to the Oracle Database Gateway for SQL Server using DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.

    BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name =>       'MSSQLLINKWW',
          hostname =>           'mssqlvm1',
          port =>               '2484',
          service_name =>       'dg4msql',
          ssl_server_cert_dn => 'CN=windows',
          credential_name =>    'DB_LINK_CRED',
          directory_name =>     'dblink_wallet_dir',
          gateway_link =>        TRUE,
          private_target =>      TRUE,
          gateway_params =>      NULL);
END;
/

  8. Now let’s query the MS SQL Server Database using the Oracle DB Link.

    SELECT * FROM HumanResources.Employee@MSSQLLINKWW;

Next Steps

In this tutorial, we learned how to configure Database Links from an Autonomous Database with Customer-Managed Heterogeneous Connectivity to Microsoft SQL Server (Non-Oracle Database) on a Private Endpoint. We explored both the options i.e., without a wallet (TLS) and with a wallet (TCPS).

Similar steps can be used for other Non-Oracle Databases, you need to ensure you have configured the Oracle Database Gateway appropriately.

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.