Understanding SQL Server encryption (by password)?

Question

After asking this I still have a question.

A company need to store sensitive data. Please let's not talk about certificate right now.

A DBA does the following :

  CREATE SYMMETRIC KEY SecureSymmetricKey1 
  WITH ALGORITHM = DESX
  ENCRYPTION BY PASSWORD = N'blabla';

A programmer wants to encrypt data and does the following :

  -- open key
  OPEN SYMMETRIC KEY SecureSymmetricKey1
  DECRYPTION BY PASSWORD = N'blabla'

  -- actual encrpyt
  DECLARE @encrypted_str VARBINARY(MAX)
  SET @encrypted_str =  EncryptByKey(Key_GUID('SecureSymmetricKey1'),'my data');

Another programmer wants to READ the data so he does :

  DECLARE @decrypted_str VARBINARY(MAX)
  SET @decrypted_str = DecryptByKey(...encrypted_str...) 

All fine.

Questions :

1. When a programmer opens a symmetric key he must the know the password. I don't think that a programmer should know the password. How can this be solved ?

2. If a GOD hacker got the entire .BAK file and he restore the backup onto his own machine - he can view the stored procedure source which one of the programmers wrote, and see the password. and then the HACKER can do :

   OPEN SYMMETRIC KEY SecureSymmetricKey1 DECRYPTION BY PASSWORD = N'blabla'

What am I missing ?

Thanks for helping.

Answer 1

There are really only two options:

1. Automatic key decryption Ie. the key hierarchy includes an encryption by the service master key (usually through the database master key) and the engine is able to decrypt and or encrypt the data when needed. This protects against accidental loss of media, but anyone with access to the running server has access to the encrypted data (subject to access permissions, not subject to cryptographic protection). Transparent Data Encryption TDE is the high-end version of this type of access.

2. Explicit session provided decryption key The application must ask the user for the key decryption password and open the keys explicitly in each session it uses. The engine itself cannot decrypt the data. This protects against any unauthorized access to the data by cryptographic means (even if one can access the engine and has access rights to read the data, he cannot use the data w/o actually knowing the password). Needless to say having the application prompt the user for password is annoying at best on desktop apps, and down right impossible in web apps (the web apps needs to store the password to use it between requests, and that's a huge problem).

These are the only alternatives. Never ever is there any need to have the password stored in any configuration file or stored procedure. Any scheme that requires to store the access password somewhere is doomed from the get go and you need to rollback to page one of your design and start from scratch.