Still can't authenticate with allowOldPasswords=1

[dsparling]

We're running Go 1.1.1, MySQL 5.0.22 on a separate server (but the database is legacy and MySQL itself has been upgraded a few times, I'm sure it was 4.x at some point.) and "old_passwords" is set to ON. (verified with SHOW VARIABLES.)

I'm converting a batch of Perl and Ruby scripts to Go, and the Perl and Ruby scripts can connect to this legacy database with no problem. I'm trying to determine if I'm doing something wrong (relatively new to Go) or not.

In packets.go readResultOK(), the first time through it returns errOldPassword (data=[254]) as expected. Second time through it hits the default branch of the case statement, ultimately returning a "Error 1045: Access denied for user" back to the calling script.

I'm going to see if I can get the DBA to set "old_passwords" to OFF, but am still curious if I'm missing something. Thanks!

[arnehormann]

The leading 255 indicates you receive an err-packet from the server.
I'm not really in the mood to reverse engineer its contents and it probably helps to have the database at hand.
You can have a stab at it yourself.
The documentation: http://dev.mysql.com/doc/internals/en/generic-response-packets.html#packet-ERR_Packet
I hope that helped 😀

[arnehormann]

I should probably add that the function should return an error containing the information you seek. Do you store and check the returned error?

[julienschmidt]

The ERR-Packet alone doesn't really help.
The only reason I can think of is that a) the provided password is wrong b) somehow a wrong hash is transmitted to the server.
It would help a lot, if you could somehow provide a packet dump of a failing auth phase.

[dsparling]

Thank you for the info. This is as far as I've gotten so far. I'm using the same DSN as the Perl and Ruby scripts, so I know the username and password are good and that I have the ability to connect from this server/hostname. I'm also able to connect successfully to our newer databases with no problem.

The error returned from the method is: "Error 1045: Access denied for user 'remote'@'hfstats02.example.com' (using password: YES)." Since I know the password is correct (and I can connect from the Perl/Ruby scripts with the same user and hostname on this server), I am assuming the hash is not.

I am going to try to get the MySQL server updated so it doesn't use "old_password,", but will try to prove a packet dump if I can.

[dsparling]

Here's packets.go - writeAuthPacket() for failing auth phase:

[67 0 0 1 141 162 0 0 0 0 0 0 33 101 98 105 97 110 95 48 117 98 117 110 116 117 54 46 48 54 46 49 52 45 108 111 114 101 109 111 116 101 0 20 23 100 37 209 113 159 149 50 133 175 96 172 99 184 19 55 146 62 119 50 117 99 118 104 114 104 0]

With (I think) expected return in packets.go - readResultsOK():

[254]

With human readable error message in driver.go:

This server only supports the insecure old password authentication. If you still want to use it, please add 'allowOldPasswords=1' to your DSN. See also https://github.com/go-sql-driver/mysql/wiki/old_passwords

The second time around (with allowOldPasswords=1):

packets.go - writeOldAuthPacket():

[9 0 0 3 77 87 85 72 90 89 78 78 33]

packets.go - readResultsOK():

Human readable error message:

Error 1045: Access denied for user 'remote'@'hfstats02.example.com' (using password: YES)

Not sure if that's in the info you wanted to see (@julienschmidt) or if you need a username/password in plain text for a comparison. (if so, I can create a temp account.) I don't mind trying to solve the issue myself, but like I said, I'll probably try to get the database updated if I can. However, given that it's legacy and heavily used, that may not happen. So if not, I'll try and figure it out but it'll be a few weeks until I can devote much more time looking for the solution.

Thanks again!

[dsparling]

One last thing - the DBA says the db should accept long passwords, though we do have "old_passwords" set to ON. As I said earlier, this is a legacy database that's been moved and upgraded a few times, probably 10 years old and originally ran on MySQL 4.x (possibly even 3.x). Now it's on 5.0.22.

I checked the length of the crypted password in the mysql database user table, and the length is 16, which as I recall is pre-MySQL 4.1.

So if it helps, we're apparently using pre-MySQL 4.1 password hashing on a MySQL 5.0.22 server.

[xaprb]

Off-topic: if you are going to run 5.0.x, for heaven's sake don't run
5.0.22. It is almost impossible to describe how buggy it was.

[dsparling]

Yeah, this is a huge bit of technical debt we've accumulated. It's in a "if it's not broke, don't fix it" mode until we can actually replace it. It's got Gigs of data and high usage, so it's at the top of the todo list, but hardest to get done.

[dsparling]

At this point, it looks like an issue supporting pre-MySQL 4.1 and bit of an edge case, so I'm going to close the ticket. If I get something working I'll submit a pull request. If it's something you can address, awesome, if not, that's fine too. I'll just continue using Ruby or see if I can create a new dataabase user with the newer password hashing.

[dsparling]

One more item - I was able to connect to the legacy database using mymysql https://github.com/ziutek/mymysql

It looks like all is good with Go-MySQL-Driver up to and including

scrambleBuff := scrambleOldPassword(cipher, []byte(mc.cfg.passwd))

in writeOldAuthPacket() in packets.go

So it looks like maybe it's the writing of the packet, not the hash itself that's the problem. Sorry, I didn't have a chance to debug further.

[arnehormann]

So apparently our existing code is buggy...

@xaprb / @dsparling do you have an old MySQL database ready?
I'd like some test vectors (raw password and scamble array -> encrypted password).
I've rewritten the scrambling code, but I don't have a possibility to test it and want to add an offline test for old passwords only.
Raw passwords should include candidates with tabs and spaces and some regular ones - pass123 and the like...

[arnehormann]

@dsparling I created a test myself with values based on mymysql responses:

func TestOldPass(t *testing.T) {
    scramble := []byte{9, 8, 7, 6, 5, 4, 3, 2}
    vectors := []struct {
        pass string
        out  string
    }{
        {" pass", "47575c5a435b4251"},
        {"pass ", "47575c5a435b4251"},
        {"123\t456", "575c47505b5b5559"},
        {"C0mpl!ca ted#PASS123", "5d5d554849584a45"},
    }
    for _, tuple := range vectors {
        ours := scrambleOldPassword(scramble, []byte(tuple.pass))
        if tuple.out != fmt.Sprintf("%x", ours) {
            t.Errorf("Failed old password %q", tuple.pass)
        }
    }
}

My problem is that the test passes with the existing implementation. I'm at a loss here.
Can you please add a failing case to the code above? If scramble is the issue feel free to add it to the struct.

I need a mismatch in a test to fix this...