CODEWITHSUNDEEP
sqlsql-servert-sqlencryption
Cody Cook
Cody Cook

Reputation: 111

Decrypt sensitive data in database on access

I am working with a database that contains sensitive information (SSN, credit card details, etc.). I am looking for a method to secure the data.

I would like to encrypt the data when I bring it in from the outside source but also decrypt it when my users access the data via an application that uses the database as its back-end.

I know of the two methods below but I am curious to hear if there is another method I could use.

Examples:

ENCRYPTEDBYPASSPHRASE({PASSWORD}, {FIELD})/DECRYPTBYPASSPHRASE({PASSWORD},{FIELD})
-- This allows me to set a custom password but could be read through the 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.

Is ENCRYPTEDBYPASSPHRASE as secure as it gets in this case?

Upvotes: 1

Views: 2880

Answers (3)

Techno Crave
Techno Crave

Reputation: 433

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.

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:

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.

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’

Create a Certificate:

CREATE CERTIFICATE MyCertificateName WITH SUBJECT = 'A label for this certificate'

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:

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.

Decrypting Data:

You can decrypt data by using the DecryptByKey function, like so:

DECLARE @Result varchar(max)
SET @Result = DecryptByKey(@ValueToDecrypt)

Upvotes: 2

Disbeliever
Disbeliever

Reputation: 1143

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

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

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.

If for PCI requirements I'd go the route of TDE. Don't have Enterprise? Pony up for the upgrade.

Upvotes: 0

Wep0n
Wep0n

Reputation: 402

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.

Upvotes: 0

Related Questions