Solved

Resetting Unknown SA Password

Posted on 2004-10-29
845 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
Question by:hapy3sum
    6 Comments
     
    LVL 6

    Expert Comment

    by:Duane Lawrence
    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
    Hi Duane,

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

    Expert Comment

    by:jerryscole
    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
     
    LVL 6

    Accepted Solution

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

    Duaen
    0
     
    LVL 1

    Expert Comment

    by:anugrah
    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
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
    This article describes some very basic things about SQL Server filegroups.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    877 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now