Upgrade to 2008 from 2005 failed due to sa account being renamed

Question

Post reply

Upgrade to 2008 from 2005 failed due to sa account being renamed

sree-879715

SSCommitted

Points: 1794
More actions
June 22, 2010 at 1:51 pm

#145734

Hi all,
I was upgrading one of the named instance of SQL 2005 on my test box and it failed due to the sa account being named to something else.
I did not know this until I checked the error in microsoft website that if sa account being renamed to something else, the upgrade will fail.
now I even got the solution about how to resolve this issue.
http://support.microsoft.com/kb/960781
But, I dont know how to start a named instance through command propmt and renamed the sa account back for that particular instance.
Please help me.
Thanks in advance
Sree

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Dugi SSCoach Points: 17998 More actions · Answer 1

You can start a name instance from command prompt like this:

net start <InstanceName>

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

sree-879715 SSCommitted Points: 1794 More actions · Answer 2

Thank you for your reply !!

I was able to start the named instance but now I am not able to alter the login name

I mean I getting error while running this :

sqlcmd -E -A -Q"alter login sysdba1 with name = sa"

where sysdba1 is the name to which sa has been renamed.

Dugi SSCoach Points: 17998 More actions · Answer 3

sree-879715 (6/22/2010)
Thank you for your reply !!
I was able to start the named instance but now I am not able to alter the login name
I mean I getting error while running this :
sqlcmd -E -A -Q"alter login sysdba1 with name = sa"
where sysdba1 is the name to which sa has been renamed.

Check this location for you problem....!

You must do more things after you run the sqlcmd command, for example shutdown the SQL Server, then run the feature of setup through the SQL Server Installation Center...etc!

Check all the details at the link above.

Then let me know what happened!?

😉

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

sree-879715 SSCommitted Points: 1794 More actions · Answer 4

Thank you for the reply

I checked everything and tried again but not able to run the script to alter the sa login name

Please help me in getting tht in correct way.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sree-879715 (6/23/2010)
I checked everything and tried again but not able to run the script to alter the sa login name

Why not?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sree-879715 SSCommitted Points: 1794 More actions · Answer 6

when I am trying to rename the sa account back using this command :

sqlcmd -E -A -Q"alter login sysdba1 with name = sa"

getting error as incorrect syntax near login

Not sure what I am missing ..please help me in resolving this

Richard M. SSCertifiable Points: 7401 More actions · Answer 7

Maybe try:

sqlcmd -E -A -Q"alter login [sysdba1] with name = sa;"

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

sree-879715 SSCommitted Points: 1794 More actions · Answer 8

I tried even this but same error..incorrect syntax near login

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Maybe try it in two parts. First sqlcmd -E -A -Q to run SQLCMD and log in, then, as a second step, run the alter login statement

alter login [sysdba1] with name = sa;

GO

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sree-879715 SSCommitted Points: 1794 More actions · Answer 10

sree-879715

SSCommitted

Points: 1794

June 23, 2010 at 12:20 pm

#1184139

I tried this even...but nup same kind of errors

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Ok, log into SQLCMD and run the following please

SELECT @@Version;

GO

What's the result?

Do note that with the SQLCMD switches you have, you're connecting to the default instance on the machine you're running that on. Is that where you want to connect to?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sree-879715 SSCommitted Points: 1794 More actions · Answer 12

We have one default and 4 named instance on that machine and I am trying to upgrade one of the named instance. I think I am doing something wrong at this stage, if so

Please help me in connecting to my named instance and run that alter script to rename the sa account.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Yup, you're connecting to the default instance. Check the SQLCMD help and fix the settings to connect to the desired named instance. The alter login script is correct.

http://msdn.microsoft.com/en-us/library/ms162773.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sree-879715 SSCommitted Points: 1794 More actions · Answer 14

Thank you very much for your help.

I finished the upgrade successfully

The commands I used to resolve this issue are :

net start $MSSQLINSTANCENAME

sqlcmd -S servername\instancename -E -A -Q"alter login <sys-admin> with name=sa"

net stop MSSQL$INSTANCENAME

Thats all

Thank you once agian