Handling SQL Server SSL issues in Delphi applications using FireDAC

A practical approach to troubleshooting database connection security in Delphi with FireDAC

Recently, I had the opportunity to examine the behavior of FireDAC in Delphi 12 with respect to the use of ODBC drivers for SQL Server during the initialization of a database connection. FireDAC is the universal data access library developed by Embarcadero.

In this article, I describe how to address errors in the “Provider SSL error” family that may occur in a Delphi application connecting to SQL Server, as well as the changes introduced in FireDAC with Delphi 12 regarding ODBC driver handling.

A typical error message in this scenario is, for example:

[FireDAC][Phys][ODBC][Microsoft][ODBC Driver 18 for SQL Server] 
SSL Provider: The certificate chain was issued by an authority that is not trusted.

Let’s consider a Delphi application that connects to SQL Server through FireDAC using the ODBC driver installed on the machine. The connection string does not explicitly specify which ODBC driver version should be used. On the system where the Delphi application is running, multiple ODBC drivers may be installed, including:

• ODBC Driver version 18 (the most recent)
• ODBC Driver version 17
• Older versions of the ODBC drivers
• The ODBC driver installed with the operating system, kept for compatibility

In my case, the development environment used to compile the Delphi application has been upgraded from Delphi 10.x to Delphi 12.x. This upgrade also affected FireDAC, the data access library used by the application to connect to databases.

In Delphi 10, FireDAC supports the following ODBC drivers:

• ODBC Driver version 17
• Older versions of the ODBC drivers
• The ODBC driver installed with the operating system

In Delphi 12, FireDAC also supports (i.e., uses) ODBC Driver version 18, if it is present on the machine where the application is running. With the current database connection configuration in the Delphi application, FireDAC automatically searches for the available drivers and uses them in the following order:

• ODBC Driver version 18
• ODBC Driver version 17
• Older versions of the ODBC drivers
• The ODBC driver installed with the operating system

ODBC Driver 18 for SQL Server introduces a significant security-related change. In previous versions (up to and including version 17), encryption of the communication channel was disabled by default. Starting with version 18, however, encryption is enabled by default. More information are available here and here for FireDAC.

The error occurs in connections where the connection string does not explicitly specify which ODBC driver version to use, leaving FireDAC and the operating system to automatically detect and adopt the most recent available driver.

To address this error, I have identified three possible approaches.

1. Disabling the communication channel encryption

The application can be modified so that the connection string includes the option to disable encryption and accept SQL Server’s self-signed certificate. In Delphi, using FireDAC, this can be achieved by adding the following parameters to TFDConnection.Params:

Encrypt=No;
TrustServerCertificate=Yes;

Please, consider that TrustServerCertificate is part of the ODBCAdvanced parameter.

2. Allowing selection of the ODBC driver to use

The application can be modified to provide, via the graphical interface or configuration settings, the ability to explicitly select the ODBC driver version. This prevents FireDAC and the operating system from automatically adopting the most recent driver (for example, version 18).

If you do not want or if you can’t immediately modify the configuration on all environments where the application is installed, the default setting can remain as not using encryption on the communication channel, while still allowing it to be enabled in the future when servers are configured with valid certificates or updated ODBC driver versions.

3. Adapting the application to use encryption (recommended approach)

The most robust solution is to update the application to fully support the encryption enabled by default in ODBC Driver 18.

In practice, this involves:

• Installing a valid certificate on the server (issued by a trusted CA)
• Configuring SQL Server to use it
• Updating the connection string in Delphi/FireDAC with the following parameters to TFDConnection.Params:

Encrypt=Yes;
TrustServerCertificate=No;

This approach eliminates reliance on self-signed certificates and makes the application more secure and ready for future system updates.

Conclusion

If, during the execution of a Delphi application using FireDAC and ODBC drivers, an error in the “Provider SSL error” family is encountered, the cause may be related to the security parameters of the connection. You can investigate in this direction.