Encrypted SQL Server Database Connections

Since the release of VertiGIS Studio Workflow 5.42, database connections are now encrypted by default.

This can lead to breaking changes depending on how the server is configured.

Symptom

After upgrading to Workflow 5.42 or later, when running a Server Workflow that uses an activity that creates a database connection (Run SQL Non-Query, Run SQL Query or Run SQL Query Unsafe) the workflow now fails.

The logs contain an error message near the top such as:

• System.ComponentModel.Win32Exception (0x80090325): The certificate chain was issued by an authority that is not trusted.
• System.ComponentModel.Win32Exception (0x80090322): The target principal name is incorrect.

Resolution

There are three options for resolving this issue.

1. Use a certificate from a trusted authority.
   • This is the recommended course of action as it is the most secure.
   • Once the change is performed on the server, all affected workflows will start working again.
   • Refer to Microsoft's documentation for how to install and configure a secure certificate for SQL Server.
2. Manually trust the server's certificate.
   • This is done by adding the TrustServerCertificate=True parameter to the connection string.
   • If the workflow is using the Connection Config Name input:
     1. Update the connection string defined in the databasesettings.json file on the server.
     2. Recycle the Application Pool.
     3. All workflows using that connection configuration will start working again.
   • If the workflow is using the Connection String input:
     1. Update the connection string in the workflow itself.
     2. Repeat for all affected workflows.
3. Disable encrypted connections.
   • This produces the equivalent behaviour from before the upgrade, but is also the least secure of the options.
   • This is done by adding the Encrypt=False parameter to the connection string.
   • If the workflow is using the Connection Config Name input:
     1. Update the connection string defined in the databasesettings.json file on the server.
     2. Recycle the Application Pool.
     3. All workflows using that connection configuration will start working again.
   • If the workflow is using the Connection String input:
     1. Update the connection string in the workflow itself.
     2. Repeat for all affected workflows.

Cause

The 5.42 release of Workflow included a migration from the deprecated System.Data.SqlClient library to Microsoft.Data.SqlClient.

This new library had a breaking change that changed the default value of the Encrypt connection string parameter from false to true, meaning that if this parameter is absent it will now try to form an encrypted connection where in the past it would not. Microsoft's motivation for this change of the default behaviour was due to the increased use of cloud computing, where data may be more vulnerable to interception by a third party.