SSL / certificate validation error in spite of TrustServerCertificate=true in connection string

Question

At first, please note that I am aware that this question has already been asked several times. However, the accepted (and non-accepted) solutions given so far did not work in my case, so something substantial must have changed since then, which hopefully justifies asking again.

Having said this:

I am currently trying to upgrade an Access 2010 .adp application to Access 2019 .accdb. The application includes a lot of VBA code which uses ADO objects to connect with and operate on Microsoft SQL server (currently: 2008 R2, but will be upgraded soon).

I'd like to keep the most part of the code, which means to stick with ADO, so the way to go is the new OleDB SQL server driver (which has been undeprecated / newly released in 2018). The SQL server runs on another machine than my client application.

I am not able to establish a connection to SQL server from VBA. When executing the following code snippet

Dim cnTemp As Connection
Set cnTemp = New Connection
cnTemp.CursorLocation = adUseServer
cntemp.Open "Provider=MSOLEDBSQL;Server=dbserver.example.com;Initial Catalog=MyDB;Authentication=SqlPassword;User ID=sa;Password=secret;DataTypeCompatibility=80;"

I get the following error when the last line is executed:

SSL Provider: The certificate chain was issued by an authority which is not trusted.

OK, no problem, after all we have found all the other questions dealing with the same issue, all suggesting the same solution: Add Trust Server Certificate=True; to the connection string.

Well, tried that, but -to my surprise- still the same situation. Then I tried some other variants like TrustServerCertificate=True; or using true instead of True, but to no avail. I also tried adding Use Encryption for Data=True; which didn't help either (that could be expected). Furthermore, I tried some of the snippets I had found when researching the problem, but which are not documented by Microsoft as being valid in ADO connection strings (like Encrypt=true or Trusted_Connection=true;); of course, that made the situation worse, raising other error messages.

I have understood that I could solve that problem by putting the SQL server certificate into the client's trusted root certificate store, or by having SQL server use a certificate which has been issued by a known, trusted CA (e.g. Let's Encrypt).

However, I'd strongly like to know why adding Trust Server Certificate=true; to my connection string does not make the error go away and what I have to put in there to disable certificate validation (and by the way, I would be grateful if we wouldn't start a discussion about why this would be bad; this is just development and testing in a trusted, closed network, and I am aware of possible risks).

Answer 1

At first, I'd like to state that all credit goes to @Dan Guzman. It's his answer / comment which provided the solution.

However, I'd like to add some background, based on research I've done since posting my question.

The problem is that Microsoft's documentation obviously is wrong. Please have a look at the following document:

https://learn.microsoft.com/en-us/sql/connect/oledb/applications/using-connection-string-keywords-with-oledb-driver-for-sql-server?view=sql-server-2017#table3_1

It is located in the section SQL Server 2017 -> OLE DB -> Applications -> Using connection string keywords with OLE DB Driver for SQL server, so it should be the right one. It is divided into three sections; in the context of this question, the last table is what we're interested in, because only this one relates to connection strings with ADO.

That last table explicitly shows that Authentication=SqlPassword is valid in ADO / OLE DB connection strings (reformatting mine, no content altered):

Authentication SSPROP_AUTH_MODE Specifies the SQL or Active Directory authentication used. Valid values are:

(not set): Authentication mode determined by other keywords. ActiveDirectoryPassword: Active Directory authentication using login ID and password.

ActiveDirectoryIntegrated: Integrated authentication to Active Directory using the currently logged-in user's Windows account credentials.

NOTE: It's recommended that applications using Integrated Security (or Trusted_Connection) authentication keywords or their corresponding properties set the value of the Authentication keyword (or its corresponding property) to ActiveDirectoryIntegrated to enable new encryption and certificate validation behavior.

SqlPassword: Authentication using login ID and password.

NOTE: It's recommended that applications using SQL Server authentication set the value of the Authentication keyword (or its corresponding property) to SqlPassword to enable new encryption and certificate validation behavior.

It also says (again, formatting mine, no content altered):

Trust Server Certificate SSPROP_INIT_TRUST_SERVER_CERTIFICATE Accepts the strings "true" and "false" as values. The default value is "false", which means that the server certificate will be validated.

Every reasonable human being will understand this in the sense that Trust Server Certificate=true will disable certificate validation.

But when you look here

https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client?view=sql-server-2017

you'll notice that this document is structured like the first one, and that the last table does not mention the Authentication parameter.

However, this document is located in SQL Server 2017 -> Development -> SQL Server Native Client -> Applications -> Using Connection String Keywords. That means that it is not relevant for our case because it relates to SQL server native client (and not OLE DB), but it provides the correct information.

So we have the right document which provides the wrong information and an irrelevant document which provides the right information. Congratulations, Microsoft, you have made me waste a whole day again ...

Furthermore, I have found the following document:

https://learn.microsoft.com/en-us/sql/connect/oledb/features/using-azure-active-directory?view=sql-server-2017#encryption-and-certificate-validation

Reading the title ("Using Azure Active Directory"), it should relate to Azure only. However, I suspect that the following section relates to local SQL server installations as well (formatting mine, no content altered):

Certificate validation

To improve security, the new connection properties/keywords respect the TrustServerCertificate setting (and its corresponding connection string keywords/properties) independently of the client encryption setting. As a result, server certificate is validated by default.

Note

Certificate validation can also be controlled through the Value field of the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI18.0\GeneralFlags\Flag2 registry entry. Valid values are 0 or 1. The OLE DB driver chooses the most secure option between the registry and the connection property/keyword settings. That is, the driver will validate the server certificate as long as at least one of the registry/connection settings enables server certificate validation.

So it could well be that we also have to change values in the registry to finally disable certificate validation when connecting to SQL server via ADO / OLE DB.

Answer 2

The reason TrustServerCertificate=True in the connection string is not honored is twofold. One is that it isn't a valid ADO classic (ADODB) connection string keyword. According to the ActiveX Data Objects (ADO) Connection String Keywords documentation, the keyword/value pair should be Trust Server Certificate=True (note spaces). The keyword is ignored entirely without the spaces and not trusted as a result.

However, this change alone will not trust the certificate because of the Authentication-SqlPassword specification. When the Authentication keyword is specified, the documentation footnote calls out:

To improve security, encryption and certificate validation behavior is modified when using Authentication/Access Token initialization properties or their corresponding connection string keywords. For details, see Encryption and certificate validation link.

The referenced link includes this important note:

Certificate validation can also be controlled through the Value field of the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI18.0\GeneralFlags\Flag2 registry entry. Valid values are 0 or 1. The OLE DB driver chooses the most secure option between the registry and the connection property/keyword settings. That is, the driver will validate the server certificate as long as at least one of the registry/connection settings enables server certificate validation.

So even with Trust Server Certificate=True, the cert will be validated when this registry value is set to 0.

One solution is to simply remove the Authentication=SqlPassword specification as long as you don't need the improved security provided by not trusting the server certificate:

cntemp.Open "Provider=MSOLEDBSQL;Server=dbserver.example.com;Initial Catalog=MyDB;User ID=sa;Password=secret;Trust Server Certificate=True;DataTypeCompatibility=80;"