How to purposely corrupt a master database

  • mariabowman

    SSC Rookie

    Points: 33

    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.

  • crow1969

    SSCrazy

    Points: 2728

    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 3 days, 13 hours ago by  crow1969. Reason: Removed double negative making the post look like second grade gibberish
  • mariabowman

    SSC Rookie

    Points: 33

    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.

  • Eric M Russell

    SSC Guru

    Points: 124933

    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. Seek What They Sought." - Matsuo Basho

Viewing 4 posts - 1 through 4 (of 4 total)

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