I recently needed to upgrade a sql 2000 machine to 2005. Instead of
upgrading the sql 2000 default instance, I installed sql 2005 as a
separate named instance so we could go back to sql 2000 if necessary.
The web server that connected to the new server didn't have anything
change on it except the database ip was changed to
machinename\instancename instead of just machinename. SQL
authentication was being used and the exact same userid/password was
created on sql 2005. I logged into the database server using the same
userid/pass to confirm it worked.
However, when using the app, it reported "Login failure for: username"
error and I confirmed 600 times over the next two hours that it was in
fact correctly typed in. The "State" in the sql server log indicated a
password mismatch, but again, it was correct and I verified it worked
using the sql 2005 management studio from the web server itself.
Finally I had the bright idea of making the password blank, figuring it
was getting mangled somewhere in the process...
That worked. So my question is, what is going on and how can I set a
normal password? There is no encryption of the connection string and
nothing changed in the web app between converting the machine from 2000
(worked fine) to 2005 (password mismatch).
