Resetting Unknown SA Password

New to SQL Server and coming in as the new DBA (w/an Oracle background).  

Previous DBA used Windows authentication in the old office location.  When our office moved to a new location, the Network Admin backed up the image of SQL Server 2000 from an old machine to a new server when we transferred to the new location.  The Network Admin gave me local as well as domain admin rights to login to the new instance of SQL Server on the new server.  Logging in, I was able to register the SQL Services and can access and see some of the databases running on SQL Server, but I am obviously not coming in as a SYSADMIN.  The problem is, I cannot even go to the registry and change my authentication from Windows-based to Mixed mode since I do not know what the SA password is (the previous DBA cannot be reached and nobody here knows what the SA password is).  So, what other options do I have?

Appreciate the expert help ASAP as I need to start managing our databases and I cannot even get in as an "sa" to do my job.

hapy3sumAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Duane LawrenceCommented:
When you apply a patch, it will ask for your sa password, just type in what you want it to be.

IF I HAD A DIME FOR EVERY TIME THIS BIT ME:
A word of warning, when you change it, anything that is hard coded to use the old sa password will stop working.  You will have to run around in emergency repair mode for a while.  Not a comfortable situation.  A better way would be to get the source code for everything that uses the db and search for the string 'sa', then read the password.

Duane
0
hapy3sumAuthor Commented:
Hi Duane,

Thanks for your input.  What about reinstalling SQL Server as an option?
0
jerryscoleCommented:
Here's another way I have successfully used...

Login to the SQL Server box as the Administrator.
Run Enterprise Manager, right click the server name and choose 'Edit SQL Server Registration properties'.
Choose 'Use Windows authentication' and click OK button, expand a server, expand a Security and click Logins.
Double-click the sa login and specify new password on the General tab.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Duane LawrenceCommented:
Yes, you can re-install SQL server.  That will allow you to assign the SA password.

Duaen
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anugrahCommented:
hi there ,

i tested the solution given by jerryscole,

but as soon as i edit the "sqlserver registration properties" to windows authentication

an error comes on screen saying
connection could not be established with reason " Can not generate SSPI ontext"

dear jerryscole can you expalin what exactly is this happening .

thanks in advance


0
jerryscoleCommented:
Good question. First thing I would check is to see if the SQL Server "service" is running under the LocalSystem account. If it is running under a domain account, you can get this error. Unless your security policies prohibit it, I would run the service under the LocalSystem account.

You can get more information at this link http://support.microsoft.com/default.aspx?scid=kb;en-us;811889
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.