CODEWITHSUNDEEP
c#sqlsql-serverlinqencryption
Bones
Bones

Reputation: 13

Query encrypted column in MS SQL using LINQ

I need to encrypt some columns in my MS SQL database (name, ssn ...) and I have been looking at column encryption as described by a few sites such as: Encrypting Column Level Data in SQL Server and Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial.

I've been able to use an Trigger on insert to encrypt a column and I can decrypt the column within SQL Studio using:

OPEN SYMMETRIC KEY TestTableKey
DECRYPTION BY PASSWORD = 'Pa$$w0rd'
CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol
FROM TestTable

But how do I access the data from my application? I still want to be able to search for names. The only thing I can think of is using a Stored Procedure and sending the decryption password as a parameter in the LINQ statement so the stored procedure can decrypt the data and then execute the query.

Am I on the right track?

I'm new to this so I welcome other useful suggestions.

Upvotes: 1

Views: 2832

Answers (2)

indyitman
indyitman

Reputation: 76

If you are using MS SQL Server Enterprise or Developer editions, you can use TDE: TDE

Upvotes: 0

Joe Ratzer
Joe Ratzer

Reputation: 18549

Yes, I have seen stored procedures to decrypt encrypted text. I've also seen stored procedures to encrypt text, which I prefer to Triggers because I don't like Triggers - see these answers.

However, I prefer to put encryption logic in my application layer - using, for example, the Enterprise Library Encryption Code. The passphrase and salt are easily encrypted in the config file using the Enterprise Library console.

Is there a specific reason for doing this work in the database? If you must do it that way, you could use EL to protect your passphrase and pass that into the stored procedure you've written.

Upvotes: 1

Related Questions