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