Post reply

How to purposely corrupt a master database

  • May 15, 2019 at 4:42 pm

    #3643967

    I am in discussions with the other DBA's I work with.  They want to disable the sa account.  They said they could restore with our AD group.  I am trying to make a point and need some assistance.  I have  created a DR server and want to corrupt the master database and then recover it.  I need to show if they are correct or if I am correct about it needing the SA account and password.  If you disable the SA account then your master database is corrupted will it know who you are when attempting to recover?  My experience says you need the SA account and password for the master database to be recovered.  Can someone help me corrupt the master database please.  I can't believe after almost 30 years I now need to corrupt a database.

     

    Thank you in advance for any assistance you can give me on this topic.

  • May 15, 2019 at 5:24 pm

    #3643978

    I usually use a hex editor for this sort of thing.  In general the process would be

    1. Shut down SQL Server
    2. Copy MasterData.mdf and MasterLog.ldf somewhere safe
    3. Edit the MasterData.mdf file with the hex editor (you have to go in more than the first couple of pages, as I recall).  Use DBCC PAGE to find a pointer to the next or previous page on the page, and blast it (you DO have a backup, right?  RIGHT?)
    4. Start SQL Server, and watch it (probably) fail.

    When you rebuild the master database, however, you will likely only need to be a member of the local administrators group, as a new master database has to be built.  Then you need to restore your backup of master (and msdb and model) over the rebuilt copy(ies).  I don't believe the sa login is actually used in any part of that process.  I have fortunately not had to do a hard DR like that in many years, now.

    • This reply was modified 4 years, 12 months ago by  crow1969. Reason: Removed double negative making the post look like second grade gibberish

  • May 15, 2019 at 5:52 pm

    #3643986

    Thank you for the quick response.  After reading your suggestion it occurred to me I can use anything to open up the file and corrupt it.  So I used word and removed the first 10 characters.  Saved file and attempt to restart my services.  I got an error of unable to start service MSSQL$TEST on server blah(mscorlib).  Good enough for what I am trying to attempt I think.  Will let you know if this works or not.

  • May 15, 2019 at 8:18 pm

    #3644026

    I've never done this before, but assuming a scenario where you've lost the MASTER database, have no good backups, but do still have good user database (MDF) files, it may be possible to re-attach the database. There is a special FOR ATTACH clause of the CREATE DATABASE statement that is intended to re-create the database from the MDF. The ATTACH_REBUILD_LOG clause will also rebuild the transaction log (LDF) file.

    http://sqlism.blogspot.com/2014/09/restoring-orphaned-mdf-file-in-sql.html

    However, if you have transparent database encryption (TDE) enabled on the database, the above is not an option, because essential encryption keys are contained in the master database. TDE is intended to prevent this type of database recovery in the event that a thief steals your database server box or gains remote access to the storage.

     

     

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • May 24, 2019 at 11:50 am

    #3646840

    I have completed my testing and here is my findings.  First you can corrupt the master database with wordpad.  Just open it up and remove the first line.  I found that you can have the SA account disabled and still rebuild your master database as long as your password is valid.  If you have ERPM to store your passwords it will go out even when the SA account is disabled and change it.  As long as you can get the current password you can do the recovery.  You also need your sysadmin AD account in order to rebuild the master.  Always ensure you have a nightly backup as good practice.  Then you have to put the database engine in single user mode.  From the command line run

    Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=<your instance> /SQLSYSADMINACCOUNTS=<your ad group> /SAPWD=your sa password.

    I found if you have spaces in your AD group name you will need to put double quotes around the whole account so the rebuild process can read it.  Then after that bring up your database in single user mode and open a sqlcmd line and restore database master from disk="your backup location" with replace GO.  It is a little tricky getting the sqlcmd to connect when you are in single mode.  Just keep restarting the services until you can get a connection going.

    Thank you all for your input.

    Maria

  • May 24, 2019 at 12:36 pm

    #3646919

    Very cool testing (especially with the ancillary tests) and feedback, Maria.  As has been said "One GOOD test is worth a thousand expert opinions" (Werner von Braun).  I've not attempted to repeat your experiment but you've provided enough information so that someone could.  Very nice done and I've bookmarked this page for some future testing.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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