I am working with a database that contains sensitive information (SSN, Credit Card, etc.), and I am looking for a method to secure the data. I need to both encrypt it when I bring in the data from my outside source, but also decrypt it when my users access the data via an app that uses the database as it's back-end. I know of the two methods below, but I am curious if there is any other method I could use, or if ENCRYPTEDBYPASSPHRASE is as secure as I can get in this case.
我正在使用包含敏感信息（SSN，信用卡等）的数据库，我正在寻找一种保护数据的方法。当我从外部源引入数据时，我需要加密它，但当我的用户通过使用数据库作为后端的应用程序访问数据时，我也需要对其进行解密。我知道下面的两种方法，但我很好奇是否有任何其他方法可以使用，或者如果ENCRYPTEDBYPASSPHRASE在这种情况下是安全的。
Examples
例子
Encrypt with Password
ENCRYPTEDBYPASSPHRASE({PASSWORD}, {FIELD})/DECRYPTBYPASSPHRASE({PASSWORD},{FIELD}) -- Which allows me to set a custom password, but could be read through my stored procedures.
HASHBYTES('SHA_512', {PASSWORD}+CAST({SALT} AS NVARCHAR(36))) -- This seems the most secure, but I do not know how to decrypt the data from here.
3 个解决方案
#1
0
You can not decrypt a hash (edit: Unless it has been compromised as a whole), that's the point of a hash. If you were to only compare a hashed value against your hash (such would be the case for logging into your app, for example - never store passwords in cleartext) this would be an easy app-side job.
你无法解密哈希（编辑：除非它已被整体泄露），这就是哈希的重点。如果您只是将哈希值与哈希值进行比较（例如，登录到您的应用程序就是这种情况 - 永远不会以明文形式存储密码），这将是一个简单的应用程序端工作。
I found this very handy article over on security:
我发现这篇关于安全性的非常方便的文章：
https://security.stackexchange.com/questions/16939/is-it-generally-a-bad-idea-to-encrypt-database-fields
that should help you on your way.
这应该可以帮助你。
#2
1
Encryption turns data into a series of unreadable characters, that aren't of a fixed length.
加密将数据转换为一系列不可读的字符，这些字符的长度不固定。
A hash is a string or number generated from a string of text. The resulting string or number is a fixed length.
哈希是从一串文本生成的字符串或数字。结果字符串或数字是固定长度。
The key difference between encryption and hashing is that encrypted strings can be reversed back into their original decrypted form if you have the right key and hashing is good to store passwords.
加密和散列之间的主要区别在于，如果您拥有正确的密钥并且散列适合存储密码，则加密的字符串可以反转为其原始的解密形式。
1) If you want to use hashing for security of your data then there are many types of algorithms but SHA and MD4/5 is wildely used algorithms.
1）如果你想使用散列来保证数据的安全性，那么有许多类型的算法，但SHA和MD4 / 5是使用的算法。
For example, as demonstrated below, hashed output with MD5 algorithm produces a 16 bytes long value whereas SHA1 algorithm produces a 20 bytes long value:
例如，如下所示，使用MD5算法的散列输出产生16字节长的值，而SHA1算法产生20字节长的值：
SELECT HASHBYTES('MD5', 'Test String') AS Col1, HASHBYTES('MD5', 'Test String') AS Col2 GO
SELECT HASHBYTES('SHA1', 'Test String') AS Col1, HASHBYTES('SHA1', 'Test String') AS Col2 GO
2) and if you want to use Encryption then there are two primary types of encryption, symmetric key encryption and public key encryption.
2）如果你想使用加密，那么有两种主要的加密类型，对称密钥加密和公钥加密。
Example:
例：
To create a symmetric key, we first need to setup our database with a master key and a certificate, which act as protectors of our symmetric key store.
要创建对称密钥，我们首先需要使用主密钥和证书来设置数据库，这些密钥和证书充当对称密钥库的保护程序。
Create a Database Master Key:
创建数据库主密钥：
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘myStrongPassword’
通过PASSWORD ='myStrongPassword'创建主密钥加密
Create a Certificate:
创建证书：
CREATE CERTIFICATE MyCertificateName WITH SUBJECT = 'A label for this certificate'
CREATE CERTIFICATE MyCertificateName WITH SUBJECT ='此证书的标签'
Create a Symmetric Key:
创建对称密钥：
CREATE SYMMETRIC KEY MySymmetricKeyName WITH IDENTITY_VALUE = 'a fairly secure name', ALGORITHM = AES_256, KEY_SOURCE = 'a very secure strong password or phrase' ENCRYPTION BY CERTIFICATE MyCertificateName;
Encrypting and Decrypting Data:
加密和解密数据：
Open the Key:
打开钥匙：
Before you can start encrypting or decrypting data, you must first initialize the key. This is done with the following piece of code.
在开始加密或解密数据之前，必须先初始化密钥。这是通过以下代码完成的。
OPEN SYMMETRIC KEY MySymmetricKeyName
DECRYPTION BY CERTIFICATE MyCertificateName
Encrypting data
You can encrypt data by using the EncryptByKey function, like so:
您可以使用EncryptByKey函数加密数据，如下所示：
DECLARE @Result varbinary(256)
SET @Result = EncryptByKey(Key_GUID('MySymmetricKeyName'), @ValueToEncrypt)
Note that the result of the above encryption is of type varbinary(256), and if you would like to store the value in a column to use this type.
请注意，上述加密的结果是varbinary类型（256），如果您希望将值存储在列中以使用此类型。
Decrypting Data:
解密数据：
You can decrypt data by using the DecryptByKey function, like so:
您可以使用DecryptByKey函数解密数据，如下所示：
DECLARE @Result varchar(max)
SET @Result = DecryptByKey(@ValueToDecrypt)
#3
0
In re: 2nd option: A hash is a one way operation. It generally doesn't get done with the intent to unhash it. (Consider a password. It gets hashed and a 256 byte string produced. Rather than decode the hash produced and comparing it to the naked user input, the user's input is hashed and the two hashes are compared.)
在re：2nd选项中：哈希是一种单向操作。它通常无法完成取消它的意图。 （考虑一个密码。它被散列并产生一个256字节的字符串。而不是解码产生的散列并将其与裸用户输入进行比较，用户的输入进行散列并比较两个散列。）
I think you're looking for a data access layer written with knowledge of your encryption method. That's something you'll have to create on your own. (That is use stored procedures, functions, and views to read data from it's encrypted at rest state, decrypt it, and return it to the caller. Deny access to the users to the underlying tables. Create a stored procedure GetAccountNumber, etc. [You'll note that in these cases the primary key has to be unencrypted so you can find it. Other data will necessarily need to be stored in plain text so you can properly index and search it. You don't want to find yourself in a situation where you have to cycle through every record in a table, decrypting each record, to find a matching address.])
我认为您正在寻找使用加密方法知识编写的数据访问层。这是你必须自己创造的东西。 （这是使用存储过程，函数和视图从其中读取数据在静止状态下加密，解密并将其返回给调用者。拒绝用户访问基础表。创建存储过程GetAccountNumber等[您会注意到在这些情况下主键必须是未加密的，因此您可以找到它。其他数据必须以纯文本格式存储，以便您可以正确索引和搜索它。您不希望发现自己在这种情况下，您必须遍历表中的每条记录，解密每条记录，以找到匹配的地址。]）
There is transparent data encryption (TDE) available in Enterprise editions of Microsoft SQL Server. With TDE the data is encrypted at rest and anyone who can access the database will have access to the unencrypted data. This is also true for the data access layer method. At some point the secret gets exposed. Where that occurs is up to the design. TDE can be configured in many different ways.
Microsoft SQL Server的企业版中提供透明数据加密（TDE）。使用TDE，数据在静止时加密，任何可以访问数据库的人都可以访问未加密的数据。对于数据访问层方法也是如此。在某些时候，秘密被曝光。发生的地方取决于设计。 TDE可以通过多种不同方式进行配置。
If for PCI requirements I'd go the route of TDE. Don't have Enterprise? Pony up for the upgrade.
如果符合PCI要求，我会选择TDE的路线。没有企业？小马升级。
#1
0
You can not decrypt a hash (edit: Unless it has been compromised as a whole), that's the point of a hash. If you were to only compare a hashed value against your hash (such would be the case for logging into your app, for example - never store passwords in cleartext) this would be an easy app-side job.
你无法解密哈希（编辑：除非它已被整体泄露），这就是哈希的重点。如果您只是将哈希值与哈希值进行比较（例如，登录到您的应用程序就是这种情况 - 永远不会以明文形式存储密码），这将是一个简单的应用程序端工作。
I found this very handy article over on security:
我发现这篇关于安全性的非常方便的文章：
https://security.stackexchange.com/questions/16939/is-it-generally-a-bad-idea-to-encrypt-database-fields
that should help you on your way.
这应该可以帮助你。
#2
1
Encryption turns data into a series of unreadable characters, that aren't of a fixed length.
加密将数据转换为一系列不可读的字符，这些字符的长度不固定。
A hash is a string or number generated from a string of text. The resulting string or number is a fixed length.
哈希是从一串文本生成的字符串或数字。结果字符串或数字是固定长度。
The key difference between encryption and hashing is that encrypted strings can be reversed back into their original decrypted form if you have the right key and hashing is good to store passwords.
加密和散列之间的主要区别在于，如果您拥有正确的密钥并且散列适合存储密码，则加密的字符串可以反转为其原始的解密形式。
1) If you want to use hashing for security of your data then there are many types of algorithms but SHA and MD4/5 is wildely used algorithms.
1）如果你想使用散列来保证数据的安全性，那么有许多类型的算法，但SHA和MD4 / 5是使用的算法。
For example, as demonstrated below, hashed output with MD5 algorithm produces a 16 bytes long value whereas SHA1 algorithm produces a 20 bytes long value:
例如，如下所示，使用MD5算法的散列输出产生16字节长的值，而SHA1算法产生20字节长的值：
SELECT HASHBYTES('MD5', 'Test String') AS Col1, HASHBYTES('MD5', 'Test String') AS Col2 GO
SELECT HASHBYTES('SHA1', 'Test String') AS Col1, HASHBYTES('SHA1', 'Test String') AS Col2 GO
2) and if you want to use Encryption then there are two primary types of encryption, symmetric key encryption and public key encryption.
2）如果你想使用加密，那么有两种主要的加密类型，对称密钥加密和公钥加密。
Example:
例：
To create a symmetric key, we first need to setup our database with a master key and a certificate, which act as protectors of our symmetric key store.
要创建对称密钥，我们首先需要使用主密钥和证书来设置数据库，这些密钥和证书充当对称密钥库的保护程序。
Create a Database Master Key:
创建数据库主密钥：
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘myStrongPassword’
通过PASSWORD ='myStrongPassword'创建主密钥加密
Create a Certificate:
创建证书：
CREATE CERTIFICATE MyCertificateName WITH SUBJECT = 'A label for this certificate'
CREATE CERTIFICATE MyCertificateName WITH SUBJECT ='此证书的标签'
Create a Symmetric Key:
创建对称密钥：
CREATE SYMMETRIC KEY MySymmetricKeyName WITH IDENTITY_VALUE = 'a fairly secure name', ALGORITHM = AES_256, KEY_SOURCE = 'a very secure strong password or phrase' ENCRYPTION BY CERTIFICATE MyCertificateName;
Encrypting and Decrypting Data:
加密和解密数据：
Open the Key:
打开钥匙：
Before you can start encrypting or decrypting data, you must first initialize the key. This is done with the following piece of code.
在开始加密或解密数据之前，必须先初始化密钥。这是通过以下代码完成的。
OPEN SYMMETRIC KEY MySymmetricKeyName
DECRYPTION BY CERTIFICATE MyCertificateName
Encrypting data
You can encrypt data by using the EncryptByKey function, like so:
您可以使用EncryptByKey函数加密数据，如下所示：
DECLARE @Result varbinary(256)
SET @Result = EncryptByKey(Key_GUID('MySymmetricKeyName'), @ValueToEncrypt)
Note that the result of the above encryption is of type varbinary(256), and if you would like to store the value in a column to use this type.
请注意，上述加密的结果是varbinary类型（256），如果您希望将值存储在列中以使用此类型。
Decrypting Data:
解密数据：
You can decrypt data by using the DecryptByKey function, like so:
您可以使用DecryptByKey函数解密数据，如下所示：
DECLARE @Result varchar(max)
SET @Result = DecryptByKey(@ValueToDecrypt)
#3
0
In re: 2nd option: A hash is a one way operation. It generally doesn't get done with the intent to unhash it. (Consider a password. It gets hashed and a 256 byte string produced. Rather than decode the hash produced and comparing it to the naked user input, the user's input is hashed and the two hashes are compared.)
在re：2nd选项中：哈希是一种单向操作。它通常无法完成取消它的意图。 （考虑一个密码。它被散列并产生一个256字节的字符串。而不是解码产生的散列并将其与裸用户输入进行比较，用户的输入进行散列并比较两个散列。）
I think you're looking for a data access layer written with knowledge of your encryption method. That's something you'll have to create on your own. (That is use stored procedures, functions, and views to read data from it's encrypted at rest state, decrypt it, and return it to the caller. Deny access to the users to the underlying tables. Create a stored procedure GetAccountNumber, etc. [You'll note that in these cases the primary key has to be unencrypted so you can find it. Other data will necessarily need to be stored in plain text so you can properly index and search it. You don't want to find yourself in a situation where you have to cycle through every record in a table, decrypting each record, to find a matching address.])
我认为您正在寻找使用加密方法知识编写的数据访问层。这是你必须自己创造的东西。 （这是使用存储过程，函数和视图从其中读取数据在静止状态下加密，解密并将其返回给调用者。拒绝用户访问基础表。创建存储过程GetAccountNumber等[您会注意到在这些情况下主键必须是未加密的，因此您可以找到它。其他数据必须以纯文本格式存储，以便您可以正确索引和搜索它。您不希望发现自己在这种情况下，您必须遍历表中的每条记录，解密每条记录，以找到匹配的地址。]）
There is transparent data encryption (TDE) available in Enterprise editions of Microsoft SQL Server. With TDE the data is encrypted at rest and anyone who can access the database will have access to the unencrypted data. This is also true for the data access layer method. At some point the secret gets exposed. Where that occurs is up to the design. TDE can be configured in many different ways.
Microsoft SQL Server的企业版中提供透明数据加密（TDE）。使用TDE，数据在静止时加密，任何可以访问数据库的人都可以访问未加密的数据。对于数据访问层方法也是如此。在某些时候，秘密被曝光。发生的地方取决于设计。 TDE可以通过多种不同方式进行配置。
If for PCI requirements I'd go the route of TDE. Don't have Enterprise? Pony up for the upgrade.
如果符合PCI要求，我会选择TDE的路线。没有企业？小马升级。