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.
Could someone help me understand SQL TDE Database encryption?
sql server;encryption
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.