Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Resetting Unknown SA Password

Posted on 2004-10-29
6
Medium Priority
?
857 Views
Last Modified: 2012-06-21
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.

0
Comment
Question by:hapy3sum
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 6

Expert Comment

by:Duane Lawrence
ID: 12444752
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
 

Author Comment

by:hapy3sum
ID: 12445268
Hi Duane,

Thanks for your input.  What about reinstalling SQL Server as an option?
0
 
LVL 2

Expert Comment

by:jerryscole
ID: 12449114
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Accepted Solution

by:
Duane Lawrence earned 1000 total points
ID: 12450414
Yes, you can re-install SQL server.  That will allow you to assign the SA password.

Duaen
0
 
LVL 1

Expert Comment

by:anugrah
ID: 12460972
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
 
LVL 2

Expert Comment

by:jerryscole
ID: 12505880
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question