Solved

Resetting Unknown SA password

Posted on 2004-10-29
542 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
    16 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
     
    LVL 21

    Expert Comment

    by:mastoo
    You might look in EM at the logins under security and see if there are any windows accounts that you have access to that are set up as sql server admins.
    0
     

    Author Comment

    by:hapy3sum
    Mastoo,

    Checked every login account in EM and no account has 'sa' roles.  I just asked the same question to Duane but wanted your feedback as well ---- what about reinstalling SQL Server in my situation?  Would SQL Server reattach easily to the databases currently in the RDBMS if I did reinstall?  And if so, what are the steps for it?

    Thanks.
    0
     
    LVL 21

    Expert Comment

    by:mastoo
    Isn't the previous dba's windows account showing up as a server admin (look at the login properties in EM)?  Is so you could have your network/server admin guy reset the dba's windows password and then come in as him.  That would let you be a sql server admin and you could change it to mixed mode security and se up sa as desired.  You are saying that the server is using windows security only and not mixed mode currently?

    But it might be faster to do as you describe.  Detach your databases or back them up, uninstall sql server, install sql server, and then attach or restore your databases.
    0
     

    Author Comment

    by:hapy3sum
    Mastoo,

    I think the problem is that when we moved offices (and changed domains), the Network Admin created an image of the server and recovered it in our SAN here in the new office.  So in essence (at least from my opinion), we broke the connection to the SQL Server db.  Here are some items of interest:

    1)  I cannot see a BUILTIN group that supposedly is created by SQL Server as the OS group that has the SA role (if you are using Windows-based authentication, right?).  So this means that the SA account got deleted, right?

    2)  I tried already what you suggested during the early stages of troubleshooting the problem, but since the SAN is now on a different domain, creating a user account for the old DBA account with the SA role (even if I was doing it with both domain and local admin rights) and using it for the purpose of trying to login as the Old DBA does not work.  

    3)  I can see SOME of the databases and objects underneath when using EM, but I know that I am not coming in as an sa since I cannot even see the properties of each login account, or even create a new user for that matter.

    4)  I tried the SP_PROCEDURE to reset the "sa" account to a new password, but then again I can't since I do not have an "sa role".

    If I were to reinstall, is there a white paper, or would you mind sharing some steps to make sure that I do it properly (aside from making sure to make an O/S backup of the machine as it is now)?

    Thanks so much --- really in dire need of help ASAP.
    0
     
    LVL 21

    Expert Comment

    by:mastoo
    I have to leave for lunch, but my 10 second answer is that I'm sure there are all sorts of tips out there on installing.  I'll oversimplify due to my rush but some main things to consider before starting are backup before you start, how you'll configure licensing, using integrated or mixed security, where to install the databases and transaction log files and backup files, what kind of network protocols you'll use with sql server, might as well install current patch levels while you're at it, collation designator (mainly do you want case sensitive or not), and whether you run sql server and sql agent under the local system account or not (if you're not sure then use local account).  Now for those White Castles...
    0
     

    Author Comment

    by:hapy3sum
    Mastoo,

    I do appreciate your "10-second" answer; when you come back from your "White Castles" lunchbreak (and you have a moment), if you can kindly post a more detailed (and your recommended) resolution to my "bleak" scenario, this would be greatly appreciated.

    Thanks so much!
    0
     
    LVL 6

    Expert Comment

    by:Duane Lawrence
    Short answer
    Yes

    Long answer
    I have detached many databases and copied them to different servers and reatached them with no problem.  Detaching from the current server, uninstalling and then reinstalling should be no problem.  

    I have even detached a MS SQL Server 2000 32 bit database and attached it to a MS SQL Server 2000 64 bit and had no problem, it is still running.  Matter of fact it is in use now.

    I have not attached a 2000 db to a 2005 beta 2 copy.  I must do that, that would be sooo cooool to know.  I will try it tonight at home, and post what happens here.

    Duane
    0
     

    Author Comment

    by:hapy3sum
    Hi Duane,

    While "Mastoo" is on his "White Castles" break, can you recommend in detail any links that I can review on how to reattach SQL Server to existing databases?  

    Thanks.
    0
     
    LVL 21

    Expert Comment

    by:mastoo
    I'm back.  The detach and reattadh is the trivial part if you are going the reinstall route.  You can even just do it from EM by right-clicking on a database to detach (under all tasks), or right click Databases to get to attach under all tasks.  If there's any question that you won't be reinstalling the same version of Sql Server you'd be safer to back up the databases and then just restore them after the reinstall.  Since nobody else has leaped in with an alternate solution I presume you're still headed down the reinstall path?
    0
     

    Author Comment

    by:hapy3sum
    Hi Mastoo,

    Thanks for the feedback (hope the "White Castles" was good).  Yes, as you presumed, I am now headed towards the "reinstall" path.  We are on SQL Server 2000.  

    I was trying to check out some docs and/or links on how to properly (and safely) reinstall and reattach old databases ... do you happen to know of a good doc and/or link that you can pass along to refer to --- will appreciate it very much as I will be doing this first thing Monday AM.

    Thanks a lot!
    0
     
    LVL 21

    Expert Comment

    by:mastoo
    I can't recall seeing one on your situation where you'd really like to just reinstall with exactly the same settings you have now, other than security.

    Disclaimer: if the server has replication going, clustering, log shipping, analysis services, or possibly other things I'm forgetting, you'll want something more than my cursory explaination.

    If you have Sql Agent jobs that you don't want to lose, you need to backup your msdb database and restore it later.

    The key is to capture your configuration info so you'll have it available when you reinstall.  Go to server properties in EM, copy the General tab and the startup parameters and network configuration on that tab, the security tab, the database settings tab, and the server settings tab.  Detach (backing up first might be good) your databases, uninstall, install, look at the server properties to be sure they match, and then attach or restore your databases.  Magic.  Be sure you get the right collation on the install (this is on the general tab that you copied).
    0
     

    Author Comment

    by:hapy3sum
    Mastoo,

    Other than authentication (I am going to go mixed mode), I will most likely reinstall with the same settings as before.

    Other items of interest after your last feedback:

    1)  No server replication, clustering, log shipping or analysis services going on.

    2)  I do have some SQL Agent jobs running - my question then is, how can I backup my db and restore it later when I don't have sysadmin-type access?

    3)  Can I detach a database I do not have sysadmin-type access to?

    4)  Will I be able to attach or restore my databases, again, if I did not have sysadmin-type access to those databases before ?

    Thanks so much and will await your much-appreciated reply!

    v/r

    Hapy3sum
    0
     
    LVL 21

    Accepted Solution

    by:
    1) Good.

    2) If there are just a couple jobs, you might just look at their properties and reproduce them by hand when everything is done.  Otherwise you backup and restore the msdb to get those jobs.  You may not have server admin priviledge but hopefully you can backup.  If not, your previous dba will have had regularly scheduled backup jobs running.  You can look at the backup jobs to see where they are going, make sure there's a recent one or kick off the job to get a new backup.

    3) Possibly, but as in #2 you can just do a backup.  Attaching just saves you from waiting for the backup/restore.

    4) Yes because you'll be a server admin when you do the restore.

    So worst case you just end up reinstalling and then restoring from recent backups - and it's unthinkable that there wouldn't be current backups.
    0
     

    Author Comment

    by:hapy3sum
    Mastoo,

    I will go ahead and do as suggested ... we have the old image of our SQL Server box, so this is our backup for now.  Since I am not logging in with "sa"-type rights, I could only start or stop jobs but cannot really edit or see it.

    Thanks to you and Duane for your feedback, and will let you both know how this goes.

    Hapy3sum
    0
     
    LVL 21

    Expert Comment

    by:mastoo
    It'd be nice if you have backups or can detach databases first.  These you can easily restore.

    If not, it would be preferable to take a image with sql server stopped because you end up having to trick sql server into using the databases by copying the files.  It usually works but you don't want to have an image where sql server happened to be in the middle of updating a file.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Prepare to Pass the CompTIA A+ 900 Series Exam

    CompTIA aims to adapt its A+ Certification to reflect the most current knowledge and skills needed by today's IT professionals--and this year's 2016 exam is harder than ever. This certification is one of the most highly-respected and sought after in IT.

    Suggested Solutions

    By Mark Wills We often hear about Fragmentation, and generally have an idea that it is about broken bits, or bad for performance, or at least, is generally not a good thing. But what does it really mean ? By way of analogy, think of the humbl…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    933 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

    24 Experts available now in Live!

    Get 1:1 Help Now