How to change protocol for Oracle database connection in ASP.NET Core?

Question

I have an ASP.NET Core app which connects to an Oracle databases using nuget package Oracle.ManagedDataAccess.Core v2.19.80 and Dapper. Depending on the request data, there maybe a need to connect to a different database each time.

The connection happens string is created like this:

public DatabaseConnector(IConfiguration configuration, IDbConnectionFactory connectionFactory, ISyncPolicy policy)
{
    var packageSettings = configuration.GetPackageConfiguration();

    var connectionString = new OracleConnectionStringBuilder
        {
            DataSource = packageSettings.DataSource,
            UserID = packageSettings.Username,
            Password = packageSettings.Password,
            ConnectionTimeout = 5,
        };

    _dbConnection = connectionFactory.CreateConnection(connectionString.ConnectionString);
    _policy = policy;
}

When running the following query

SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') AS network_protocol 
FROM dual

the response is tcp. How can I configure the connection or the connection string, or what can I do inorder to change the protocol from tcp to tcps.

Based on the documentation here, those are the 2 supported types. I want to ensure my connection is secure (SSL/TLS 1.2).

So far, I have read, the following documentations, but have not managed to achieve the goal.

Documentations read:

• Ora file parameters
• Developer's guide for Microsoft Windows

I have also tried by adding this:

OracleConfiguration.OracleDataSources.Add("test", "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname or IP>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<service name>)(SERVER=dedicated)))");

Queries are executed using this command

_dbConnection.Query<string>(databaseQuery, queryParameters);

I have also looked at property TnsAdmin but am not sure how can I use it which is exposed when building a query string using the OracleConnectionStringBuilder class. It looks like is a path to a ora file, but I do not posses such file.

Answer 1

There are two ways of encrypting SQLNET traffic.

1. Using Wallets/Certificates and network layer (SSL/TLS)
2. Using native Oracle encryption which doesn’t encrypt the network layer, but does encrypt each packet sent along it

In my case, we have chosen to go with option 2, as long as you see the ASE256 (which is what the DB server side is requesting) on your response to the query select NETWORK_SERVICE_BANNER from v$session_connect_info where SID = sys_context('USERENV','SID') then I would say you good.

You could explicitly configure your connection as is, but in my case I didn't have to, as the oracle server has been configured to require it by default.

OracleConfiguration.SqlNetEncryptionClient = "required";
OracleConfiguration.SqlNetCryptoChecksumClient = "required";

Sources:

1. Database security - video Explanation at 34:00
2. Oracle Network Encryption

Answer 2

Since version 10g Release 2 onward, Native Network Encryption and TCP/IP with SSL/TLS are no longer part of the Advanced Security Option, therefore you can configure all the elements in the database server and in the client to establish secure connections by TCPS.

Basically you need to use the orapki utility in both server and client, and reconfigure the listener.ora and the sqlnet.ora files in the server side.

In your case, you would need to reconfigure the client elements in the Oracle Client which comes with the ODAC components for Windows.

You must meet the following prerequisites:

• A functioning database server 12.2 or higher (recommendable)
• A client machine with an Oracle Client installed ( 12.1 or higher recommendable )
• There are no local or network firewalls blocking communication between the server and the client in port you want to use for TCPS.
• You can use self-signed certificates for the wallet, or you can use your own company trusted certificates.

Create Server Wallet with auto-login

$ mkdir -p /your_wallet_directory

$ orapki wallet create -wallet "/your_wallet_directory" -pwd yourpassword -auto_login_local
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Create a self-signed certificate and load it into the wallet

$ orapki wallet add -wallet "/your_wallet_directory" -pwd yourpassword \
  -dn "CN=`hostname`" -keysize 1024 -self_signed -validity 3650
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Export the certificate, so we can load it into the client wallet later

$ orapki wallet export -wallet "/your_wallet_directory" -pwd yourpassword \
   -dn "CN=`hostname`" -cert /tmp/`hostname`_certificate.crt

Client Wallet and Certificate

In your client machine perform the following actions

$ mkdir -p /my_client_wallet 
$ orapki wallet create -wallet "/my_client_wallet" -pwd myclientpassword -auto_login_local

Create a self-signed certificate and load it into the wallet in the client

$ orapki wallet add -wallet "/my_client_wallet" -pwd myclientpassword -dn "CN=`hostname`" -keysize 1024 -self_signed -validity 3650

Export the certificate in the client so we can load it into the server later

$ orapki wallet export -wallet "/my_client_wallet" -pwd myclientpassword -dn "CN=`hostname`" -cert /tmp/clientcertificate.crt 

Exchange Certificates

Each side of the connection needs to trust the other, so we must load the certificate from the server as a trusted certificate into the client wallet and vice versa. Transfer the export certificates done in each side to the opposite and import it using

In client

orapki wallet add -wallet "/my_client_wallet" -pwd yourclientpassword -trusted_cert -cert /serverhostname_certificate.crt

In Server

orapki wallet add -wallet "/your_wallet_directory" -pwd yourwalletpassword
-trusted_cert -cert /tmp/myclienthost-certificate.crt

Once we have the wallets ready and the certificates in both sides of the connection, we can configure the sqlnet.ora file in $ORACLE_HOME/network/admin

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /your_wallet_server_directory)
     )
   )

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

You probably need to think about what cipher suites you want to support. You may wish to avoid those that support SSLv3 in favour of those that support TLS only. Your decision my vary depending on the Oracle database and client versions.

Finally, configure the listener to accept SSL/TLS encrypted connections. Edit the $ORACLE_HOME/network/admin/listener.ora file, adding the wallet information, as well as the TCPS entry.

Be aware that I use 1521 for standard connections and 2484 for TCPS connections

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /your_wallet_directory)
    )
  )

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

ADR_BASE_LISTENER = /your_adr_path