I don't quite follow how it works. According to the MSDN Article there is a big hierarchy of keys protecting other keys and passwords. At some point the database is encrypted. You query the database which is encrypted, and it works seamlessly.

If you're able to simply connect to the database as normal and not have to worry about any of the encryption from a developer point of view, how exactly is it secure? Surely anyone can simply connect and do select * from x and the data is revealed.

Sorry my question is a bit scattered, I am just very confused by the article.


You are right, the point of database encryption is not to protect data from the users of the data base - that is the task of role-based access and privilege levels.

Encryption protects you against someone physically stealing the server from its rack, ripping out the hard disk and then reading the confidential data from the file system. It's a bit more complicated than that - obviously you can't just keep the decryption key lying around, in particular not on the same disk where you store the encrypted DB, otherwise the thief could just decrypt the data - but done right, it can add a functional layer of data security, and security is all about defense in depth.

  • If they took the hard disk out could they not just boot it up, or take the keys/certificates from it? Since you don't enter a password when you connect to the database for the purposes of unencryption. – NibblyPig Nov 5 '13 at 13:29

Yes you can still query select * from x

I have little experience with Transparent Data Encryption, but as the article itself states:

TDE works at the file level, which is similar to two Windows® features: the Encrypting File System (EFS) and BitLocker™ Drive Encryption, the new volume-level encryption introduced in Windows Vista®

It notes that:

TDE does not replace cell-level encryption, EFS, or BitLocker.

It further states:

TDE operates at the I/O level through the buffer pool. Thus, any data that is written into the database file (*.mdf) is encrypted.

To give you an idea of the extensiveness:

Encrypting at the I/O level also allows the snapshots and backups to be encrypted, thus all snapshots and backups created by the database will be encrypted by TDE.

As for cell-encryption:

Cell-level encryption is implemented as a series of built-ins and a key management hierarchy.

It basically invokes an additional en/decryption function for each cell-access, and requires schema modification of the cell's to 'varbinary' and re-casting to the appropriate data type upon reading.

See also:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.