了解SQL Server加密（通过密码）？

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

公司需要存储敏感数据。现在请不要谈论证书。

A DBA does the following :

DBA执行以下操作：

  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 :

问题：

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 ?

当程序员打开对称密钥时，他必须知道密码。我不认为程序员应该知道密码。怎么解决这个问题？
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 :

如果GOD黑客得到了整个.bak文件，并且他在自己的家用机器上恢复备份 - 他可以查看其中一个程序员编写的SP，并查看密码。然后HACKER可以做到：

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

OPEN SYMMETRIC KEY SecureSymmetricKey1 PASSWORD = N'blabla''

What am I missing ?

我错过了什么？

Thanks for helping.

谢谢你的帮助。

2 个解决方案

#1

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.

当SQL Server本身存在加密时，您可以在所有内容或逐列上打开，这是否有必要执行此操作的原因。

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...

#2

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.

您的问题的答案是：1）不要使用密码作为您的密钥。使用数据库主密钥加密密钥。然后使用密码和服务密钥加密数据库主密钥。程序员仍然需要在加密\解密时编写OPEN SYMMETRIC KEY SecureSymmetricKey1。但是你不需要告诉他数据库主密钥的密码，因为他不需要在任何地方写这个密码。当我们不是每次创建对称密钥时，密码只写一次。此外，当使用服务密钥加密数据库主密钥时，如果您位于相同的SQL Server实例下，则不必提及打开DataBase主密钥的密码。

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.

2）如果有人拿走你的.bak文件并尝试打开它，他就不能，因为他没有密码就无法打开数据库主密钥。没有数据库主密钥，对称密钥将无法打开。因此，如果他运行存储过程，他将看不到任何东西。

I hope that helped.

我希望有所帮助。

#1