Andre_C10002 has asked for the wisdom of the Perl Monks concerning the following question:

Hello guys,

Long time reader but this is my first question.

I have a MySQL server in a data center and processing servers in another place, therefore I need to encrypt connections from my Perl scripts to the database. All the necessary settings were made on MySQL (creation of a new user, creation of ca, server and client keys) and MySQL connections over SSL work fine.

root@server:# mysql -h _HOST_ --port 3306 -u _SSL_USER_ --ssl-cert=/et
+c/mysql/certs/client-cert.pem --ssl-key=/etc/mysql/certs/client-key.p
+em -p _DATABASE_
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49694205
Server version: 5.0.96 Source distribution
...

[download]

So, I believe set up is good. The problem is that I can't make my Perl script to connect to the database. The returned error is simply:

Access denied for user '_SSL_USER_'@'_HOST_' (using password: YES) at temp.pl line 7.

To simplify, I've put only the following code in my script:

#!/usr/bin/perl

use strict;
use DBI;

#DBI->trace(5);
my $dbh = DBI->connect(
                        "DBI:mysql:database=_DATABASE_;host=_HOST_;
                        mysql_ssl=1;
                        mysql_ssl_client_key=/etc/mysql/certs/client-k
+ey.pem;
                        mysql_ssl_client_cert=/etc/mysql/certs/client-
+cert.pem;
                        mysql_ssl_ca_file=/etc/mysql/certs/ca-cert.pem
+",
                        '_SSL_USER_',
                        '_SSL_USER_PWD_'
                  ) || die DBI->errstr;
exit(0);

[download]

The DBD::mysql module was compiled with the '-ssl' option.

I can't figure it out or find ways to further debug. Any help would be much appreciated.

Thank you!

Replies are listed 'Best First'.
Re: DBI:mysql connection over SSL fails
by kcott (Archbishop) on May 09, 2014 at 04:50 UTC

    G'day Andre_C10002,

    Welcome to the monastery.

    I'm not in a postion to test this, but one problem may be all the embedded spaces and newlines in your DSN. Try writing it more like this: 

    my $dbh = DBI->connect(join('',
        'DBI:mysql:database=_DATABASE_;host=_HOST_;',
        qw{
            mysql_ssl=1;
            mysql_ssl_client_key=/etc/mysql/certs/client-key.pem;
            mysql_ssl_client_cert=/etc/mysql/certs/client-cert.pem;
            mysql_ssl_ca_file=/etc/mysql/certs/ca-cert.pem
        }
    ),
    '_SSL_USER_',
    '_SSL_USER_PWD_'
) || die DBI->errstr;

    [download]

    Another potential issue is what you really have for the placeholders you show (e.g. '_SSL_USER_PWD_'). If any of those are (or evaluate to) strings with special characters (e.g. "pass$word"), you may need to escape the special characters. There's several ways of doing this depending how you're actually getting that data into connect(). Some examples: "pass\$word", 'pass$word', q{pass$word}, "\Q$variable_holding_password\E".

    -- Ken

[reply]
[d/l]
[select]

      Thank you, Ken.

      Unfortunately, the syntax change to use "qw" didn't help.

      On the '_SSL_USER_' and '_SSL_USER_PWD_' values, I shouldn't have problems as they are enclosed on single quotes, therefore shouldn't be evaluated. Anyway, I escaped whatever chars could cause problems with no luck.

      Cheers,

      André

[reply]
        "Unfortunately, the syntax change to use "qw" didn't help."

        Just checking that you did notice that involved more than just qw{}.

        joining without intervening whitespace was the main point; qw{} was of lesser importance and could've been implemented in other ways. 

        join('', 'DBI:...', qw{...})

        [download]

        -- Ken

[reply]
[d/l]
[select]
      Thank you Ken, you saved my day!
[reply]
      Fixed it for me. Thanks!
[reply]
Re: DBI:mysql connection over SSL fails
by Andre_C10002 (Initiate) on May 09, 2014 at 02:42 UTC

    I forgot to mention the Perl version:

    This is perl, v5.10.0 built for i486-linux-thread-multi
[reply]
[d/l]

Back to Seekers of Perl Wisdom