1

We are implementing SQL 2014 encrypted connections in the near future. I want to do my due diligence and confirm the cert validation process. I also want to use the trustservercertificate=false option. I want all connections to actually use cert validation. If a server certificate gets revoked, I want the connection to fail. So I have implemented a certificate on the SQL server and revoked it. if I use certutil -verify, I confirm the revocation. However, even with trustservercertificate=false, my sql connection still succeeds.

This is my full SQL connection parameters:

$cn = New-Object System.Data.SqlClient.SqlConnection
$cn.ConnectionString = "data source=fqdnservername;user=blah;password=blah;encrypt=true;trustservercertificate=false"
$cn.Open()

$cmd = $cn.CreateCommand()
$cmd.CommandText = "select sysdatetimeoffset()"
$dto = $cmd.ExecuteScalar()
Write-Output "Current SQL server time: $dto"
$cmd.Dispose()

$cn.Close()
HBruijn
  • 72,524
  • 21
  • 127
  • 192
MattRDude
  • 11
  • 3
  • How did you revoke it? With the `REVOKE` SQL command? Or did you revoke it at the CA? – longneck Jan 04 '17 at 16:18
  • The cert was issued by an internal MS Cert Server, so I used the CA server console to revoke. Certutil -verify confirms: The certificate is revoked. 0x80092010 (-2146885616 CRYPT_E_REVOKED) ------------------------------------ Certificate is REVOKED Leaf certificate is REVOKED (Reason=5) CertUtil: -verify command completed successfully. – MattRDude Jan 04 '17 at 18:04

1 Answers1

1

Why does SQLclient still allow an encrypted connection with a revoked cert?

Because many TLS libraries only do minimal checking of server certificates, either for performance reasons or because developers see no need to implement better checks.

In this particular case the .Net documentation hints at what checks are performed:

SqlConnection.ConnectionString Property --> Encrypt
When true, SQL Server uses SSL encryption for all data sent between the client and server if the server has a certificate installed. Recognised values are true, false, yes, and no. For more information, see Connection String Syntax.
Beginning in .NET Framework 4.5, when TrustServerCertificate is false and Encrypt is true, the server name (or IP address) in a SQL Server SSL certificate must exactly match the server name (or IP address) specified in the connection string. Otherwise, the connection attempt will fail.

In other words the only1 security check that's being done with the combination of "encrypt=true;trustservercertificate=false" is to see if the certificate hostname matches the hostname of the server you're trying to connect to.

I wouldn't be surprised if using an expired TLS server certificate would also work without fail.

1 Well not quite the only check, trustservercertificate=false won't accept self-signed certificates so the certificate must still be signed by a known/trusted CA

HBruijn
  • 72,524
  • 21
  • 127
  • 192
  • That is consistent with what I have researched. However, I do know that it does more than just name verification. It does some amount of chain verification. If I use a self-signed cert on the server, then my client is unable to connect when using TrustServerCertificate=false. So, I ponder why they would perform a chain verification without revocation checking? The whole house of cards comes down if you are going to skip revocation checking... – MattRDude Jan 04 '17 at 18:17
  • That the cert name must match is hostname is **not quite** the *only* check, [`trustservercertificate=false`](https://msdn.microsoft.com/en-us/library/ms254500(v=vs.110).aspx#Using%20TrustServerCertificate) won't accept self-signed certificates so the certificate must still be signed by a known/trusted CA – HBruijn Jan 05 '17 at 11:43