Ssl – SQL Server does not enforce encryption rules

sql serversql-server-2008ssl

I am forcing encryption on my MS SQL server. My intention is to reject any client connection that does not use SSL to connect.

Here are my detailed steps :

  • makecert -r -pe -n "CN=slc02xla.company.com" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 c:\my.cer
  • I imported the same certificate into the trusted Root Certification Authorities Store
  • In SQL Server Configuration Manager, expandrf SQL Server Network Configuration, right-clicked Protocols for , and then selected Properties.
  • On the Certificate tab, selected the desired certificate from the Certificate drop-down menu, and then clicked OK.
  • On the Flags tab, selected Yes in the ForceEncryption box, and then clicked OK to close the dialog box.
  • Restarted the SQL Server service.

Why in the world are my clients who don't specify encrypt=true in their connection strings are still able to connect?

Best Answer

10 seconds google lead to:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/bde679d9-ff83-4fa7-b402-42e336a97106/force-encryption-on-sql-server-not-working?forum=sqlsecurity

Let me quote:

When the Force Encryption option for the Database Engine is set to YES, all communications between client and server is encrypted no matter whether the “Encrypt connection” option (such as from SSMS) is checked or not. You can check it using the following DMV statement.

Clear enough?

More details under the link. And yes, nothing should stop you from using google occasionally. I just looked for "sql server ForceEncryption". Second hit had the needed title.

Related Topic