How can I secure connection to SQL Server from Perl DBI?

Question

I need to connect to a secure SQL Server database using Perl DBI. I need to find a way to authenticate the user securely (without fear of eavesdropping, and without storing passwords on the client side). I'm using SQL Server 2008 on Windows Server 2008, and Perl 5.10 on XP.

SQL Server supports encrypted connections via something called the "SQL Server Native Access Client API", but I cannot tell whether this is supported by any DBD driver, or how to use it if it is.

I am reluctant to use the DBD::ODBC driver with SQL Server authentication, because ODBC transmits user IDs and passwords in cleartext.

I can't use the DBD::ODBC driver with Windows authentication (trusted connection), because the server is not on any Active Directory domain that is accessible from the client's network.

How can I secure my connections to the database? Thanks.

Answer 1

Two options:

1. Create an IPSec tunnel between two locations. Some info here: technet.microsoft.com/en-us/library/cc737154(WS.10).aspx technet.microsoft.com/en-us/library/cc786385(WS.10).aspx

2. Enable SSL. I am not sure how well it would be protected. technet.microsoft.com/en-us/library/ms189067.aspx

Answer 2

I would suggest that you use some kind of secured connection system. It might be IPSec, but it is often viewed as complicated, so you might be better (or faster) off with OpenVPN, which I use, and generally am happy with.

It can be used on virtually any operating system, is fast, and free. You can check it here: http://openvpn.net/

Answer 3

I am reluctant to use the DBD::ODBC driver with SQL Server authentication, because ODBC transmits user IDs and passwords in cleartext.

That is not strictly true. If you are using a recentish SQL Server and have not disabled encryption in it then the username/password and connection string are encrypted as follows:

The client contacts the server and tells it what it is capable of (e.g., it can do SSL). If the server then supports SSL it requires the client end to encrypt the connection info and falls back to unencrypted after that. You should also see the "Use Strong encryption for data" checkbox in the DSN.