October 8, 2009 at 8:09 am
We have SQL Server 2000 and we run Blowfish as an extended procedure to encrypt/decrypt certain fields in the DB. That works fine, no issues there.
My question is design: It seems to me that having the stored procedures to encrypt and decrypt data stored within the DB is a lot like having the key inside the lock (or under the door mat, at best).
Sure, the data is not readable by doing a simple "SELECT * FROM..." query, but the tools to decrypt the data are right there. We run all decryption/encryption functions from stored procedures, but what's to stop someone from extracting the encryption key from the stored procedure's code? I'm no hacker (not even much of a DBA) and even I figured out how to find the encryption keys and decrypt the data.
What is the best way to protect encryption keys? Am I missing the point of data encryption altogether? Is it more akin to locking your door against the casual thief while a real crook will just smash through the window?
I'm a total SQL n00b, so be gentle.
October 8, 2009 at 9:45 am
Chi Chi Cabron (10/8/2009)
We have SQL Server 2000 and we run Blowfish as an extended procedure to encrypt/decrypt certain fields in the DB. That works fine, no issues there.My question is design: It seems to me that having the stored procedures to encrypt and decrypt data stored within the DB is a lot like having the key inside the lock (or under the door mat, at best).
Sure, the data is not readable by doing a simple "SELECT * FROM..." query, but the tools to decrypt the data are right there. We run all decryption/encryption functions from stored procedures, but what's to stop someone from extracting the encryption key from the stored procedure's code? I'm no hacker (not even much of a DBA) and even I figured out how to find the encryption keys and decrypt the data.
What is the best way to protect encryption keys? Am I missing the point of data encryption altogether? Is it more akin to locking your door against the casual thief while a real crook will just smash through the window?
I'm a total SQL n00b, so be gentle.
Its almost like a key to one of your room in your house and its kept under the mat, but someone has told you that the key is there under the mat 😉
You have been provided with access to look at the code of the stored procedure. So you are authorised to do that encryption/decryption I guess.
Its all to do how sophisticated your encryption/decryption should be. You can go to the next level of random key generation. But at somestage we will decide that this level of encryption/decryption is enough for me.
Its like what level of security is needed if its bank and if its just my apartment!
There are different levels of encryption/decryption for sure! I think oracle creates its own key everytime before storing the password in the database(Inbuilt). I am not sure if its there in sqlserver.
---------------------------------------------------------------------------------
October 9, 2009 at 6:51 am
What you mention is why I am not super impressed with SQL Server's encryption functions in 2005/2008. I am in favor of doing encryption in the business layer. By encrypting in the SQL Server you also are sending the data to be encrypted across the wire unencrypted whereas encrypting in the business layer you do not have that issue.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 16, 2010 at 3:52 pm
We run all decryption/encryption functions from stored procedures, but what's to stop someone from extracting the encryption key from the stored procedure's code?
You have been provided with access to look at the code of the stored procedure. So you are authorised to do that encryption/decryption I guess.
I was wondering the same thing. Being a SQL Newbie and completely new to encryption I'm not sure my solution was a good one, but I wrote the SP with an EXECUTE AS OWNER statement. That way I only had to grant EXECUTE permission to the application's user, but that user cannot view or edit the SP (which contains the certificate & key). I thought this was a better alternative than having to grant the user VIEW DEFINITION and CONTROL on the encryption certificate, which enables them to see the certificate and key.
What are experts' opinions on this solution? Does it make any sense to do it this way?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply