Avatar of sqdperu
Flag 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.)

Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment

8/22/2022 - Mon

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.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Because it is the truth and works.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes