[Solved][One] Question - Account Passwords?

[Necrovoice]

Hello, I am looking to change the password for an account in the realm DB. the wiki says

SELECT SHA1(CONCAT(UPPER(`username`), ':', UPPER(<pass>)));

Im not very bright when it comes to sql updates and stuff, but the above does not seem to work. I get the error (I replaced the password with pass as it shows in the code so you know where the error is talking about.

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<pass>)))' at line 1"

obviously im doing something wrong, but I always thought this command line looked different in the past. Any help would be grand.

[Necrovoice]

So I thought I had it working. It tells me my login information is incorrect.

I am checking and it seems that my update Query is not updating the password information. When I execute the command it will change the password, but if I logout of Sqylog, and log back in its all back to default. It also did this when I changed the username and also added a new row. when I log in its all gone.

UPDATE account SET sha_pass_hash=SHA1('password') WHERE id=1;

Yes my realmlist.wtf file is directed to localhost

Another quick update I can log in with username:player / password: player. However everything else seems to be locked out.

Ok well I figured out the password problem, but now I am sitting at "Authenticating" at the login screen.

[lfxgroove]

Reading your last sentence i come to wonder if this is solved or not? The problem you had in your first attempt was that you should replace the whole <pass> with the password you would like quoted in '. Ie:

SELECT SHA1(CONCAT(UPPER('myusername'), ':', UPPER('mypass')));

Try that out and see if it yields anything better

[Necrovoice]

Thanks for the response,

I guess I got a little carried away with the updates of what I have going on. I am able to connect to the server and play using default password/user names. But I am unable to add or updated password in the account table. I ran the SELECT option but that does not update anything, only gives me a return with a new hash. so that is why I ran UPDATE This will change the password hash in the table but when I try to log in its the wrong login information. At this point when I log back into Sqylog the password has reverted back to the original hash for the password. Sadly enough it will do this to any field that I try to update in the reamd DB.

Hope this helps with the current issue I am having.

[lfxgroove]

Hmm, does sqlyog has some kind of apply button or the like? You could try this instead (not uppercase on the password):

SELECT SHA1(CONCAT(UPPER('myusername'), ':', 'mypass'));

To generate the password and then use the following query to update it:

UPDATE account SET sha_pass_hash='pass from earlier query' WHERE id=1;

So no `SHA1` around the set in the last one.

[Necrovoice]

The update changed the password hash but I still get incorrect password. I go back to sqylog and its back to the original hash.

Yes Sqylog has a field where you can execute code.

[W@WAdict]

If the id of the account you want to change is 1 then you could try some thing like the following. Replacing yourusername with the user name and yourpassword with the password you want to set, be sure to include the : between them.

UPDATE account SET sha_pass_hash=SHA1(UPPER(`yourusername:yourpassword`))) WHERE id=1;

Later.

W@WAdict.

[Necrovoice]

Thanks for the help so far. that returns an error.

Error Code: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') WHERE id=1' at line 1

I am also unable to add an account. Its almost like anything that I alter in realmd is not saving correctly. I tested the mangos DB and characters DB by altering some of the table data and both reflect the change in game. Its all the tables within realmd that I am having an issue with.

[W@WAdict]

Sorry, try this instead

UPDATE account SET sha_pass_hash=SHA1(UPPER("yourusername:yourpassword")) WHERE id=1;

Take the extra ) off before the WHERE, and change the `` to be ""

[Necrovoice]

Sorry man, it did what we want it to do, change the first password to the new one, well as far as the DB shows. but when I try to log in with new password it says it is wrong, I try the default and it works.

here are the steps I am following. I hope this will pinpoint where its goofing up.

1) run above code in Sqylog (password changes in the table)

2) Logout of sqylog.

3)Start Mangos like normal.

4)Start TBC try to log in with new password (fail at log in) Try old default with Success.

5) Go back into Sqylog and the new password hash is still saved this time around.

If you like I can pm you the exact changes with the hash and the password. see if you can see whats wrong from there?

[Necrovoice]

one thing I forgot to say. I also put the new SHA1 password in the realmd.sql that came with the .git, I rebuild the realmd DB with it and I still get an error that I have incorrect login.\

could this be an issue with the mangos build and how it reads the DB? I am using the precompiled mangos-one-0.18.0-win64.

[lfxgroove]

It seems very strange that it isn't saving the changes to the db Are you sure that you don't have some kind of duplicate and that realmd.exe connects to the same realmd db as the one you're editing?

[Necrovoice]

I had that thought on lunch today at work. Im about to make a new realm DB and alter the .conf files to the new one and see what happens.

*Update* No luck, even after renaming the realm DB and changing the Conf files it still will not allow me to log into the game with a new password. Although the account table shows the new password hash.

I deleted the reamd DB just in case. I am puzzled. I am willing to Teamview this if your up for it. Im on vacation starting tomorrow so give me a time and day. Im at a loss.

[Hikari]

If you change the password in the database you have to erase the cache variables v and s. Otherwise you can't login with the new password.

UPDATE `account` SET `sha_pass_hash`=SHA1(CONCAT(UPPER(`username`),':',UPPER('your new password'))), `v`=0, `s`=0 WHERE `id` = 'your account id';

Note: Change only the password and account id in this query. The query gets the username from the column.

[Necrovoice]

Thank you very much Hikari, that did the trick!!

[lfxgroove]

Oh my, thanks a heap Hikari!

[Hikari]

You're welcome. I had the same problem some time ago and I figured out this solution.

It's always a pleasure if I can help.