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).

pb648174 (google@webpaul.net) writes:
> 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).

What about upper/lowercase? I seem to recall that in SQL 2000, the
password is case-insensitive if the system collation is case-insensitive,
whereas on SQL 2005 it's always case-sensitive.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

No, the password was all lower case in both sql server and the
connection string. I deleted and re-added different users with
different names and passwords, painstakingly trying different things
over the course of a few hours.

pb648174 (google@webpaul.net) writes:
> No, the password was all lower case in both sql server and the
> connection string. I deleted and re-added different users with
> different names and passwords, painstakingly trying different things
> over the course of a few hours.

And only lowercase letters? That is, no special characters or anything
else?

I'm sorry, I have no idea of what might be going on.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx