August 6, 2008 at 12:27 am
I have just worked through Michael Coles' article:SQL 2005 Symmetric Encryption
By Michael Coles, 2007/05/11
http://www.sqlservercentral.com/articles/SQL+Server+2005+-+Security/sql2005symmetricencryption/2291/
Now I understand how to use Master Keys, Certificates and Symmetric keys to encrypt/decrypt the data in a table.
I took Michael's suggestion and set up my database in non-"automatic key management" mode.
This is so that even the sysadmins can't read my encrypted data without the password to the Master Key
But I am a little confuse as to how to hide the password to the Master Key.
I tried putting this into a function (without the /** **/)
/*******************************
OPEN MASTER KEY ENCRYPTION BY PASSWORD = 'password'
OPEN SYMMETRIC KEY TestSymmetricKey
DECRYPTION BY CERTIFICATE TestCertificate;
********************************************/
on compile, I get :
/*******************************************
Msg 443, Level 16, State 14, Procedure EncryptData, Line 22
Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function.
*******************************************/
AND on the decrypt function, if I try to pass the Password in (so the user can't just run it), I fail with
Msg 443, Level 16, State 14, Procedure EncryptData, Line 24
Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.
Now I was using variable for the password because in the Decrypt function and concating to the OPEN MASTER KEY DECRYPTION BY PASSWORD = string, then using exec.
Not using exec causes another error!
So I like this concept of the MasterKet/Certificate/Symmetric keys as I can back them up and restore them without loss of the encrypted data.
But How Do I hide the Password for the Master KEY from the sysadmins and everybody else?
I could use an ASymmetric key, but I can't back it up independant of the data.
Regards
GF
August 6, 2008 at 12:35 am
Hi
Dont know whthr this will help or not ... what abt putting the open master key statement in a encrypted procedure.
"Keep Trying"
August 6, 2008 at 7:56 pm
Well I tried that and it did work.
But I am trying to do the Encryption on a INSERT or UPDATE of a table
When I put the OPEN MASTER KEY/Encrypt statements in a trigger to encrypt any incoming data and have the decrypt OPEN MASTER KEY/decrypt statements in a Procedure I get a NULL on the decryption.
So I was trying moving the OPEN MASTER KEY/encryptiondecryption statements into Functions to see if that would work.
The other downfalls to putting into Procedures are that:
-I would like to not have this functionality scattered into many SPs
-I wish to Encrypt on a trigger
Regards GF
August 7, 2008 at 6:35 am
what abt this..
One single encrypted procedure containing the open master key statement.
In the procedure that inserts the data
call the master key proc
do the encryption & insertion
insertion will call the trigger. keep the trigger if required.
close the master key.
again these are things that i have not tried out...
all the best and keep us posted.
"Keep Trying"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply