Encryption/Decryption with stored procedure / view

Question

I'm working on a project that uses Entity Framework for programmers to access the data. We've recently found need to add fields that were determined that need to be encrypted. This will be the first fields that require encryption.

We plan on using Symmetric Key for cell level encryption for that specific field. For the developers to continue to use Entity Framework, I wanted to make a stored procedure to handle inserts for this table which would encrypt the cell needed, and a View to open the key and decrypt the field.

I plan on encrypting the stored procedure & view so the meta data isn't visible by looking at the definition.

Is this a sound plan? Am I overlooking anything?

• wiretext: is you want to encrypt column value right ?? if you encrypt SP then whole SP content will encrypted
• ribald: @AaronBertrand Thanks Aaron! I'll read up on the Always Encrypted. It sounds promising. I was thinking about TDE, but our System Admin advised against it; however, if I had to implement cell level encryption - is using a view/sp with deny view definition to encrypt/decrypt that cell good practice? Otherwise I'm not sure how the programmers can get the data for the application.
• Aaron Bertrand: Yes, TDE is like renting a steamroller to make pizza dough, because it's the whole database and it only protects the data at rest. Your approach seems fine, just note that it is still vulnerable to man-in-the-middle attacks (if the application passes unencrypted data to the procedure, and the view returns unencrypted data, this information can be viewed pretty easily). Always Encrypted was created to solve both of these problems, but now you have a different problem: it's not released yet (well, unless you are interested in Azure SQL Database).

Answer 1

Note that encryption of modules in SQL Server is not really encryption; it is more like weak obfuscation. Anyone with a search engine and a few minutes to kill can reverse engineer your objects, so encrypting them is almost completely pointless. DENY VIEW DEFINITION is far more effective here (as well as not giving your developers sysadmin and other privilege escalation).

As for the data, SQL Server 2016 will have Always Encrypted (read about it here), it may make a lot of your work easier if you can wait for that. One of the big plusses is that the app can send the data through the provider already encrypted, so it can't be sniffed out by trace or other man-in-the-middle attacks. Be aware of limitations, too, though:

• Always Encrypted Limitations
• Performance Impact of Always Encrypted
• Always Encrypted Performance : A Follow-Up

• ribald: thanks Aaron. I think we'll proceed with cell level encryption. A quick followup question I had: what prevents a hacker from using the Decryption View if the database was compromised?