Remote mySQL connection throws "cannot connect to MySQL 4.1+ using the old insecure authentication" error from XAMPP

Question

I'm running a local copy of WordPress on XAMPP/WinXP for development, but would like to maintain a connection to the remote database. I keep getting "Error establishing database connection" no matter what I try.

On the same PC, I can connect to the remote mySQL DB using any number of mySQL clients, and on the mySQL side, the both the user and the database are set to accept incoming requests from any wildcard domain. I can also easily ping the remote database server from my PC (though I don't know how to do it from WITHIN XAMPP).

Is XAMPP its own little universe that can't reach through to the outside world? Or is there something I'm clearly overlooking that's not letting me connect?

Errors

Warning: mysql_connect() [function.mysql-connect]: Premature end of data (mysqlnd_wireprotocol.c:553) in C:xampphtdocsdbtest.php on line 5 
Warning: mysql_connect() [function.mysql-connect]: OK packet 1 bytes shorter than expected in C:xampphtdocsdbtest.php on line 5 
Warning: mysql_connect() [function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication

Edit

Thanks to @Michael for suggesting I just create a simple connection script so I can get better insight into the actual error that's being thrown. This revealed that it had to do with the old_password setting in mySQL. See my Answer below for a full description of how to resolve this issue.

Here's the test script I put inside my xampphtdocs folder and tested out:

<?php

$mysqli = new mysqli('my.server.address', 'user_name', 'password', 'database_name');

if ($mysqli->connect_error){
    die ("Connect error: " . $mysqli->connect_error );
}

Answer 1

I'm not really clear on why this became an issue on my XAMPP installation, since I'm also running PHP 5.3.x on the server's local box and wasn't experiencing those issues there. However, it has to do with my mySQL server running in "old password" encryption mode. Newer versions of PHP won't allow those kinds of connections, so you need to update your mySQL server to use the newer password encryption. Here are the steps, assuming you have control over the mySQL server. If you don't, that falls out of the scope of my knowledge.

1. locate the configuration file for the mysql server called my.cnf. I found mine at /etc/my.cnf. You can edit it with sudo nano /etc/my.cnf

2. Look for a line that says old_passwords=1 and change that to old_passwords=0. You have now told the server that the next time it is run, and it is asked to encrypt a password using the PASSWORD() command, it use the new 41-character encryption rather than the 16-character 'old' style encryption

3. Now you have to restart your mysql server / service. YMMV, but on Fedora that was easily done with sudo service mysqld restart. Check your OS' instructions for restarting the mysql daemon or service

4. Now we have to actually edit our user table within mysql. So open up an interactive shell to mysql (on the server you can type mysql -uYourRootUsername -pYourRootPassword)

5. Change to the mysql database. This is the database that holds all the good stuff for server operation and authentication. You must have root access to work with this database. If you get an 'access denied' you're SOL. Sorry. use mysql; will switch to that database

6. Now we want to update the user that was giving you grief. Ultimately you'll probably want to update all your users, but for now, we're just focusing on the user that threw the error. update user set Password=password('YOUR_PASSWORD') where User='YOUR_USERNAME';

7. Now you just need to tell mysql to use the new password for authentication when that user attempts to connect. flush privileges;.

You should be good to go!