troubleshooting Question

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

Avatar of sqdperu
sqdperuFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
4 Comments1 Solution563 ViewsLast Modified:
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.)

Thanks
ASKER CERTIFIED SOLUTION
sqdperu
Application Development Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros