Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Encryption:hiding the Password Expand / Collapse
Author
Message
Posted Wednesday, August 6, 2008 12:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 3:27 PM
Points: 41, 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

Post #547286
Posted Wednesday, August 6, 2008 12:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 1, 2014 6:55 AM
Points: 2,366, Visits: 1,845
Hi

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


"Keep Trying"
Post #547288
Posted Wednesday, August 6, 2008 7:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 9, 2013 3:27 PM
Points: 41, 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
Post #547963
Posted Thursday, August 7, 2008 6:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 1, 2014 6:55 AM
Points: 2,366, Visits: 1,845
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"
Post #548236
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse