SQL Server 2012 ForceEncryption without SSL certificate?

user_1442508 0
Viewed 3630

Using information from some consultants about 2 years ago we set up a SQL Server 2012 box to always have an encrypted connection (at least we thought so). A week ago the SSL certificate expired and everything continues to work fine which is puzzling.

We forced encryption by doing the following:

Obtain an SSL certificate and install it on the SQL Server via MMC. Explicit instructions for this step are here.

  1. Open SQL Server Configuration Manger and expand the SQL Server Network Configuration node in the left pane.
  2. Right click Protocols for MSSQLServer and choose properties.
  3. Go to Certificate tab and choose the installed SSL cert from the drop down.
  4. Go to the Flags tab and set force encryption to Yes.

Now, since the SSL cert expired, there is no longer any cert selected in the drop down when I visit step 3 above. However, I put WireShark on the DB server and use SSMS to request some data. The TDS (Tabular Data Stream) packets are still encrypted? When I put WireShark on a different DB Server I set ForceEncryption to NO, those TDS packets are not encrypted as expected.

So my questions are:

  1. How are the TDS packets still encrypted if the cert is expired?
  2. Even when the cert was valid, why didn't I have to select Options > Encrypt Connection to connect to my encrypted DB server? I thought setting ForceEncryption=Yes required ALL client connections to specify Encrypt in their connection string (or the SSMS checkbox)?
  3. If Web APIs are the only method of accessing my DB, and they do so over SSL, is it even necessary to encrypt the SQL Protocol?
wireshark https encryption ssl sql-server
1 Answers

1)How are the TDS packets still encrypted if the cert is expired?

you don't need a valid certificate in order to encrypt, you can still encrypt using an expired certificate.

2)Even when the cert was valid, why didn't I have to select Options > Encrypt Connection to connect to my encrypted DB server? I thought setting ForceEncryption=Yes required ALL client connections to specify Encrypt in their connection string (or the SSMS checkbox)?

Do not enable the Force Protocol Encryption option on both the client and the server, if you enabled force encryption on SQL server you don't need to do it on any client-side

3)If Web APIs are the only method of accessing my DB, and they do so over SSL, is it even necessary to encrypt the SQL Protocol?

To enable SSL you need the certificate to be installed on SQL server so in order to access the DBusing SSL you are doing the same job.

Thank you

user_110331360
Related