locked
Post-2008-upgrade: "Password did not match that for the login provided" from certain programs RRS feed

  • Question

  • We upgraded to SQL Server 2008 (from 2000) over the weekend, and we've been pulling our hair out over this issue since then. Some of the SQL logins on the server refuse to authenticate, and the problem seems to arise only when using certain programs (possibly just ODBC-based) - for instance, I was able to connect with Management Studio, but not with our ERP application, using the same login. Without fail, I see this in the error log:

    Login failed for user 'xxxx'. Reason: Password did not match that for the login provided. [CLIENT: x.x.x.x]
    We've tried resetting passwords, which has no effect. In some cases, completely deleting the login and all associated database users, then recreating said login and users has resolved the problem, but this hasn't worked for all the problem logins. The choice of ODBC provider doesn't seem to have any effect. I've tried with and without "Enforce password policy" enabled for the logins, as we're running on Windows Server 2003.

    Is there some new detail of SQL logins that I've completely missed?
    Thursday, September 24, 2009 2:04 PM

Answers

  •   The root cause of the problem may be a change of behavior introduced in SQL Server 2005. In SQL Server 2000, passwords were case-insensitive, but since SQL Server 2005, all passwords are case-sensitive.

      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, September 25, 2009 1:08 AM

All replies

  •   The root cause of the problem may be a change of behavior introduced in SQL Server 2005. In SQL Server 2000, passwords were case-insensitive, but since SQL Server 2005, all passwords are case-sensitive.

      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, September 25, 2009 1:08 AM
  • Wow, yeah, that might be causing some of it. Can't believe I never knew that little detail about 2000! Although that still wouldn't explain the ones that couldn't log in even after a password reset - we had to delete and recreate those logins.

    I'll try to do a little more investigating today.
    Friday, September 25, 2009 11:00 AM
  • Hi all,

    i have the same problem but I have checked the case - sensitive for password and I have not any problem on this.

    If I will try to verify the database from the designer (Crystal Report XI R2) using OLEDB(ADO) on a server SQL2005, the crystal is opening from the ERP program.
    If I will try to verify the database from the designer  on a server SQL2008 the crystal is not opening from the ERP program and on the Profiler exists the following error.

    Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: x.x.x.x]

    any help???
    Friday, November 6, 2009 4:31 PM
  • After a great deal of hair-pulling and teeth-gnashing, I think we have the issue resolved. The problem was two-fold: first, there was the case-sensitivity problem mentioned earlier. The other half of the problem, which I didn't uncover until spending a fair amount of time looking at Wireshark packet dumps, appeared to be related to certificate negotiation.

    About two years ago, when we reorganized our domain, we also renamed a lot of our servers - including the one hosting SQL Server. At the time, it was running SQL Server 2000. I made sure sysservers reflected the new name of the machine, as some SQL Agent stuff that I can't recall right now was failing. Of course, we didn't want to go around to a hundred workstations updating connection strings in ODBC, and various other applications, so we threw an entry into DNS redirecting the old name to the same server. Everything worked fine, and we didn't think about it much afterward.


    So now after upgrading to 2008, things start blowing up. From digging through the packet dumps, I could tell there was some kind of self-signed certificate being sent out from SQL Server, and of course it was using the current server name. On a hunch, I went to one of the machines that couldn't connect via ODBC, and of course it was using the old server name in DNS to connect. I switched it to the current server name, and presto, the problem totally disappeared.

    If you're still having issues, make sure the workstations are using the actual server name to connect, and not some other name stored in DNS, or the IP address of the server. We switched to just using the NetBIOS machine name, and not the FQDN, so you can experiment with both setups. Also, make sure SQL Server thinks it has the correct server name - check sys.servers. If you haven't renamed the computer since installing SQL Server, you shouldn't have to worry about that.
    Saturday, November 7, 2009 12:24 AM
  • Really? I thought it was only Oracle that had that sort of crazyness ;)
    Oh well, yet another reason no one should ever, EVER use SQL 2000 anymore! :D
    Sunday, November 8, 2009 5:17 PM