Recently, Microsoft released two new drivers for SQL Server, a major upgrade:

ODBC 18 Driver for SQL Server
OLEDB 19 Driver for SQL Server

That’s great news! However, there is a major breaking changes that requires your attention. Specifically, they changed how the default settings work for the encryption. In all prior versions of drivers, the default was to not require encryption. We had the options of forcing encryption from the server side or requesting it within the connection string on the client side. Obviously, for server administrator, it was usually more desirable to force encryption from the server, so that it would not matter if some old application did not request for it but it would be guaranteed to encrypt its communication with the server.

There are 2 connection string keywords and a server setting that influences how the driver should behave:

Within the connection string from client side:

  • Encrypt: Indicates whether the communication should be encrypted.
  • TrustServerCertificate: Indicates whether the client should just trust the server’s certificate without checking the authenticity of the certificate.

Within settings from the server side:

  • Force Encryption: Mandates that any client connecting to the server to encrypt the communication regardless of the client’s connection string.

The combination of the 3 properties influences how the connection will be made. There is a handy chart enumerating them, which can be found here..

However, the most common scenario is that we force encryption from server and do not specify anything else in the connection string. Here’s the extracted version of both prior versions and new version behavior:


Trust Server
Server Force

ODBC 17 & prior
OLEDB 18 & prior
NoNoYesServer certificate isn't checked.
Data sent between client and server is encrypted.
NoNoYesServer certificate is checked.
Data sent between client and server is encrypted.

I think this is generally a good thing, especially with Azure SQL databases becoming more common, but the change of checking the SQL Server certificate does introduce a change, especially for servers that might not have certificates set up. By default, it will use a self-signed certificate, which is not as safe as one that is trusted. For those servers where connections are made over the internet, the extra precaution is worth the effort.

Here’s a comparison of the ODBC connection strings for Microsoft Access with SQL Server changes between the previous version and the now-current version:

ODBC 17 vs. ODBC 18

17: DRIVER=ODBC Driver 17 for SQL Server;SERVER=<server name>;DATABASE=<database name>;Encrypt=yes;
18: DRIVER=ODBC Driver 18 for SQL Server;SERVER=<server name>;DATABASE=<database name>;

OLEDB 18 vs. OLEDB 19 connection strings for Microsoft Access with SQL Server

18: Provider=MSOLEDBSQL;Data Source=<server name>;Initial Catalog=<database name>;Encrypt=yes;
19: Provider=MSOLEDBSQL19;Data Source=<server name>;Initial Catalog=<database name>

Note that in previous versions, you had to specify the Encrypt=yes but this is now implicit in the current versions.

Ok, but I have a on-premise server but it’s not working with the new drivers?

Because of the change in the setting, you may now see this error:
SQL Server login error

Depending on the scenario and requirements, here are possible resolutions:

  • Install & configure a trusted certificate on the server.
  • Modify the application’s connection string to include TrustServerCertificate=Yes. USE WITH CAUTION
  • Modify the application’s connection string to include Encrypt=No and disable Force Encryption on the server. NOT RECOMMENDED
  • Do not update drivers.

The steps to resolve the issue are detailed in the corresponding sections.


Install & configure a trusted certificate on the server

It’s very important to note that just because you have a server that has a valid SSL certificate set up and in active use does not actually mean that the SQL Server is using the same certificate. Furthermore, it turns out that the SQL Server Configuration Manager is horrid at handling the certificates. You might find that it won’t list any certificates for you to use:

The short version is that SQL Server Configuration Manager is excessively restrictive on what certificates it will list, which can be quite frustrating especially because this is a UI problem, not a true requirement by SQL Server itself. Fortunately, we can circumvent this silly UI limitation by editing the registry directly. This corresponds to the registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<name of your instance>\MSSQLServer\SuperSocketNetLib

Within that key, there is a value Certificate which expects a thumbprint of the certificate.
Value certificate

You could manually paste in the thumbprint of the SSL certificate to be used but I would recommend using a script to do this as we also need to make sure that the server account has the permission to access the certificate. I used this blog article as a guide for setting up the PowerShell script to select the certificate and load it into SQL Server’s registry key and restart the service. Depending on who provides your SSL certificate and the workflow, you may want to integrate this into some other scheduled tasks so that when the SSL certificate is renewed, the registry key & permissions will be updated accordingly.

If everything is set up correctly, then your server should be able to use the new drivers without any modifications to the application’s connection string. As an added verification, you can check your SQL Server’s error log and look for a line like this:

<timestamp> spid11s The certificate [Cert Hash(sha1) "<certificate thumbprint>"] was successfully loaded for encryption.

If the thumbprint matches the one that you want to use, then you know you have it loaded correctly and thus chain of trust is now established.

Modify the application’s connection string to include TrustServerCertificate=Yes

However, if your server is not facing the internet and it’s too painful to set up a SSL certificate, it may be acceptable to turn on TrustServerCertificate. This requires a change in your application’s connection string. This allows the application to allow connecting to a server without verifying the server’s certificate. If you are able to confidently control your application so that it will not go outside your network, this should be OK. Do keep in mind that if someone is able to spoof the server’s name or IP within the network, the client applications will be blindly connecting to that computer. For that reason, we cannot recommend this if there’s internet involved in the connection. We really would rather not take the risk.

Modify the application’s connection string to include Encrypt=No and disable Force Encryption on the server.

This is for those who like streaking on the internet with a giant neon sign “STEAL MY DATA! HIJACK ME NOW!” to every bad actors out there. This is erm, an “option”. The only thing I can say about this option is that it’s exceedingly bad. So bad, that I forgot how to do this. You’re on your own, buddy.

Do not update drivers.

A slightly better alternative compared to the previous is to simply not update and stick with ODBC 17 and OLEDB 18 drivers. However, this is at best a stopgap measure. This resolution requires no application changes but this only delays the inevitable changes at best. You can make use of the time to explore paths that will get you to the latest version and protect your data properly.

Hope that helps!