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?
Best Answer
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 DEFINITIONis far more effective here (as well as not giving your developerssysadminand 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: