Encrypting connections in SQL Server 2005 & SQL Native Client with SSL

Published Mar 23 2019 04:13 AM 142 Views
Microsoft
First published on MSDN on Nov 10, 2005

There are a few different encryption options for SQL Server 2005

By default using SQL Native Client your login packet will be encrypted over the wire so as to not disclose your password.  This encryption is supported by a self-signed & self-generated certificate that is provisioned by SQL upon server startup.  If you install a mutually trusted certificate you can increase your protection against man-in-the-middle type attacks.

Server
There are controls on the server to require that clients connecting to SQL you can control this with the ForceEncryption property under the Network Configuration.  When you set this any client that is not able to use an encrypted connection will fail.  In the SQL Server Configuration Manager right-click on “Protocols for MSSQLSERVER” and go to properties. You have to restart SQL for the changes to take effect here.

Client
On the client there are two settings; 1) Force Protocol Encryption; this will force the client to encrypt the entire data stream 2) Trust Server Certificate; this is the switch to say if you are going to trust the self-generated server certificate.

Your safest bet here with these two settings is #4; this will require that your server uses a provisioned, trusted certificate.



Force Protocol Encryption client setting


Trust Server Certificate client setting


Connection string/connection attribute Encrypt/Use Encryption for Data


Connection string/connection attribute Trust Server Certificate


Result


1.


No


N/A


No (default)


Ignored


No encryption occurs.


2.


No


N/A


Yes


No (default)


Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails.


3.


No


N/A


Yes


Yes


Encryption always occurs, but may use a self-signed server certificate.


4.


Yes


No


Ignored


Ignored


Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails.


5.


Yes


Yes


No (default)


Ignored


Encryption always occurs, but may use a self-signed server certificate.


6.


Yes


Yes


Yes


No (default)


Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails.


7.


Yes


Yes


Yes


Yes


Encryption always occurs, but may use a self-signed server certificate.

Brad Sarsfield


Version history
Last update:
‎Mar 23 2019 04:13 AM
Updated by: