SQL Server 2005 and LINQ TO SQL Data Encryption

Question

I have an ASP.NET MVC application, and I used Linq-to-SQL queries to retrieve data from my SQL Server database. Now for security purposes I need to encrypt my database. How does Linq-to-SQL work with encrypted databases?

Answer 1

Use Transparent Database Encryption . As the name implies, it is transparent, and LINQsSQL works just fine.

IF you are on a non-Enterprise SKU, then you have to use SQL cryptographic functions to encrypt / decrypt data: ENCRYPTBYKEY and DECRYPTBYKEY . In client tools (SqlClient, ODBC, OleDB), they are not supported in any form or in any ORM structure, including LINQ. The encryption and decryption process must be performed on the server and managed by T-SQL constructs, which largely means that you must perform the entire DML operation using stored procedures. Data retrieval operations can be automated in some way using representations that design decrypted data, and these representations can be magnified using LINQ.

, TDE, IQueryable , , , LINQ ORM (, InsertOnSubmit).