Home / server / Questions / 824153
In Process
MattRDude
Asked: 2017-01-05 07:50:42 +0800 CST

Why does SQLclient still allow encrypted connection with revoked cert?

  • 772

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()
sql encryption revoked

1 Answers

  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

    • 1