SQL Encryption:hiding the Password

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » SQL Encryption:hiding the Password

SQL Encryption:hiding the Password

Rate Topic

Display Mode

Topic Options

Author

Message

GregFrazer

Posted Wednesday, August 06, 2008 12:27 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 09, 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

ChiragNS

Posted Wednesday, August 06, 2008 12:35 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

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

GregFrazer

Posted Wednesday, August 06, 2008 7:56 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 09, 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

ChiragNS

Posted Thursday, August 07, 2008 6:35 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

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 »

Permissions