Problem using Perl to connect to MySQL database on remote server

Question

I have a Perl script that gets data from a MySQL database on one server (let's call it server1), does stuff with it and writes it out to another database on another server (server2). Both servers are remote to the server that runs the Perl script.

I can connect to the DB on server1 OK, but when I try to connect to the DB on server2, using the same DBI method, I get an error. Here, as command-line Perl, is the bit that's causing the error:

perl -MDBI -e 'DBI->connect("DBI:mysql:myDB:server2.whatever.co.uk","myuser","mypassword") or die DBI->errstr;'

And here's the error message:

DBI connect('myDB:server2.whatever.co.uk','myuser',...) failed: Client does not support authentication protocol requested by server; consider upgrading MySQL client at -e line 1 Client does not support authentication protocol requested by server; consider upgrading MySQL client at -e line 1.

I do not have root access so I can't upgrade MySQL and I can't change the password to use the old password hashing algorithm, which is the solution suggested in lots of places.

Ideas anyone?

Answer 1

The database may be set up to accept connections only from within a certain set of addresses, as a security measure. So if you're trying to access a prod database from a home laptop (for example), it may reject you, even if you have the proper credentials. Try accessing it from a place where it's known to work using another technology -- for example, if you have a website that accesses it already, go to wherever apache/tomcat is running, and try the perl there. If it works, that's the issue. You can also proactively check on the database settings.

Answer 2

OK, in the absence of an alternative, I got someone with root access to server2 to do the fix that's published elsewhere:

Connect to MySQL as the MySQL root user, then:

mysql> use mysql;
mysql> SET PASSWORD FOR 'username'@'hostname' = OLD_PASSWORD('password');
mysql> FLUSH PRIVILEGES;

Replacing 'username', 'hostname' and 'password' with appropriate values.

So what I'm saying here is, it seems like if you don't have root access to upgrade MySQL or to change the password to use the old password hashing algorithm, then the only solution is to find someone who does who can make the change for you.