CODEWITHSUNDEEP
sql-serversql-server-2008securityencryption
Royi Namir
Royi Namir

Reputation: 148524

Understanding SQL Server encryption (by password)?

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 restores the backup on its own home machine - he can view the SP which one of the programmers have written , 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.

Upvotes: 4

Views: 3815

Answers (2)

user2788694
user2788694

Reputation: 23

Answer to your questions are : 1) Do not use password for your key. Encrypt your key with Database Master Key instead. And then Encrypt your Database Master Key with a password as well as Service Key. The programmer still needs to write OPEN SYMMETRIC KEY SecureSymmetricKey1 while Encrypting\Decrypting. But you don't need to tell him the password of Database Master Key because he doesn't need to write this password anywhere. The password is written only once when we create the symmetric key not every time. Also when the Database Master Key is encrypted with a service key you don't have to mention password for opening DataBase Master Key if you are under the same SQL Server Instance.

2) If somebody takes your .bak file and tries to open it he cannot because he cannot open the Database Master Key without a password.Symmetric key won't open without a database master key. So if he runs the stored procedures he won't see anything.

I hope that helped.

Upvotes: 1

u07ch
u07ch

Reputation: 13702

Is there a reason why you are needing to do this when there is encryption in SQL Server itself that you can turn on either on everything or column by column.

If you want to go your own way you could create your procedure with encryption as you create/ alter. This will stop people being able to extract the logic from the database on or before restore.

Create Procedure enc.prMyProcedure With Encryption
as...

Upvotes: 2

Related Questions