secure ODBC connection -- oxymoron?

Question

Hello -

There are times we need to create an ODBC connection over the "tubes" to one of our customer sites. We would like to provide as much security as possible to our customers, given we are using ODBC and, well...

Anyway, there is a checkbox setting in the SQL Server DSN that says "Use strong encryption for data", but absolutely no documentation for it. The only references I can find on the Google nets are unanswered questions -- not very encouraging. Does anybody have a clue what it does or how it works? If that isn't a way to encrypt the data stream, is there another way?

BTW, we cannot rely on our customers to force encryption from their end, and dealing with security certificates would be a real nightmare.

Thanks in advance, Dave

Answer 1

There is no reason you can't have a secure connection while using ODBC. Basically, the responsibility for over-the-wire security would fall under the ODBC driver (basically the database-specific part). If the driver doesn't already provide for this (SQL Server may or may not - I don't know what "Use strong encryption for data" applies to) you can probably add your own. One possibility would be to create a SSH bridge, e.g. using ssh -L. I don't know if this counts as a "nightmare", but it would probably be an effective and fairly simple technique.

Answer 2

Is it SQL 2000 or 2005/2008?

The encryption enforcement can be requested by the client or enforced by the server. The encryption is based on Schannel protocol (ssl) and as such requires an valid certificate deployed on the server and trusted by the client, there is no way out of that. The certificate has to be signed by an authority that is trusted by the client and, amongst other typical server certificate requirements, must have the FQDN name used to connect by the client as its subject.

In SQL 2005 How to: Enable Encrypted Connections In SQL 2000 Configure the Server and Request encryption by client