Monday, October 19, 2009

Selectively using secure connection to SQL Server

by SQL Protocols via SQL Protocols on 10/19/2009 9:48:00 PM

Secure connection to SQL Server can be enforced by different ways, e.g. forcing encryption by using “ForceEncryption” property under the Network Configuration in the server. But this setting will force all clients to use encryption and any client that is not able to use an encrypted connection will fail.

If you need to connect to SQL Server selectively here is how to achieve this:

You can take advantage of the two client settings, “Force Protocol Encryption” and “Trust Server Certificate” and two connection string parameters, “Encrypt” and “Trust Server Certificate”. The table below describes different combinations of these flags and the corresponding behavior.

If you are not using a server certificate you can go for #5, though #4 with Server certificate is recommended to prevent Man-In-Middle type network attacks. Alternatively you can also use connection string parameters, depending on whether you are using verifiable Server Certificate or self signed server certificate chose #2 or #3. The last two options use both client settings and connection string parameters.

The above connection string values are for OLEDB, for ODBC the connection string values are “true”/”false” instead of “yes”/”no”. For more info please refer to http://msdn.microsoft.com/en-us/library/ms130822%28SQL.90%29.aspx

If you still get error messages like “SSL Security error” and you are using a verifiable server certificate you need to follow the link below to export the server certificate and import it back in the client.

http://support.microsoft.com/kb/316898 (follow the steps for "Enable encryption for a specific client")