Best practices for dealing with encrypted data in

问题:

I have some data in my user database that I would prefer to be encrypted. Most of the data will need to be decrypted when requested, but there are also passwords that can stay encrypted (in the old days we would use pwdcompare but I believe this is obsolete now).

I have followed the steps here, so I have now successfully encrypted my data.

What I don't understand is the correct way to open the master key at runtime, in order to encrypt/decrypt data. If I want to use stored procedures to retrieve encrypted data, how do I go about opening the master key? Do I pass in the master key's password using a stored proc parameter?

回答1:

As I have understood, you should create master key once (you can do this during installation process) and this is the first and last time password is needed. After that, using master key create encryption key (symmetric or asymmetric) without a password and use it to encrypt/decrypt your data. You don't have to give a password for it, all you need is use your encryption key and have CONTROL permission on it. The only issue is that your DBA can have it too :)

See this article: Using Asymmetric Encryption and Digital Signatures in a SQL Server 2005 Database