Link to home
Start Free TrialLog in
Avatar of sqdperu
sqdperuFlag for United States of America

asked on

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

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

Avatar of achaldave
Flag of United States of America image

Disable option "Enforce password expiration" on SA account.
Avatar of sqdperu


"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.
Avatar of sqdperu
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sqdperu


Because it is the truth and works.