StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You have two alternatives:</p> <ol> <li><p>Cryptographic control. With this only the users that know the password can decrypt the data. The downside is that the user <strong>must enter</strong> the decryption password each time they access the data. A Report woul have to contain a Password parameter that the user running the report fills with the data access password. Application must request the password from the user. Web sites must request password from visitor. And so on and so forth</p></li> <li><p>Access control. The data is encrypted with a key that SQL Server itself has access to (ultimately the ecnryption chain goes all the way up to the Service Master Key and this is encrypted using DPAPI). This gives you no greater protection other that granting and denying SELECT would give you: is <strong>access</strong> control, not cryptographic control. Such a scheme protects solely against accidental loss of media (somebody finds a disk with your database, or you loose a laptop with the database on it). You can achieve the very same using <a href="http://msdn.microsoft.com/en-us/library/bb934049.aspx" rel="noreferrer">Transparent Data Encryption</a> or file level encryption (<a href="http://technet.microsoft.com/en-us/windows/aa905065.aspx" rel="noreferrer">BitLocker</a>).</p></li> </ol> <p>The common data encryption scenario is to encrypt the data with a symmetric key, and then encryt the symmetric key with an asymmetric key (usually the private key of a certificate). The asymmetric key is then in turn encrypted with a password, and this password must be presented when trying to access data. The primary reason for this two level indirection is password change: when a password or a private key is compromissed the symmetric key is re-encrypted with a different asymmetric key or the asymmetric key is re-encrypted with a different password. This way the access password has changed <em>without requiring a re-encryption of all the data</em>. If the access would be granted directly to the symmetric key then a password compromise would possibly require to re-encrypt all the <em>data</em>, possible terabytes of data.</p> <p>Where the two scenarios I presented differ is whether the asymmetric key is also ecnrypted with the database master key or not. Case 1) it isn't, case 2) it is. This is all explained in <a href="http://msdn.microsoft.com/en-us/library/ms189586.aspx" rel="noreferrer">Encryption Hierarchy</a>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload