Support for ODBC Driver 18 for SQL Server
Introduction
As of DataFlex 23.0, ODBC Driver 18 for SQL Server has been a supported version of the ODBC driver to access Microsoft SQL Server 2022 (and higher).
The DataFlex SQL Server Driver (MSSQLDRV) 23.0 will now recognize and support ODBC Driver 18 for SQL Server. However, ODBC Driver 18 for SQL Server has a breaking change, where the default value of the "Encrypt" connection option changed from optional/no to mandatory/yes.
Potential Issue
When migrating from ODBC Driver 17 for SQL Server or earlier, the changed default encryption may result in the following error:
[Microsoft][ODBC Driver 18 for SQL Server] SSL Provider: The certificate chain was issued by an authority that is not trusted.
Use the SQL Connection Manager to correct your workspace's encryption and certificate settings.
Difference in Connection String
The connection string and log in command will need to be adjusted to use ODBC Driver 18 for SQL Server.
To log in to an SQL Server database using a trusted connection and ODBC Driver 17 for SQL Server (and earlier), the following was acceptable:
Login "SERVER=MyServerName;DATABASE=MyDatabaseName;Trusted_Connection=yes" "" "" "MSSQLDRV"
To do the same but in an environment using a trusted connection and ODBC Driver 18 for SQL Server (and later), that command will need to be adjusted to use the following:
Login "SERVER=MyServerName;DATABASE=MyDatabaseName;Encrypt=Optional;TrustServerCertificate=no;Trusted_Connection=yes" "" "" "MSSQLDRV"
Difference in Connection IDs
With ODBC Dtiver 18, the Encrypt parameter needs different values than yes (encrypted) or no (not encrypted) as it used to be up to ODBC Driver 17.
To experiemnt and identify which settings to use with the new ODBC Driver 18 SQL Server in your environment, we suggest creating a Managed Connection using the Studio, and selecting the settings you would like to use. The workspace's DFConnId.ini will reflect the different options.
For example, to set encryption optional and not use a server certificate, the INI settings would look something like this:
[connection1]
id=MySQLServer_ID
driver=MSSQLDRV
connection=SERVER=MyServerName;DATABASE=MyDatabaseName;Encrypt=Optional;TrustServerCertificate=no
trusted_connection=yes
After installing ODBC Driver 18, you may want to keep your original connection settings for the pre-18 driver while experimenting with the 18 driver settings. For that, you may configure a second connection while preserving the original one. The example below shows a single connections INI file (DFConnId.ini) containing two connections to an SQL Server database, one using ODBC Driver 17 (connection 1, disabled) and the other using ODBC Driver 18 for SQL Server (connection 2) - note the difference in the setting for the Encrypt keyword:
[connection1]
id=SupportDBid
driver=MSSQLDRV
connection=SERVER=.\SQLEXPRESS;DATABASE=SameDB;Encrypt=No;TrustServerCertificate=yes
trusted_connection=yes
disabled=yes
[connection2]
id=SQLv18ID
driver=MSSQLDRV
connection=SERVER=.\SQLEXPRESS;DATABASE=SameDB;Encrypt=Optional;TrustServerCertificate=yes
trusted_connection=yes
This shows a way to test an application when moving from using ODBC Driver 17 to ODBC Driver 18.