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:
Version | Encrypt | Trust Server Certificate | Server Force Encryption | Result |
|---|---|---|---|---|
| ODBC 17 & prior OLEDB 18 & prior | No | No | Yes | Server certificate isn't checked. Data sent between client and server is encrypted. |
| ODBC 18 OLEDB 19 | No | No | Yes | Server 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=;DATABASE=;
18: DRIVER=ODBC Driver 18 for SQL Server;SERVER=;DATABASE=;
OLEDB 18 vs. OLEDB 19 connection strings for Microsoft Access with SQL Server
18: Provider=;Data Source=;Initial Catalog=;
19: Provider=;Data Source=;Initial Catalog=
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:
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=Noand disableForce Encryptionon the server. NOT RECOMMENDED - Do not update drivers.
The steps to resolve the issue are detailed in the corresponding sections.
Resolutions
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.
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!
Hi Ben, tnx for replying.
Yes, it must be self-signed (it’s called ‘SERVER’.’DOMAIN’.local BTW). I’ve tried about every possible combination of keywords but the OLE 19 driver can’t seem to get a trusted connection. I wouldn’t mind purchasing a real certificate but it would have to work on Express with the latest drivers. There is an ERP-like critical database on it so it’s pretty important. The new ODBC 18 driver for attaching tables and such functions correctly.
I’m now experimenting with a .udl Data Link Properties file to generate the right connection string but so far it gives me the same errors.
Please note that while my SQL Config Manager listed the certificate, my server’s registry key (…\SuperSocketNetLib\) only has the ProtocolList key in it!
I assume you guys got the new drivers functioning?
Kant, I verified with a VM with a SQL Server Express instance and it does work. One thing to keep in mind is that express must be a named instance so simply specifying the machine name may not be enough. By default, it uses SQLEXPRESS as the instance name but that can be different. This is the minimum connection string that worked for me (tested with ODBC; OLEDB should be almost similar and should work as well). You can save this as a file DSN and adjust the instance name if needed:
Hi Ben, I hope I’m not bothering you to much but I can’t be the only one with this issue, right?
ODBC18 works fine here also, I notice you say “OLEDB should…” Have you actually got that part working?
This connection string works for me with OLE18:
sOLEDB = “Provider=MSOLEDBSQL;” _
& “Data Source=Alias;” _
& “Initial Catalog=DATABASE;” _
& “Trusted_Connection=Yes;” _
& “Encrypt=True;” _
& “TrustServerCertificate=Yes;” _
& “MARS_Connection=True;”
If I change Provider to MSOLEDBSQL19 I get an invalid connection string attribute error. MARS is the problem. If I remove that I get the SSL Provider certificate chain error. Using the instance name instead of an alias makes no difference, the error proves it tries to make a connection.
Thanks for writing back, Kant. Re-reading your earlier comment, I now realized I missed your previous remark that ODBC was working but not OLEDB. Sorry about that. I thought I had tested OLEDB but I realized that I was testing with a server that had a trusted certificate, so my test was not valid. Testing with a server using self-signed certificate, I can confirm that the keywords seem to be ignored. According to Microsoft’s documentation, either
TrustServerCertificate=yes;orTrust Server Certificate=trueought to have worked. However, the only way I was able to connect was to disable encryption. Here’s the minimum connection string:Persist Security Info=False;Provider=MSOLEDBSQL19;Integrated Security=SSPI;Data Source=localhost;Use Encryption for Data=Optional;I should also note that the keyword
Encryptdoes not seem to work even though it’s documented in the linked documentation page.This may be a bug with the driver, given that the documentation claim those should have worked. Will do some investigation and see what we can find. Thanks for pressing on this issue!
Hi Ben, tnx for hanging in there with me. In my case, your minimun connection string also fails with the certificate chain error.
For now I’m sticking with both 18 versions of the drivers, anxious to see what you can find out further.
Do you still have the server-side “Force Encryption” set? If so, then the snippet
Use Encryption for Data=Optional;wouldn’t help you because of the server requiring the encryption which in turn forces certificate validation, which we cannot bypass because theTrust Server Certificateis not working.I agree that it’s best to stick to the previous version for now. Will update if I hear anything further.
Hi Ben, I’ve been struggling a few days now to get it working between my on-site server 2012 with SQL Server Express 2012 and my Access app. Server is not directly accessible via Internet and in my SQL Config Manager I have a certificate called ..local. I never installed any certificate on the server myself, don’t know what this is.
When I activate it and force encryption (both were off until now), the previous drivers keep functioning, so all good. However, I can’t get MSOLEDBSQL19 to function and keep getting these not trusted errors, no matter what keyword combos I put in my connection string.
I’m starting to think it has to do with the Express version?
Hi, Kant!
The name makes me think it’s a self-signed certificate and therefore not a trusted certificate. To force encryption from the server’s side, you’d need to use the `TrustServerCertificate` keyword set to `Yes` to use the `.local` certificate. This should work even with an express edition. Are you say it does not work even if you add `TrustServerCertificate=Yes` to your connection string with the new drivers?
Thanks for providing this information. My hope is that in a future version Microsoft will make this process easier.
Can you explain the difference between the ODBC and OLEDB drivers? Why would I choose one over the other?
In short:
DAO uses ODBC while ADO uses OLEDB.
Whenever you use linked tables, pass-through queries, form’s
RecordSources or control’sRowSources, you are using DAO and therefore ODBC is your only choice.However, when you use VBA to work with ADO objects which may include form’s or control’s
Recordsetproperty, you are actually working with OLEDB. However, one common misconception is that because ADO allow you to use the same ODBC connection strings to open anADODB.Connectionobject, it seems that you can use ODBC over ADO but in fact, you’d be using the ODBC over OLEDB provider (aka MSDASQL), which means you now have additional layers of technology as well potential performance ramifications. To avoid those issues, it’s best to use OLEDB provider directly with ADO to get the most from ADO.Hope that helps!