locked
Is it possible to access SQL Server's buffer pool directly? RRS feed

  • Question

  • My company is planning to implement TDE on some databases that contain very sensitive data and I want to be ready to explain all the places where data could still be exposed even though it's encrypted on disk.  TDE should protect the data at rest. I know I'm also going to have to encrypt all network traffic, but one other thought came to mind. What about the buffer pool? I have 2 questions about that.

    1. If a malicious user has access to the SQL Server host, can they peek at SQL's buffer cache to steal unencrypted data? What level of access would they need to be able to do this?
    2. If the server core dumps, will that dump file contain what was in SQL's buffer cache? Will encrypting the filesystem or folder where the dump gets written to close this hole?



    Chuck

    Tuesday, August 7, 2012 12:38 PM

Answers

  • Some aspects of data exposure of TDE data, with highlighting of issues that you ask about.

    From: http://msdn.microsoft.com/en-us/library/cc278098(SQL.100).aspx

    When the encryption scan is completed, the DEK state is set to the Encrypted state. At this point all database files on disk are encrypted and database and log file writes to disk will be encrypted.

    For data that is in use, all pages are decrypted as they are read and stored into the buffer pool and are in clear text in memory. The operating system may page data out of memory as part of memory management. In this process, decrypted data may be written to disk. Windows and SQL Server can be configured to prevent memory from being paged to disk, although the performance cost of this can be high. Other OS actions such as hibernation and crash dumps can also cause memory to be written to disk. Data in transit is not protected because the information is already decrypted before it reaches this point; SSL should be enabled to protect communication between the server and any clients.

    If someone gets enough rights to cause a dump to happen, they could capture some memory that you want protected.  And, of course, if someone gets administrative privileges they can examine all of memory.

    FWIW,
    RLF

    • Marked as answer by chuckh1958 Tuesday, August 7, 2012 6:55 PM
    Tuesday, August 7, 2012 5:49 PM

All replies

  • Some aspects of data exposure of TDE data, with highlighting of issues that you ask about.

    From: http://msdn.microsoft.com/en-us/library/cc278098(SQL.100).aspx

    When the encryption scan is completed, the DEK state is set to the Encrypted state. At this point all database files on disk are encrypted and database and log file writes to disk will be encrypted.

    For data that is in use, all pages are decrypted as they are read and stored into the buffer pool and are in clear text in memory. The operating system may page data out of memory as part of memory management. In this process, decrypted data may be written to disk. Windows and SQL Server can be configured to prevent memory from being paged to disk, although the performance cost of this can be high. Other OS actions such as hibernation and crash dumps can also cause memory to be written to disk. Data in transit is not protected because the information is already decrypted before it reaches this point; SSL should be enabled to protect communication between the server and any clients.

    If someone gets enough rights to cause a dump to happen, they could capture some memory that you want protected.  And, of course, if someone gets administrative privileges they can examine all of memory.

    FWIW,
    RLF

    • Marked as answer by chuckh1958 Tuesday, August 7, 2012 6:55 PM
    Tuesday, August 7, 2012 5:49 PM
  • Good questions, and good answers.

    Security must always be done in depth.

    Windows is nothing like a secure environment.

    You can have no idea what a malicious user can do with remote or physical access to the server.

    If the data is really that critical, use all the security Windows and SQL Server offer, but make sure your operational and physical security is also done by best practices.

    Josh

    Tuesday, August 7, 2012 6:31 PM
  • I don't think any computer environment is completely secure.  An environment is only as secure as the the people configuring and using it. Linux'ers used to think they were somehow inherently secure until someone got a compromised ssh package on a major distribution's repository a few years ago. They suddenly realized how vulnerable they were too.

    I think there are some that believe that data encryption solves all security problems. I dont believe that at all.

    I'm trying to weigh the benefits vs. the costs of TDE for a customer. Personally I see very little benefit from TDE, but a lot on the cost side of the equation. Particularly the extra space consumed by backups and the network bandwidth needed to move them around (we use log shipping as a foundation of off-site backups). With TDE my testing has shown that compressed  backups are about double the size - including log backups.

    What are the benefits? 

    Securing the data at the file level? Anyone with access to the SQL Server can query the data. That's the nature of it being "transparent". Everyone else is kept at bay by firewalls, SQL permissions, and O/S permissions. Physical access to the drives and servers is handled by locked doors and guards at the datacenter. I see little benefit of TDE here.

    Securing the backups? They can be secured without TDE by the backup devices and software. There's little benefit of TDE here either.

    About the only scenario I can think of is an insider - someone with administrative access to the disk -  clones it and walks off with the clone. Its unusable without the server's certificate. If you've got people with that level of access that you can't trust though, you've got bigger problems than wondering whether to use TDE or not.

    Is there some big advantage that I'm missing?



    Chuck





    • Edited by chuckh1958 Tuesday, August 7, 2012 7:04 PM
    Tuesday, August 7, 2012 6:55 PM
  • Well, (clearing throat) I don't use TDE either. 

    I can see it as something you might want to do for a hosted environment where you fear the amount of control over the backups.  But, yes, it does not protect you from someone with rights to select the data.  If you fear physical media exposure (and it certainly can happen) then consider it.

    Also, you might find the latter part of this interesting:  http://www.sqlservercentral.com/articles/Transparent+Data+Encryption/66334/

    FWIW,
    RLF

    Tuesday, August 7, 2012 7:13 PM
  • Securing the backups? They can be secured without TDE by the backup devices and software. There's little benefit of TDE here either.

    ...

    Is there some big advantage that I'm missing?

    Just so you have the primal case blocked: someone takes a backup of your database to a thumb drive, then restores it on another server and has all your data - then you're at least in the game!  TDE does that.  If you have alternate encryption processes on your devices, then that could be OK.  The other is to steal the raw blocks of data from disk files, log files, even tempdb.  It looks like TDE blocks those.

    But if it doubles the size of compressed backups - that's an interesting factoid, thanks.

    There's never infinite security, you look at the value of the data, you get a budget for security, and when that's used up you hope for the best.

    Josh

    Tuesday, August 7, 2012 7:27 PM
  • I think that case is covered by limiting access to the SQL server, the host, and the file server that contains the backups.

    Chuck

    Wednesday, August 8, 2012 2:24 PM
  • Security in depth!  Even if a "guest" gets into the room and is left alone for a few minutes, you want it so he can't easily steal anything.

    Just yesterday we had someone here in SoCal sneak into a hospital, talk the mother into taking a shower, then grabbed her newborn and stuffed it in a sack and tried to walk out of the hospital with it.  Luckily the baby had on an ID bracelet with some kind of rfid and they grabbed the perp, baby OK.

    No perfect security, but lots of little things in random places add up to more effectiveness.

    Josh

    Wednesday, August 8, 2012 6:15 PM