MSOLEDBSQL19 driver seems to ignore TrustServerCertificate=yes if SQL Server Force Encryption is yes

Question

MSOLEDBSQL19 driver seems to ignore TrustServerCertificate=yes if SQL Server Force Encryption is yes

Mitch Gershonowitz 25

Microsoft SQL Server 2019 with GDR 2101 (KB5021125) is installed on a Windows Server 2019 computer.
The SQL Server has a self-signed certificate and is configured with Force Encryption = yes.
A Windows Server 2012 R2 computer has Microsoft OLEDB Driver 19 for SQL Server installed (v19.3.1.0).
An application is running on this server that uses OLE DB to reach the SQL Server. The connection string is:

Provider=MSOLEDBSQL19;Server=STATENISLAND;Database=CATALOG;Trusted_Connection=yes;TrustServerCertificate=yes;Use Encryption for Data=Optional;

The connection fails with: "SSL Provider: The certificate chain was issued by an authority that is not trusted."

Yes, that is true, but I am specifying "TrustServerCertificate=yes;" in the connection string. It does connect OK if Force Encryption = no on the SQL Server but gives this certificate chain error if Force Encryption = yes.

Why is the driver ignoring the "TrustServerCertificate=yes" connection string parameter?

Note: On the W 2012R2 server, registry value: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SNI19.0\GeneralFlags\Flag2 = 0x1.

Thank you.

Accepted answer

1 additional answer

Your answer

Answer 1

Bernd 80

I also think that the "Trust server certificate" (SSPROP_INIT_TRUST_SERVER_CERTIFICATE) property in "Microsoft OLE DB Driver 19 for SQL Server" does not work:

It is impossible to open an encrypted connection to a sql server with this driver without using a server certificate. Thus this driver is less secure than the previous versions 18.x for this case.

You can easily verify this problem by trying to connect to a sql server without a certificate using an udl-connection:

With encryption set to "Mandatory" the connection will always fail, although "Trust server certificate" is set to "True" and the server does not enforce encryption.

The documenation in this point is wrong:

https://learn.microsoft.com/en-us/sql/connect/oledb/help-topics/data-link-pages?view=sql-server-ver16

Mitch Gershonowitz 25 Reputation points

2023-07-04T12:46:01.4966667+00:00

Bernd, thank you. Your suggestion about testing with a UDL set me on the right track. Once I got the connection to work using a UDL, I found that the corresponding connection string that worked was:

Provider=MSOLEDBSQL19;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CATALOG;Data Source=STATENISLAND;Use Encryption for Data=Optional;Trust Server Certificate=True;

I'm not sure why this connection string works and the one I tried earlier doesn't. Something is definitely odd about this driver. There were a lot of breaking changes they made to v19, including adding a version number to the Provider name, which broke all existing code if you don't install the earlier drivers as well. Not sure what Microsoft were thinking here.
Bernd 80 Reputation points

2023-07-04T12:59:35.86+00:00

Hi @Mitch Gershonowitz ,

you are right that this connection string works, but it deactivates the encryption ("Optional") if the server does not enforce it. I tried to use "Mandatory" for the Encryption and "true" for "Trust Server Certificate" and unfortunately this won't work. In the driver version 18.x this was still possible.

I know that the connection isn't completely secure without checking the server certificate, but having no encryption at all is much worse.
Mitch Gershonowitz 25 Reputation points

2023-07-04T13:35:23.6766667+00:00

The SQL Server has "Force encryption" set to Yes. So won't it be encrypted no matter what with this connection string?
Bernd 80 Reputation points

2023-07-04T13:41:17.44+00:00

The documentation says "Yes", but I have not checked this. You can use Wireshark and check the TCP packets if you want to be sure.
Erland Sommarskog 121.7K Reputation points MVP Volunteer Moderator

2023-07-04T14:08:37.57+00:00

I've been eavesdropping on this thread in silence, since I don't really have had a good answer to share. I seem to recognise the problem myself, but it was about a year ago that I worked with, so my memory is a little foggy. Also, my scenario is different from yours: I'm maintaining a general API for Perl programmers, so I want to expose all options, as opposed to getting something to work in a specific environment. In any case, I have a recollection of that the documentation did not really match the behaviour that I saw.

It is worth pointing out that if you want a secure connection, you should stay away from TrustServerCertificate. Instead the server certificate should be installed in the client certificate store as a trusted certificate. With TrustServerCertificate you are saying "I don't mind a man-in-the-middle attack".

Note also that MSOLEDBSQL19 exposes the connection string option HostNameInCertificate. This can be useful if the certificate was created for the server MYSERVER, but you need to connect to it by IP address or FQDN.

There were a lot of breaking changes they made to v19, including adding a version number to the Provider name,

Well, if you make breaking changes, you better change the provider name, so this don't break just because you install a point release. And it is nothing new. We've had SQLOLEDB, SQLNCLI, SQLNCLI10, SQLNCLI11, MSOLEDBSQL and now MSOLEDBSQL19. And from my, admittedly quite odd, position as an API implementor, I kind of prefer when they make a new provider rather than a point release. To wit, there is not really any good way for me to tell if the version is 18.2 or 18.6, so if the caller tries to use a feature only available in 18.6 but only 18.2 is installed, the error message will not be pretty. But if the feature is only available in MSOLEDBSQL19, I can give a clear error message, since I know which provider that is in use.
Bernd 80 Reputation points

2023-07-04T14:33:04.66+00:00

Thanks for your additions @Erland Sommarskog . I agree with you that the assignment of a new provider name was correct in this case and that the server certificate should always be checked whenever possible.

In my opinion, the problem with the new driver version 19 is that without a server certificate it is impossible to activate encryption on the client side. The combination "Encryption=Mandatory" and "Trust Server Certificate=True" does not work as documented.
Mitch Gershonowitz 25 Reputation points

2023-07-04T20:08:35.9933333+00:00

Thank you Bernd and Erland for the detailed analyses. The solution I needed was for an internal test system, so my goal was to get it to work with SQL SErver "Force Encryption" on, but without a CA certificate, and to platform test the latest Microsoft OLEDB driver for SQL Server (v19). Because this is just for testing, I'm not worried about MITM attacks. Before I found a solution, I did install the self-signed certificate in the client's Trusted Root certificate store, and that worked fine, but I wanted to see if I could get it to work without installing the certificate, and the weird behavior of TrustServerCertificate=yes threw me.

The software I test is all on-prem behind firewalls, so some of our customers are not really worried that much about MITM attacks, or aren't willing to get a CA certificate, but they do want encryption to satisfy their corporate IT requirements. A security engineer would have an issue with my solution, I suppose.

The whole problem came about because our application had been hard-coding the MSOLEDBSQL provider for years and then all of us sudden, the application broke when I installed MSOLEDBSQL v19 and uninstalled v18. Since we don't know what OLEDB driver our customers will install, we enhanced our software to accept a connection string if the MSOLEDBSQL provider is not installed. My job was to figure out what connections string would work with a SQL Server that had Force Encryption on and a self-signed certificate. My problem is now solved, though I agree, it's not totally secure.

Thanks again for your comments.
Erland Sommarskog 121.7K Reputation points MVP Volunteer Moderator

2023-07-04T20:15:23.82+00:00

Even if you are on-prem, you may still have reason to be concerned about MITM attacks. A network engineer which is not what he or she is supposed to be could install a component on the network and siphoon/inject data into the stream.

A self-signed certificate in the trusted-certificate store might be good enough in most cases, I think.
Mitch Gershonowitz 25 Reputation points

2023-07-04T20:27:44.9633333+00:00

I totally agree. Our corporate security department always reminds us about exactly what you said, but they don't give us the ability to get CA certificates, and the same issue exists with our customers! In any event, it's more about what our customers want and the relationship between their application engineers and their corporate security. We know we can get it to work the secure way and the somewhat less secure way. It's up to the customer to decide their level of desired security.

As another example, the application I test has its own proprietary network layer. One prominent customer insisted we encrypt our in-transit client-server communication, and we provided it, but they happily used a self-signed certificate for the server. I don't get it, but it's their call.

Answer 2

Anonymous

Hi @Mitch Gershonowitz

I think maybe your string is set wrong.

According to the official documentation, "Add ;TrustServerCertificate=true to the connection string. This will force the client to trust the certificate without validation."

For more details, you can refer to this documentation: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/certificate-chain-not-trusted?tabs=ole-db-driver-19.

If the answer is helpful, please click Accept Answer and Up-Vote for the same. If you have any questions, please feel free to let me know.

Best regards,

Aniya

Mitch Gershonowitz 25 Reputation points

2023-06-30T11:08:18.1+00:00

Thank you for responding, Aniya. I tried TrustServerCertificate=true (instead of yes) in the connection string, and that did not work either. The only way it will work is if I set Force Encryption = no on the SQL Server configuration, or if I install the certificate on the client. I don't want to do either. I think this must be a bug in the MSOLEDBSQL19 driver.
Anonymous

2023-07-04T06:35:56.6766667+00:00

Hi @Mitch Gershonowitz

You can try selecting the "Trust Server Certificate" option in the connection properties tab of SSMS.

You can refer to this link: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/error-message-when-you-connect.

Best regards,

Aniya
Mitch Gershonowitz 25 Reputation points

2023-07-04T12:40:03.3733333+00:00

Aniya, I am not using SSMS. As I mentioned above, I have an application that requires a connection string. I am trying to figure out the correct connection string.
Basim Kadhim 31 Reputation points

2024-02-02T01:04:32.5133333+00:00

I previously overlooked the last sentence of original posters comment about the registry setting that you have to ensure is set according to this article: https://learn.microsoft.com/en-us/sql/connect/oledb/features/registry-settings?view=sql-server-ver16#trust-server-certificate By default, the setting is 0, which causes the TrustServerCertificate connection string property to be ignored. Once I flipped this registry setting, my connection started working.
Mitch Gershonowitz 25 Reputation points

2024-02-02T12:55:28.9766667+00:00

Basim, On the application server, this registry value is set as follows: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\SNI19.0\GeneralFlags\Flag2\Value = 0x1. With this setting, and using the connection string I posted above, I am getting the certificate chain error. Are you suggesting I should set this value to 0x0 to get it to work?
Basim Kadhim 31 Reputation points

2024-02-02T16:20:16.38+00:00

No. 0x1 was the correct value in my case. The default value was set to 0x0 when things weren't working for me. In my case, I'm working with a 64-bit application, so the registry key I'm setting omits Wow6432Node from the path that you mentioned. Sorry my post isn't really much help to you - it was just an observation on my part that things started working when I changed that registry setting.

Share via

MSOLEDBSQL19 driver seems to ignore TrustServerCertificate=yes if SQL Server Force Encryption is yes

1 additional answer

Your answer