SQL Server / ODBC data encryption question

Question

Hello -

We would like to force encryption of the data going to and from our customers' SQL databases. Currently, we access the customer databases via ODBC DSNs, using both SQL Management Studio (Query Analyzer) and our custom applications.

Yeah, I know this is not "best practices", but I have to deal with it for now.

Anyway, I have been tasked with encrypting the data on the pipe between us and them. I found plenty of information on how to set up the connection between SQL client and server, but precious little information on how to use the ODBC DSN in such a situation.

Please note, I don't want to know how to encrypt the database, only how to encrypt the data going through the 'tubes'.

1) There is a checkbox in the DSN wizard called "Use strong encryption for data" -- with no help available for the option. Can anyone explain to me how that fits in with the Force Encryption flags in SQL Server and Client Configuration? Our application doesn't use the SQL Native Client interface, but relies on the ODBC DSN setup to connect to the database. Do I need to check this checkbox and install a root certificate? Is that all I need to do if the server is set up properly?

2) In documentation for the client and server encryption (http://support.microsoft.com/kb/316898), Microsoft states "You can enable the Force Protocol Encryption option on the server, or on the client. Do not enable the Force Protocol Encryption option on both the client and the server." Can anybody tell me why this is so? What happens if you get both of them enabled?

3) Does #2 apply to DSN configurations, and if so, how?

So many questions. If you have experience with this, please share some of your hard-earned wisdom...

Thanks, Dave

Answer 1

I assume you have found this:

http://technet.microsoft.com/en-us/library/ms189067%28SQL.90%29.aspx

Our application doesn't use the SQL Native Client interface, but relies on the ODBC DSN setup to connect to the database.

In the ODBC dsn you can choose the SQL Native Drivers, right?

Here's a good link with a spiffy table as well!

http://msdn.microsoft.com/en-us/library/ms131691%28SQL.90%29.aspx

And another blog post from the SQL team explaining a bit more

https://blogs.msdn.com/sql_protocols/archive/2005/11/10/491563.aspx

They don't explain why you shouldn't enable both client and server to require encryption - it doesn't seem like it would hurt anything. I'd say, just try it and see.