February 23, 2009 at 2:29 pm
Is it possible you would end up with a 'bad' password hash during server migration? Like in a recent migration, we were facing a login error (Message - Login failed for user XYZ) with a particular account - and the administration staff came back with a root-cause-analysis that the problem was a "BAD PASSWORD HASH" ..... what does that even mean? aren't password hashes encrypted so one can't make out the good from the bad?????? I just find it easier to discuss these items here.
February 23, 2009 at 2:55 pm
What are you using to check the hash code? There were some undocumented procedures floating around on how to do that, but those have been notoriously "flaky" since the hashing mechanism can change at any time (well - any time a new patch comes out).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 23, 2009 at 3:00 pm
Matt, I was not using anything to verify of the password had a bad hash - this came from the DBA team which worked 'behind the scenes to come back with an analysis that the "bad password hash" was what causing our application failures as were logging in to the database.
I wanted to know if there was such a thing as "bad" password hash - and if there was, what would cause it/ fix it?
February 23, 2009 at 3:21 pm
What I'm trying to get at is someone somewhere is trying to validate the password during login. The most common way to do this is to use the same encryption routine as what was used by the authentication mechanism to generate an encrypted version of the password, or hash. This way - you are no longer storing passwords "in the clear", so no one can swoop in and steal them.
In short - to circle back to your initial description, the hash IS the encrypted value that gets stored.
You can then compare the new hash(you derive as part of someone logging in) to one you've stored to see if the passwords match. If they don't you might get an error back stating "bad Hash value". (i.e. the password doesn't match the OLD hashcode).
If the encryption method (or "hash function") changes, or any of the various pieces that go into that encryption method, then the hash values change too.
-----------------------------------------
The reason I was asking is that there was a bunch of code samples used a lot in SQL 2000 that relied on the undocumented and unsupported pwdcompare and pwdencrypt functions. The problem often ends up that MS will change what these things do at the drop of a hat, so anything built around them might work under one patch, fail under the next, bomb out under yet another, etc......
So - find out what they're doing, and it should guide you to what the answer is.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 23, 2009 at 5:58 pm
[font="Arial"]Matt,
Thanks!!! That was a very clear and comprehensive description of the idea around a password hash. Honestly, I find it a little easy to discuss SQL on external forums than with my administration staff ... unfortunate ... :hehe: ....
although here's the question - this login was working on a SQL 2005 machine A all too well;for a couple of items around space and performance, the staff decided to move to machine B SQL 2005. After we were notified the migration was complete, we tried logging on to the new box with the old id, which is when we kept getting the error "Login failed for user". To our alert, admin support "looked at the problem" and we were told that there was a bad password hash. It just seems to difficult to explain that if the servers we moved between (A and B) were the exact same SQL level (9.0.3233) why would the hash cause trouble? Aren't there MS scripts that extract the logins with their stored hashes that you just run on the target server you want to have these logins on?[/font]
February 23, 2009 at 6:04 pm
Also, wouldn't/shouldn't the password encryption routines of SQL (at least 2005+) be confidential?
February 23, 2009 at 8:33 pm
That's the thing about a hash function. They're usually functions that even if known are very difficult to reverse. It's not a reversible thing (it's technically not encryption, since the hash value can't be used to recreate the original password).
What may have happened is that the SALT (it's a constant or value that is used by the hash function to confuse the process even more), which is often enough tied to some hardware ID or something internal to the machine, didn't get carried over which means the old hash values didn't work any more. Same if the mastere key for the server weren't the same. I am not up on all of the subtleties, but I have seen those darned errors before.
Still - you may need to on occasion drop and readd logins when and if this should happen.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 23, 2009 at 9:21 pm
This makes sense - so there is a chance that the password hash could have been dependent on the machine ID or something specific to the old box that of course did not get carried over to the new one; but it is strange that this happens for certain migrations and not for certain others - oh well, at least with the insight you have provided, I can explain this for our situation.
Thanks much!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply