SQL 2005 Symmetric Encryption
-
I am unclear on how encrypting a column makes the data anymore secure than simple restricting its access via permissions. 1. Restrict a single user to open a key to decrypt the contents of a column. 2. Allow only a single user to view that table (or column using a view). How is #1 any more secure than #2? In both cases only a single user (not counting sysadmins) can view the data.
-
to Noeld ..
About the restore of a db at another server, you can reanable automatic key management for the db using the old dbpassword :
if not exists ( select MKP.*
, D.name as DbName
from sys.master_key_passwords MKP
inner join sys.credentials C
on MKP.credential_id = C.credential_id
inner join sys.database_recovery_status DRS
on MKP.family_guid = DRS.family_guid
inner join sys.databases D
on DRS.database_id = D.Database_id
Where D.name = 'yourdb'
 
begin
Print 'Adding dbmasterkey for yourdb';
-- using the same password that has been used the first time !
EXEC sp_control_dbmasterkey_password @db_name = N'yourdb',
@password = N'P@ssword', @action = N'add';
end
GO
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
ALZDBA,
Thanks! I haven't had the time to play enough with the encryption stuff latetly so I do appreciate your comments.
* Noel -
http://www.privacyrights.org/ar/ChronDataBreaches.htm
http://www.usa.gov/veteransinfo/
Stealing passwords isn't the only avenue of attack for hackers. Data can be retrieved from a stolen hard drive or lap-top with unencrypted data fairly easily. I used to do it all the time when I needed to recover data from a swapped out hard drive on my PC. There are also several regulatory requirements for encrypting personal and confidential data "at rest" (HIPAA, SOX, FCRA, FACTA, etc.) I haven't heard of anyone going to jail over it yet, but there are some pretty hefty fines for non-compliant businesses, plus billions of $ worth of bad publicity.
-
In addition to this nice article, I also enjoyed Laurentiu Cristofor's blog at http://blogs.msdn.com/lcris/
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
keassnow (3/20/2006)
Say your malicious person deletes your symmetric key? You can restore a master key, but I've seen nothing that tells me how to restore a symmetric key.
You can recreate the key, but if the KEY-GUID isn't identical, nothing encrypted with the old key can be decrypted with the new one. And with adhoc changes to system tables eliminated, your only choice is to restore a backup of the database to a new location, decrypt all the old info with the old key, and reencrypt it using the new key.
Correct me if I'm wrong. Please!
According to what I read, the GUID generated will be the same GUID as long as everything else used to create the Symmetric Key is exactly the same. The GUID being generated is not using NewID() or something similar. I imagine they are taking some textual data in the Key and converting it to varbinary and then converting every character to a byte, take 32 of those bytes and voila, you have a GUID!
-
Hi Mike,
I'd be interested to hear you view on this
Since the cert is stored in the database, anyone obtaining access to the database could simply take a look in the certs folder and call decryptbycert on sensitive data, spinning throught the cert id's seen there, until one works.
The only requirement is credentials which have associated VIEW DEFINITION and CONTROL rights to cert in question.
Effectively, if these credentials are compromised this encryption only protects against simple reading of the sensitive data (assuming your attacker is naive enough not to know about decyptbycert)
Am I missing something?
Thanks in advance,
Matthew Evavns
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply