处理MSSQL中加密数据的最佳做法 [英] Best practices for dealing with encrypted data in MSSQL

问题描述

我的用户数据库中有一些数据，我更喜欢加密。大多数数据需要在需要时进行解密，但也有密码可以保持加密（在过去我们将使用 pwdcompare ，但我相信现在已经过时了）。

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.

我不明白是正确的打开方式运行时的主密钥，以加密/解密数据。如果我想使用存储过程检索加密数据，我该如何打开主密钥？我使用存储的proc参数传入主密钥的密码？

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?

推荐答案

据了解，您应该创建一次主密钥（您可以在安装过程中执行此操作），这是第一个也是最后一次需要密码。之后，使用主密钥创建加密密钥（对称或不对称）而不使用密码，并使用它来加密/解密您的数据。您不需要输入密码，您只需使用加密密钥并对其进行控制权限即可。唯一的问题是你的DBA也可以拥有它:)

看到这篇文章：在SQL Server 2005数据库中使用非对称加密和数字签名

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

这篇关于处理MSSQL中加密数据的最佳做法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！