SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Encryption:hiding the Password


SQL Encryption:hiding the Password

Author
Message
GregFrazer
GregFrazer
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 51
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
ChiragNS
ChiragNS
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3505 Visits: 1865
Hi

Dont know whthr this will help or not ... what abt putting the open master key statement in a encrypted procedure.

"Keep Trying"
GregFrazer
GregFrazer
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 51
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
ChiragNS
ChiragNS
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3505 Visits: 1865
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"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search