Query encrypted column in MS SQL using LINQ

Question

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.

Community · Accepted Answer · 2017-05-23 10:31:59Z

1

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.

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Jan 30, 2013 at 16:28

Joe Rätzer

18.6k3 gold badges40 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bones Over a year ago

Thanks for your response. I'm not able to encrypt at the application layer because I'm inserting the data from another database using a bulk insert. So I guess I'll try out the stored procedures for now.

indyitman · Accepted Answer · 2014-01-22 17:59:44Z

0

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

answered Jan 22, 2014 at 17:59

indyitman

761 silver badge5 bronze badges

Collectives™ on Stack Overflow

Query encrypted column in MS SQL using LINQ

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related