In-Place upgrade from SQL 2000 to SQL 2008 R2 - sa password no longer valid ?!?

sqdperu used Ask the Experts™
Starting on a Windows Server 2003 Std. / SQL Server 2000 Std.  I login and ran queries with 'sa' account just fine.  I do an in-place upgrade on the machine to SQL Server 2008 R2 std.  

Now going to log into MSSM (on the physical server) for Database Engine.  If I use Windows Authentication, (populates user name with servername\Administrator and no password automatically and cannot be changed) it logs in fine.  However, if I use SQL Server Authentication with user name 'sa' and the same password that worked before the upgrade, I get "Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456).  If I check the log it also says "Reason: Password did not match that for the login provided. [CLIENT: local machine], Severity: 14, State 8".

I checked the Security\Logins\Properties\Status of 'sa' and the "Permission to connect to database engine" has "Grant" selected and Login is "Enabled".  The Server Roles page has "sysadmin" checked.

I checked the SQL Server\Properties\Security and Server Authentication is set to "SQL Server and Windows Authentication mode".

So to test from a different machine I installed the MSSM basic on a PC and tried to connect with 'sa'.  I get the same login failed.  From the PC, I tried to connnect to a different SQL user account and WAS able to connect.

Why am I unable to connect with my "sa" login?  Did the upgrade change the password to my "sa" account?  

Do I need to do something different during the upgrade to is does not change the password on "sa" or do I have to some how fix it after the upgrade to get it back to the original password (and exactly what is the best method)?  (I have I many scripts with this password in them and don't want to change them.)

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Disable option "Enforce password expiration" on SA account.


"Enforce password expiration" by default on my "sa" account was not checked.

Did you mean "Enforce password policy"?  It is checked by default.

I was able to go into MSSM Security\Logins\ sa account and right-click Properties and change the password to what it was before the upgrade (and in my opinion what it should have remained).  Then I was able to login as sa from the server itself and from a remote PC.

Would "Enforce password policy" being checked have caused my password to change during upgrade?

My password is composed of 6 characters, all lowercase letters and numbers.  It did not complain when I "changed" the password to what it was before the upgrade.
I have not tested this yet, but believe I have figure out the problem (by trying to log into 2008 with 2000 Query Analyzer and running scripts).  

My sa password in 2000 contains one upper case letter.  Over time signing into 2000 sa account everyday I had apparently stopped holding down the shift key to get the upper case letter in the password.  Well 2000 is not case sensative - it will take it with or without.  However 2008 is case sensative and rejected the password without the upper case.  So I thought I was changing the sa password to the same thing, but was really changing it to all lower case so then it worked when I logged in after the change.


Because it is the truth and works.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial