9

I am forcing encryption on my SQL Server. My intention is to reject any client connection that does not use SSL to connect. Am I on the right track?

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.

Am I missing anything else?

Julien Vavasseur
  • 10,180
  • 2
  • 28
  • 47
Codrguy
  • 195
  • 1
  • 1
  • 5

3 Answers3

8

Yes, this is correct. It's also described here Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager) And maybe also this discussion helps: force-encryption-on-sql-server-not-working

Andreas Wolter
  • 653
  • 3
  • 10
2

There are two ways to encrypt the SSL connection.

  1. Configure server for all the incoming client request for SSL.

  2. Selective client configuration.

In the first case if you installed certificate on the server and make "Force encryption to yes" then no matter which client is querying for connection, it will always create SSL connection.

In the second case you need to have the same certificate installed on server and client (trust root certificate authorities store) machine.Then it will create SSL connection for that client otherwise it will fail to establish SSL Connection (provided you check the Encrypt connection in SSMS). If Encrypt connection is not checked then it will create connection but not secured (SSL).

Julien Vavasseur
  • 10,180
  • 2
  • 28
  • 47
Nihal Kumar
  • 121
  • 2
0

In SQL Server 2022 the correct answer would be to use "Force Strict Encryption" with a proper certificate. The term "Force encryption" is indeed not the greatest choice. Note that things have changed with the latest Windows version and SQL Server 2022. Using self-signed certificates no longer complies. Here is a recent article about this subject: Use TLS 1.2 and trusted certificates to encrypt data in transit for all SQL Servers, including development environments

Andreas Wolter
  • 653
  • 3
  • 10