Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
WaterWolf
Starting Member
24 Posts |
 Posted - 2012-01-03 : 11:22:17
|
| I've been looking at various sites about encrypting a column in sql server. They all seem to be saying pretty much the same kind of thing:[url]http://msdn.microsoft.com/en-us/library/ms179331.aspx[/url][url]http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/[/url][url]http://www.4guysfromrolla.com/articles/022107-1.aspx[/url]So I have implemented two stored procedures which can be accessed from my website, one that encrypts and updates a column and the other that decrypts and reads from the column. This works fine and would protect against a situation where an unauthorised person gets hold of the database files.However what happens in the event that an unauthorised person gets access to the database user associated with the website, via a sql injection attack or by other means? Surely they can just call the stored procedure and unencrypt the data. Is there anyway to protect against that?On a side note, the password used to generate the database master key doesn't appear to ever be referenced again. If I'm deploying the same schema many times on different sites, would it be better just to set this password to some randomly generated string in the creation script? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-03 : 12:11:04
|
quote:
Originally posted by WaterWolf
So I have implemented two stored procedures which can be accessed from my website, one that encrypts and updates a column and the other that decrypts and reads from the column. This works fine and would protect against a situation where an unauthorised person gets hold of the database files.
No it doesn't. Unless you're encrypting with passphrase (which the rest of the post implies you're not), anyone getting hold of the database files can restore/attach them to an instance they are sysadmin of and gain full permissions on all of the keys, including permission to open them and hence decrypt the data.Column encryption protects against non-sysadmin users in the database who try to view data they are not authorised to have. Someone steals the database files or hacks the server and gains access or control of the user that does have permission on the keys can decrypt the data.It's TDE (transparent database encryption) that protects against someone stealing the database files--Gail ShawSQL Server MVP |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-03 : 12:46:41
|
| I'm not "up" on this, but I think there is an asymetric column-encryption that relies on a certificate created/imported into Master, so restoring the database backup on another server wouldn't work (unless Certificate was also Exported/Imported from the original server)But if I understand you correctly then just calling the Sproc (on the original server) WILL use the certificate, and thus the hacker would be able to see the decrypted data.If its a Password then use HASH + SALT; if its a Credit Card Number then ... I don't know how to protect against someone who has got the "keys to the server" (short of making the encryption OUTSIDE SQL Server and passing the data, encrypted, TO SQL server for storage.http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/ |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-03 : 13:02:42
|
| You're thinking of TDE. Column encryption is based on keys/certs in the user database.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-04 : 05:49:29
|
Blast! You're right (as always  )So how to protect the "keys" to the "safe" then? (I mean once a hacker / disgruntled employee has access to the DB itself, rather than trying to just protect Backup Files) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-04 : 06:00:47
|
| Depends what you're trying to do and what you're trying to protect against. Encryption's a big, complex subject. May I suggest Denny Cherry's book Securing SQL Server? It's not just encryption, but that is covered (iirc)--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-04 : 07:23:46
|
| I've got a project under-way at present that needs a column of data encrypted, so that's timely advice, thanks, I'll grab a copy of his book. |
|
|
|
|
|
|
|
|
Is an encrypted column safe from sql injection?