Secure Data Connections

Starting version 17.32.160 LTS, 20.0 PROD and 17.33.270 BETA, Invantive SQL has changed defaults on connections to MySQL, PostgreSQL and SQL Server to enforce encryption on data transport using SSL. This article gives some tips on proper configuration of certificates. However, they are only suggestions. Consult your database administrator, security officer or IT provider for more in-depth knowledge and application to your local situation.

Starting 20.0.112 and 20.1.261 BETA, the defaults for SQL Server have been finetuned: default encryption is disabled when a SQL Server data container was registered from discovery and is either accessed using the pipe driver (Windows Internal database) or the data source/server name has no decimal dot (’.’). This simplifies first use, since little (if any) browseable local SQL Server instances exist with proper certificate configuration.

Typical SQL Server Error

After an upgrade you might run into an error with code itgenclr038 like:

provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.

Or

SSL Provider, error: 0 - The target principal name is incorrect.

Typical PostgreSQL Error

On PostgreSQL you might run into an error like:

Remote certificate validation failed

Typical MySQL Error

On MySQL you might run into an error like:

The host SOMETHING does not support SSL connections.

Approach

The recommended steps for such issues are:

  • Configure proper transport encryption.
  • Check with your security officer whether unencrypted and unsecured data transport between your Invantive SQL node and database is allowed.
  • If so, disable the now default encryption.

Configure Transport Encryption

Azure

On Azure, Microsoft by default deploys the necessary certificates on the server which are by default accepted on Microsoft Windows.

Please make sure you use the right host name, such as myname.database.windows.net.

Amazon

On Amazon, you may need to load the certificate chain on your client. Follow the AWS instructions, which typically are:

Self-managed Server

For on-premise and self-managed servers, please consult your local administrator.

Disable Default Encryption

These steps describe how to disable default encryption by changing the connection string.

The location of the connection string varies, but in most deployment scenarios it can be found in settings*.xml files. These are typically located in the %USERPROFILE%\invantive folder; consult the knowledge base article 7208961 for more information.

Within the settings*.xml files the connection string can occur as part of a data container definition such as:

<database
 ...
 connectionString="Server=dbhost.com;Initial Catalog=database;Persist Security Info=False;User ID=user;Password=secret;Connection Timeout=30;" 

And in attributes such as the connection string used for Invantive Data Replicator:

dataCacheConnectionString="provider=SqlServer;connectionString={Server=dbhost.com;Database=database;User Id=user;Password=secret}"

The Invantive documentation contains more background information on setting up the settings*.xml files. Alternatively, your local database administrator may be able to assist you.

SQL Server Change

As a first step, you may want to keep using encryption, but allowing for some attacks by changing your connection string to include:

hostNameInCertificate=*.domain.name

Alternatively, you may want to keep using encryption, but allowing for more attacks like instance man-in-the-middle attacks by changing your connection string to include:

TrustServerCertificate=True

As a last resort, fully disable encryption using:

Encrypt=false

SQL Server Sample

For example, fully disabling the SQL Server encryption in the connection string as part of a data container definition given above leads to:

<database
 ...
 connectionString="Encrypt=false;Server=dbhost.com;Initial Catalog=...original..." 

And for SQL Server used to store the Invantive Data Replicator repository given above:

dataCacheConnectionString="provider=SqlServer;connectionString={Encrypt=false;Server=...original...}"

PostgreSQL

As a first step, you may want to keep using encryption, but allowing for more attacks like instance man-in-the-middle attacks by changing your connection string to include:

Trust Server Certificate=True

As a last resort, allow disabling encryption using:

SslMode=Prefer

MySQL

As a first step, you may want to keep using encryption, but allowing for more attacks like instance man-in-the-middle attacks by changing your connection string to include:

SslMode=VerifyCA

As a last resort, allow disabling encryption using:

SslMode=Preferred