Sue D. Nymme has asked for the wisdom of the Perl Monks concerning the following question:

O Great Monks,

I hope nobody'll be offended by my posting a windows-specific question. I know *nix is the Truth and the Way and the Light, but I'm sort of stuck here in purgatory, bear with me. :-)

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? This can't possibly be a wheel that I need to invent myself. Thanks.

  • Comment on Secure connection to SQL Server from Perl DBI

Replies are listed 'Best First'.
Re: Secure connection to SQL Server from Perl DBI
by mje (Curate) on May 28, 2009 at 17:06 UTC

    ODBC does not transmit usernames and passwords in clear text (ODBC does not transmit anything) - but some ODBC drivers do. The basic SQL Server driver on Windows does "encrypt" the password and does not send it clear text but it is not that difficult to break.

    It has been a while since I looked at it but I thought you could set SQL Server up to only accept encrypted connections and if that is the case there must be a Windows driver which does the encryption. May be I am imagining that.

    So long as your client is on UNIX the Easysoft SQL Server ODBC Driver does all forms of SQL Server connections - simple encrypted password (and it is very simple), NTLM (a little better but not much), SSL for the connection only or SSL for everything. Unfortunately it is not available for Windows currently. BTW, I have a connection with Easysoft.

[reply]
Re: Secure connection to SQL Server from Perl DBI
by SilasTheMonk (Chaplain) on May 28, 2009 at 16:38 UTC

    Noone can be offended at the question. This is a perl website not a UNIX website. In any case the monks here are surprisingly tolerant of impertinent non-perl questions though apparently it helps to prefix off-topic questions with the string "[OT]".

    I cannot find any such module. I suspect such a module would need to be XS code linking to the SQL server API libraries. The libraries may have licenses restricting the distribution of derived code which may be one reason why no such modules are in CPAN. There is a procedure for writing new DBD drivers which you might want to look at. But more likely you have to look round the SQL server website on account of the licensing issues. I tried a google and came up with an article.

[reply]
Re: Secure connection to SQL Server from Perl DBI
by lithron (Chaplain) on May 28, 2009 at 17:00 UTC
    I know this is a non-perl solution, but it would work just fine and you already have all the tools available to you.

    Set up an IP Security Policy between the Server (optional) and the client (mandatory). Now when the client requests a TCP connection to the server it will be automatically encrypted (assuming you set up the policy that way) and no one can listen to any data over the wire, including plain text passwords.

    HTH.
[reply]
      This is one of the reasons I like the culture here in the Monastary - People offer the best solution to a problem, rather than the best Perl solution.

      It also epitomises the virtue of laziness, as extolled by Perl - why write a script at all when you don't need to?!

      --
      use JAPH;
      print JAPH::asString();

[reply]
Re: Secure connection to SQL Server from Perl DBI
by december (Pilgrim) on May 29, 2009 at 09:29 UTC

    Hi,

    I don't know anything about SQL Server or Windows, but I would be surprised if there wouldn't be a way to enable authentication over secured connections.

    The Unix way to solve this problem would be to tunnel the connection over SSH. This is what I do when MySQL or PostgreSQL are bound to the loopback interface without open ports to the outside. You connect to the server with SSH, and the SSH daemon on the server would then forward the connection locally to the right port where SQL Server is running. I'm sure there are versions of SSH for Windows, and perhaps Putty can listen for connections in a server sort of way. I'm sorry I can't help you more – I know nothing about Windows software – but this is one way you might be able to make it work in a secure manner.

    Good luck!

[reply]
Re: Secure connection to SQL Server from Perl DBI
by Argel (Prior) on May 28, 2009 at 19:37 UTC
    Regarding Windows, based on replies and posts I'd say that some of our most prominent Monks such as ikegami and BrowserUK have extensive experience running Perl on Windows. In other words, it's not nearly as taboo as you make it out to be. In fact, it's not taboo at all!! Welcome to The Monastery!!

    Elda Taluta; Sarks Sark; Ark Arks
[reply]
Re: Secure connection to SQL Server from Perl DBI
by ikegami (Patriarch) on May 30, 2009 at 20:01 UTC
    This sort of problem is often solved using an ssh tunnel. For example, instead of connecting to server:1234, you connect to localhost:1234 which was setup as a tunnel to server:1234. This solution involves running a bit of software on both the client (putty) and the server (???).
[reply]
[d/l]
      the server (???)

      sshd from cygwin should to the trick, like one of the many other SSH server implementations for Win32.

      Sometimes, a friendly Linux or *BSD machine near the server can also help, running the ssh server, tunnelling a port to the windows server. In PuTTY, specify windowsbox.remote.lan:1433 (not localhost) as tunnel destination when connecting to linuxbox.remote.lan. It may look strange, but it works (unless linuxbox and windowsbox are separated from each other by a firewall). With the openssh command line client, it should look something like this:

      ssh -L 1433:windowsbox.remote.lan:1433 joe@linuxbox.remote.lan

      Connect to localhost:1433 and you should see the SQL server as long as the ssh connection is alive. The command line for PuTTYs plink utility should look nearly the same.

      Alexander

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
[reply]
[d/l]
Re: Secure connection to SQL Server from Perl DBI
by ryanc (Monk) on May 30, 2009 at 19:21 UTC
    What about using Stunnel to send your database connection over an SSL tunnel?
[reply]

Back to Seekers of Perl Wisdom