locked
Security and tempdb RRS feed

  • Question

  • Hello,
    currently I am trying to identify risks and confidentiality of data on some of our SQL servers. We have Dell poweredge servers attached to Dell Powervault arrays. All the data we hold on our SQL servers is highly confidential.
    Basically I need to know a few things.
    Consider the scenario that somebody has broken into the building and stolen a poweredge server but left the array. All they would be getting at (data wise) is the operating system and tempdb which is stored on a RAID1 inside the poweredge. All the SQL databases are stored on RAID on the Powervault (array).

    Can anybody extract ANY information at all if they only had tempdb to look at? Will it be useless data?

    If you need anymore information, please let me know.

    Kind Regards,

    Ded
    You can't dangle the bogus carrot of possible reconciliation in front of me whilst riding some other donkey
    Thursday, February 18, 2010 5:16 PM

Answers

  • Ded,

    TempDB is a special case as it is re-created everytime SQL Server starts up. I was curious about this and did a VERY QUICK test. I don't have RedGate or Apex on my computer so I could use those to test, however I assumed the following cases:

    1. Server was shut down nicely (Clean).
         a. Server was started back up nicely - SQL Server starts up.
         b. Server was started back up and fails - SQL Server does NOT startup.
    2. Server's power was pulled (Dirty).
         a. Server was started up nicely - SQL Server starts up.
         b. Server was started up and fails - SQL Server does not start up.
    3. Drives are taken out and attached in a read only mode to another raid controller using forensic tools.

    In 1.a and 2.a, when SQL Server starts up TempDB will be cleared, this includes the version store. In 1.b and 2.b SQL Server fails to start and there could potentially be data. In 3, the drives aren't touched per se and all data at the time of power plug pulling could be available.

    I attempted 1.b and 2.b with a test system I have. I would create a couple global temp tables and copy tempdb over after simulating a failure (I would use sysinternals to kill the sqlserver process and copy the tempdb mdf and ldf files to another directory). I did not insert any data into the version store. All data inserted was ascii character data, not unicode.

    After the copy I would open up the copied TempDB files using a hex editor and would search for the ascii and unicode versions of text that I put in using partial matching. I could not find any data that was inserted.

    Please do not use this as the definitive proff, I was just curious and wanted to test this case. If anyone knows I am interested in the final answer to this.

    Hope this helps,
    Sean
    Thursday, February 18, 2010 5:49 PM

All replies

  • Ded,

    TempDB is a special case as it is re-created everytime SQL Server starts up. I was curious about this and did a VERY QUICK test. I don't have RedGate or Apex on my computer so I could use those to test, however I assumed the following cases:

    1. Server was shut down nicely (Clean).
         a. Server was started back up nicely - SQL Server starts up.
         b. Server was started back up and fails - SQL Server does NOT startup.
    2. Server's power was pulled (Dirty).
         a. Server was started up nicely - SQL Server starts up.
         b. Server was started up and fails - SQL Server does not start up.
    3. Drives are taken out and attached in a read only mode to another raid controller using forensic tools.

    In 1.a and 2.a, when SQL Server starts up TempDB will be cleared, this includes the version store. In 1.b and 2.b SQL Server fails to start and there could potentially be data. In 3, the drives aren't touched per se and all data at the time of power plug pulling could be available.

    I attempted 1.b and 2.b with a test system I have. I would create a couple global temp tables and copy tempdb over after simulating a failure (I would use sysinternals to kill the sqlserver process and copy the tempdb mdf and ldf files to another directory). I did not insert any data into the version store. All data inserted was ascii character data, not unicode.

    After the copy I would open up the copied TempDB files using a hex editor and would search for the ascii and unicode versions of text that I put in using partial matching. I could not find any data that was inserted.

    Please do not use this as the definitive proff, I was just curious and wanted to test this case. If anyone knows I am interested in the final answer to this.

    Hope this helps,
    Sean
    Thursday, February 18, 2010 5:49 PM
  • tempdb is an important database that is overlooked. It could potentially carry the sensitive data. So that is the reason when you start to use encryption on any user database SQL server automatically encrypts the tempdb. Even though there are other non encrypted  databases are co-existence. So, that tells something.
    Wednesday, August 31, 2016 5:44 PM
  • tempdb is an important database that is overlooked. It could potentially carry the sensitive data. So that is the reason when you start to use encryption on any user database SQL server automatically encrypts the tempdb. Even though there are other non encrypted  databases are co-existence. So, that tells something.
    Make a note its an very old thread .

    Regards, S_NO "_"

    Wednesday, August 31, 2016 5:59 PM