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 with SQL Server. 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.

Typical SQL Server Error

After an upgrade you might run into an error like:

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

Or

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

Typical PostgreSQL Error

On PostgreSQL you might run into an error like:

Remote certificate validation failed
CODE

Typical MySQL Error

On MySQL you might run into an error like:

The host SOMETHING does not support SSL connections.
CODE

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;" 
XML

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}"
XML

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
CODE

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
CODE

As a last resort, fully disable encryption using:

Encrypt=false
CODE

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..." 
XML

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

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

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
CODE

As a last resort, allow disabling encryption using:

SslMode=Prefer
CODE

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
CODE

As a last resort, allow disabling encryption using:

SslMode=Preferred
CODE