3

Short Version

  • ConnectionString: Provider=SQLOLEDB;Data Source=hydrogen;
    User ID=lgilmore;Password=squeegebeckenheim;
    Use Encryption for Data=true;
    Trust Server Certificate=true;

gives error:

[DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error

Background

I am attempting to encrypt communication between SQL Server and an OLEDB client (e.g. ADO, SQLOLEDB).

We note from Microsoft that (unless you've otherwise provisioned one yourself), SQL Server automatically generates a self-signed certificate that is used to secure the login process:

Using Encryption Without Validation

SQL Server always encrypts network packets associated with logging in. If no certificate has been provisioned on the server when it starts up, SQL Server generates a self-signed certificate which is used to encrypt login packets.

We can also opt-in to encryption all the time

Our client can request that we use encryption all the time - not just for the login process. Again, from Microsoft:

Applications may also request encryption of all network traffic by using connection string keywords or connection properties. The keywords are:

  • "Encrypt": for ODBC and OLE DB when using a provider string with IDbInitialize::Initialize, or
  • "Use Encryption for Data" for ADO and OLE DB when using an initialization string with IDataInitialize.

This Use Encryption for Data connection string keyword means we want to use:

  • use encryption not just for the login process
  • but use encryption also for data

And we use "Trust Server Certificate" to trust the self-signed cert

The self-signed certificate that SQL Server automatically generates is self-signed. Normally the client driver walks the chain of trust of a certificate in order to see if the certificate is valid. With the automatic self-signed certificate the connection will fail.

But there's a another keyword to force the client to accept the server certificate: Trust Server Certificate:

To enable encryption to be used when a certificate has not been provisioned on the server, applications may use the "TrustServerCertificate" keyword or its associated connection attribute to guarantee that encryption takes place. To guarantee encryption even when a server certificate has not been provisioned, an application may request encryption and "TrustServerCertificate".

When Trust Server Certificate is set to true, the transport layer will use SSL to encrypt the channel and bypass walking the certificate chain to validate trust.

So we have two keywords:

  • Use Encryption for Data=true: to opt-in to encryption
  • Trust Server Certificate=true: to trust the self-signed certificate

Functional Code Example

Now for some minimally reproducible code.

  • I'm using OLEDB (e.g. ADO).
  • I'm not using ADO.NET.
  • I'm not using ODBC.
  • I'm not using SqlClient (i.e. i'm not using ADO.NET)
  • I'm using SQLOLEDB (i.e. Microsoft OLE DB Provider for SQL Server).
  • I'm not using SQL Server Native Client (e.g. SQLNCLI, SQLNCLI11)

And to further drive home i'm using OLEDB, i'll give a code example that uses OLEDB directly (although it is all true for ADO; which is simply a thin wrapper around OLDB):

String connectionString = 
      "Provider=SQLOLEDB;Data Source=hydrogen;"+
      "User ID=lgilmore;Password=squeegebeckenheim;";

//DAInitialize helper class parses the connection string 
IDataInitialize dataInit = (IDataInitialize)CreateComObject(CLSID_MSDAInitialize);

//Ask DAInitialize to create the SQLOLEDB class for us and set it up 
IDBInitialize dataSource;
dataInit.GetDataSource(null, CLSCTX_INPROC_SERVER, connectionString, IDBInitialize, ref (IUnknown)dataSource);

//Connect to SQL Server
dataSource.Initialize(); //actually opens the database connection

Our initial code sample doesn't request encryption yet; so it's no surprise that we are able to connect. The connection to SQL Server is established without incident. (We can confirm the connection using SQL Profiler).

Code Example - Encryption Enabled

We refactor the code into a function that accepts a connection string:

String connectionString = 
      "Provider=SQLOLEDB;Data Source=hydrogen;"+
      "User ID=lgilmore;Password=squeegebeckenheim;"+
      "Use Encryption for Data=true"; //opt-in to encryption of data

IDbInitialize dataSource = ConnectToDataSource(connectionString);

With our new helper function:

IDbInitialize ConnectToDataSource(String connectionString)
{
   //DAInitialize helper class parses the connection string 
   IDataInitialize dataInit = (IDataInitialize)CreateComObject(CLSID_MSDAInitialize);

   //Ask DAInitialize to create the SQLOLEDB class for us and set it up 
   IDBInitialize dataSource;
   dataInit.GetDataSource(null, CLSCTX_INPROC_SERVER, connectionString, IDBInitialize, ref (IUnknown)dataSource);

   //Connect to SQL Server
   dataSource.Initialize(); //actually opens the database connection
}

We expect this code to fail when connecting to the database. And it does:

[DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error

Trust server certificate also fails

We now update our connection string to Trust Server Certificate=true:

String connectionString = 
      "Provider=SQLOLEDB;Data Source=hydrogen;"+
      "User ID=lgilmore;Password=squeegebeckenheim;"+
      "Use Encryption for Data=true;"+ //opt-into encryption of data
      "Trust Server Certificate=true"; //trust the self-signed server cert

but it still fails with the same error:

[DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error

SQLOLEDB doesn't recognize Trust Server Certificate

There is a lot of confusion how to specify Trust Server Certificate. Between initializing an OLEDB provider directly, to using an IDataInitialize, to the SQL Server Native Client (SQLNCLI), to ADO.net SqlClient, and Java ODBC, there are variantions out there:

  • Trust Server Certificate=true
  • Trust Server Certificate=yes
  • TrustServerCertificate=true
  • TrustServerCertificate=yes

I tried all four of the above variantions; and none worked.

Ask the provider if it recognizes it

I used the DataLinks class have it canonicalize my connection string:

String CanonicalizeConnectionString(String cs)
{
   IDataInitialize dataInit = (IDataInitialize)CreateComObject(CLSID_DataLinks);

   IDBInitialize datasource;
   dataInit.GetDataSource(nil, CLSCTX_INPROC_SERVER, connectionString, IDBInitialize, ref (IUnknown)dataSource);

   String result;
   dataInit.GetInitializationString(dataSource, true, out result);
   return result;
}

This gives me my original and the parsed connection strings:

  • Original: Provider=SQLOLEDB;Data Source=hydrogen;User ID=lgilmore;Password=squeegebeckenheim;Use Encryption for Data=true;Trust Server Certificate=true;
  • Canonicalized: Provider=SQLOLEDB.1;Password=squeegebeckenheim;User ID=lgilmore;Data Source=hydrogen;Extended Properties="Trust Server Certificate=true";Use Encryption for Data=True

Given that Trust Server Certificate has been put inside the catch-all for unrecognized properties:

  • Extended Properties="Trust Server Certificate=true"

The fact that it's not recognized means i might have to keep searching for the correct magical syntax.

Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219

1 Answers1

3

I figured it out.

Asking a Data Source for all the properties it supports:

String EnumerateAllProperties(String connectionString)
{
   IDataInitialize dataInit = (IDataInitialize)CreateComObject(CLSID_MSDAInitialize);

   IDBInitialize dataSource;
   dataInit.GetDataSource(null, CLSCTX_INPROC_SERVER, connectionString, IDBInitialize, ref (IUnknown)dataSource);

   String s = "";
   Int32 nSets;
   PDBPropInfoSet pi;
   POleStr desc;
   (dataSource as IDBProperties).GetPropertyInfo(0, null, ref nSets, out pi, ref desc);
   for (int i=0, i <nSets, i++)
   {
      s = s+CRLF+
            PropSetGuidToStr(pi.guidPropertySet);
      for (int j=0, j < pi[i].cPropertyInfos, j++)
      {
         s = s+CRLF+
             IntToStr(pi[i].rgPropertyInfos[j].dwPropertyID)+TAB+
             PWideChar(pi[i].rgPropertyInfos[j].pwszDescription)+TAB+
             VTypeToStr(pi[i].rgPropertyInfos[j].vtType);
      }

      s = s+CRLF;
   }

   return s;
}

I realized that SQLOLEDB (Microsoft OLE DB Provider for SQL Server) doesn't support Trust Server Certificate:

SQLOLEDB - DBPROPSET_DBINIT Property Set

PropertyID Description Type SQLOLEDB
7 Integrated Security VT_BSTR Yes
9 Password VT_BSTR Yes
11 Persist Security Info VT_BOOL Yes
12 User ID VT_BSTR Yes
59 Data Source VT_BSTR Yes
60 Window Handle VT_I4 Yes
64 Prompt VT_I2 Yes
66 Connect Timeout VT_I4 Yes
160 Extended Properties VT_BSTR Yes
186 Locale Identifier VT_I4 Yes
233 Initial Catalog VT_BSTR Yes
248 OLE DB Services VT_I4 Yes
284 General Timeout VT_I4 Yes

SQLOLEDB - DBPROPSET_SQLSERVERDBINIT property set

PropertyID Description Type SQLOLEDB
4 Current Language VT_BSTR Yes
5 Network Address VT_BSTR Yes
6 Network Library VT_BSTR Yes
7 Use Procedure for Prepare VT_I4 Yes
8 Auto Translate VT_BOOL Yes
9 Packet Size VT_I4 Yes
10 Application Name VT_BSTR Yes
11 Workstation ID VT_BSTR Yes
12 Initial File Name VT_BSTR Yes
13 Use Encryption for Data VT_BOOL Yes
14 Replication server name connect option VT_BSTR Yes
15 Tag with column collation when possible VT_BOOL Yes

Where if you compare that to SQL Server Native Client 11.0

SQL Server Native Client 11.0

SQLNCLI11 - DBPROPSET_DBINIT Property Set

PropertyID Description Type SQLOLEDB SQLNCLI11
7 Integrated Security VT_BSTR Yes Yes
9 Password VT_BSTR Yes Yes
11 Persist Security Info VT_BOOL Yes Yes
12 User ID VT_BSTR Yes Yes
59 Data Source VT_BSTR Yes Yes
60 Window Handle VT_I4 Yes Yes
64 Prompt VT_I2 Yes Yes
66 Connect Timeout VT_I4 Yes Yes
160 Extended Properties VT_BSTR Yes Yes
186 Locale Identifier VT_I4 Yes Yes
200 Asynchronous Processing VT_I4 No Yes
233 Initial Catalog VT_BSTR Yes Yes
248 OLE DB Services VT_I4 Yes Yes
284 General Timeout VT_I4 Yes Yes

SQLNCLI11 - DBPROPSET_SQLSERVERDBINIT property set

PropertyID Description Type SQLOLEDB SQLNCLI11
4 Current Language VT_BSTR Yes Yes
5 Network Address VT_BSTR Yes Yes
6 Network Library VT_BSTR Yes Yes
7 Use Procedure for Prepare VT_I4 Yes Yes
8 Auto Translate VT_BOOL Yes Yes
9 Packet Size VT_I4 Yes Yes
10 Application Name VT_BSTR Yes Yes
11 Workstation ID VT_BSTR Yes Yes
12 Initial File Name VT_BSTR Yes Yes
13 Use Encryption for Data VT_BOOL Yes Yes
14 Replication server name connect option VT_BSTR Yes Yes
15 Tag with column collation when possible VT_BOOL Yes Yes
16 MARS Connection VT_BOOL No Yes
18 Failover Partner VT_BSTR No Yes
19 Old Password VT_BSTR No Yes
20 DataTypeCompatibility VT_UI2 No Yes
21 Trust Server Certificate VT_BOOL No Yes
22 Server SPN VT_BSTR No Yes
23 Failover Partner SPN VT_BSTR No Yes
24 Application Intent VT_BSTR No Yes

You can see that the deprecated native client supports Trust Server Certificate, while the supported OLE DB client does not:

| 21         | Trust Server Certificate                | VT_BOOL |   No      | Yes       |

This is unfortunate, given the "won't fix" bugs in the ODBC driver (1, 2, 3)

Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219