Geek Answers Handbook

Understanding SQL Server encryption (password protected)?

Companies need to store sensitive data. Please do not talk about the 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 performs the following actions:

  -- 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 that he:

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

All perfectly.

Questions:

  • When a programmer opens a symmetric key, he must know the password. I do not think that the programmer should know the password. How can this be solved?

  • If the GOD cracker received the entire .bak file, and he restores the backup on his own home machine - he can look at the SP that one of the programmers wrote and see the password. And then HACKER can do:

    OPEN SYMMETRIC KEY. SecureSymmetricKey1 DECREE ON PASSWORD = N'blabla '

What am I missing?

Thanks for the help.

+4
source share
2 answers

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

If you want to go your own way, you can create your own procedure using encryption when creating / changing. This will stop people who can extract logic from the database before or after recovery.

 Create Procedure enc.prMyProcedure With Encryption as...
+2
source

The answer to your questions: 1) Do not use a password for your key. Instead, encrypt the key with the database key. And then encrypt your primary database key with a password as well as a service key. The programmer still needs to write OPEN SYMMETRIC KEY SecureSymmetricKey1, while Encrypting \ Decrypting. But you do not need to tell him the password of the base database key, because he should not write this password anywhere. The password is recorded only once, when we do not create a symmetric key every time. Also, when the database master key is encrypted using the service key, you do not need to provide a password to open the database master key if you are under the same instance of SQL Server.

2) If someone takes your .bak file and tries to open it, he cannot, because he cannot open the database master key without a password. A symmetric key will not be opened without a primary database key. Therefore, if he starts the stored procedures, he will not see anything.

I hope this helps.

+1
source

More articles:


All Articles