SQL Server 2005 Data Encryption And LINQ TO SQL

Question

I have a ASP.NET MVC application and I have used Linq-to-SQL queries to get 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 suggests, is transparent and LINQsSQL works just fine.

IF you are on non-Enterprise SKU then you will have to use the SQL cryptographic functions to encrypt/decrypt data: ENCRYPTBYKEY and DECRYPTBYKEY. There is no support for them of any kind in the client tools (SqlClient, ODBC, OleDB) nor in any ORM framework, including LINQ. The encryption and decryption process must happen on the server and is driven by T-SQL constructs, which pretty much implies you have to do all DML operation by stored procedures. Data retrieval operations may be automated somehow by using views that project the decrypted data and these views can be elevraged by LINQ.

In short, w/o TDE, you will be able to use IQueryable over data returned by views and procedures that decrypt the data, but you won't be able to use LINQ as an ORM (eg. no data context InsertOnSubmit).