Selectively using secure connection to SQL Server

Selectively using secure connection to SQL Server

Rate This
  • Comments 2

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.

 

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; certificate is not validated by the client.

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 certificate is not validated by the client.

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 certificate is not validated by the client.

 

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")

 

Bhaskar Bhattacharyya, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Leave a Comment
  • Please add 6 and 2 and type the answer here:
  • Post
  • We have just recently upgraded to Sql2005 from 2000.

    Our main application uses services which are written in Ms Java 6.0.  One service in particular is used very frequently to receive data over a port and enter it into the application database.  We use java and com classes in com.ms.wfc.data.*.  In particular we use com.ms.wfc.data.Connection to create a Connection to the database.  We are experiencing com error "Failed remote creation of coclass com/ms/wfc/data/adodb/Connection" We are using  a connection string like "Driver={SQL Server};SERVER=FINFANTE;DATABASE=UltraCare;UID=CareUser;PWD=pwd;APP=Infinity;"

    What is happening is that after creating and using several Connections successfully, and storing data in the db successfully, then after a short period like a minute or 2, the next attempt to create a Connection fails with the com error.

    Nothing like this had ever happened with sql 2000.

    We have tried using different drivers and/or providers and are still getting the same error.

    Any clues about how to proceed debugging would be greatly appreciated.

  • good article.. no del please..

Page 1 of 1 (2 items)