Enabling TLS security between Microsoft SQL Server and Folio Backend services

First published: 3 December 2014 (cf)

Information

Fabasoft Folio Backend services support TLS communication between COO Services and Microsoft SQL Server. TLS encryption can be enabled by the Fabasoft Backend services registry after a successful Fabasoft Folio installation.

All current Microsoft ODBC, OLE DB and SQL Server Native Client versions support at least TLS 1.2.

Using the “SQL Server” driver (the default driver used by Fabasoft Folio) uses TLS 1.0.

Solution

To use TLS, first install Fabasoft Folio Backend services without TLS support enabled. The required registry keys are created during the initial installation of Fabasoft Folio. To install Fabasoft Folio, make sure that Microsoft SQL Server allows unsecured connections for the initial Fabasoft Folio installation.

After the installation, edit the following registry hives, located in:

HKEY_LOCAL_MACHINE\SOFTWARE\Fabasoft\Fabasoft Components Server\Domain 1.4000\Service 1\Datasources\Default

DWORD “ConnectString”: Trusted_Connection=yes

DWORD “Driver”: SQL Server Native Client 11.0

The Service 1 hive represents the first COO Service. Repeat this for all COO Services hives.

The driver name “SQL Server Native Client 11.0” may change (e.g. with a higher SQL Server version). To check the driver name, open the Windows-App “ODBC Data Sources (64-bit)” and open the “Drivers” tab to check the installed drivers.

Troubleshooting

Checking what TLS connection is used

In the Links sections you find an SQL script “SQL Script to create SQL Extended trace for TLS monitoring” that creates an Extended event trigger in SQL Server to trace TLS connection information.

Output of the tracing:

You can start and stop this tracing in SQL Server Management Studio / <Your Server> / Management / Extended Events / TLS_monitoring. You also can remove this tracing by deleting the “TLS_monitoring” item.

Enabling TLS 1.2, disabling TLS 1.0 and TLS 1.1

As TLS 1.0 and TLS 1.1 are already deprecated and therefore should be disabled in Microsoft Windows, and TLS 1.2 or TLS 1.3 should be used. In the Links section the Registry Key to disable TLS 1.0 and TLS 1.1, and enable TLS 1.2 (ZIP file) download is an example of a registry key to disable TLS 1.0/1.1 and enable TLS 1.2. The download is a ZIP with the regfile export packaged.

"The certificate chain was issued by an authority that is not trusted" error after upgrading SNAC applications

When using the Microsoft ODBC Driver or OLE DB Driver for SQL Server from Microsoft, the Fabasoft Folio Server Management may return the following SQL Client error:

This is caused by ODBC Driver starting with version 18, and OLE DB Driver starting with version 19.

Solution 1: For ODBC, use the ODBC Driver for SQL Server 17.

Solution 2: For OLE DB, use the OLE DB Driver for SQL Server 18.x.

There might be additional solutions with added ConnectString parameters.
See Microsoft’s website for further information: “The certificate chain not trusted error” https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/certificate-chain-not-trusted?tabs=ole-db-driver-19

Links

Download ODBC Driver for SQL Server
https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16

SQL Script to create SQL Extended trace for TLS monitoring (ZIP file)
https://at.cloud.fabasoft.com/folio/public/1yk4g67x5ux553pi0231hladdl

Registry Key to disable TLS 1.0 and TLS 1.1, and enable TLS 1.2 (ZIP file)
https://at.cloud.fabasoft.com/folio/public/1b1783pl2e9eh2o2bq9sqtchp5

Troubleshooting The certificate chain not trusted error
https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/certificate-chain-not-trusted?tabs=ole-db-driver-19