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 fine-tuned: 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 or itgenclr121 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
or an itgenclr072 with text such as:
AuthenticationException
Exception while performing SSL handshake.
The remote certificate was rejected by the provided RemoteCertificateValidationCallback.
or an itgenclr139 error like:
SSL connection requested. No SSL enabled connection from this host is configured.
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
The transport encryption must be configured on the client establishing the connection. With on-premises products, this is the server or device used to run the Invantive product. Invantive online products provide varying amounts of configuration possibilities, but loading custom or non-standard certificates is typically not possible.
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:
- Download the AWS public key from https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem.
- Convert that to a certificate using https://www.sslshopper.com/ssl-converter.html (convert to PKCS#7/P7B).
- Import p7b file into certificate store.
Self-managed Server
For on-premise and self-managed servers, please consult your local administrator.
Disable Default Encryption
These steps describe how to disable a request for encryption initiated by the client software by changing the connection string. Both database server and client must agree upon encryption, so it is not always possible to provide a client-only solution.
The location of the connection string varies, but in most deployment scenarios it can be found in settings*.xml files (format description). 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