April 19, 2017 at 10:42 am
Hello All,
My organisation has a product based with sql as DB(SQL 2012 Std) . We need to implement encryption of critical client data which is hosted on DC across the world. We basically have 3 options :
1.Custom encryption using AES 192 algorithm
2. TDE
3. SQL 2016 Always Encrypt.
While custom encryption is time consuming and will involve lot of resources and will stretch the timelines . TDE will encrypt the entire database and also it encrypts data at rest and quering the DB will get encrypted DB , which is not our requirement as there is lot of data which moves across. Always encrypt will involve moving to SQL 2016 and will entail licensing cost , plus we don't have enough experience to manage it .
Will it be possible to please advise me the test possible to go for ?
Thanks in advance !
April 19, 2017 at 11:00 am
Those three are not quite the same; they really are different technologies with different requirements.
TDE(Transparent Data Encryption) protects your data at rest: that means a backup, or a san snapshot of the MDF + LDF files cannot be attached to another instance, unless you have the certificates to allow you to do so.
TDE does not encrypt your data from anyone connecting to the SQL instance: someone who has db_datareader can still see all the data.
generally, encryption at the column level, where you protect the data int he table itself from casual data discovery, involves changing the data type from whatever it is now to a varbinary, and the varbinary contains an ecnrypted binary of the original value...you cannot see the original data at all, unless you have the decryption mechanism, and there is half a dozen ways you can do that( @ SQL server level with certificates, passphrases or other methods) or having an application control the encryption, and SQL holds the data. this is very common, with lots of available examples.
Always encrypted is SQL doing the same work as the second item above: changing the way the data is stored, but preserving a way to decrypt and see the data in an unencrypted format.
Always encrypted has some caveats: it's going to require a slightly modified connection string in SSMS to allow sysadmins to see the original data, and then the the admin hasthe ability to create permissions to allow different groups to see the data unencrypted or not.
however, it requires the .net framework 4.5 1(or higher? i forget)
so that might mean an upgrade path is required....so if you have any applications using OleDB, they will not be able to decode the encryption; other .net or web applications might need to be recompiled and upgraded to the higher .net version.
Lowell
April 19, 2017 at 12:04 pm
Good summary from Lowell, but I'll add a couple simple thoughts.
Column encryption is is designed when you trust the server, not the client. You need to trust admins and those with physical access here.
Always Encrypted is when you trust the client, but not the server. This may be what you need, but there are restrictions and limitations you need to learn about and evaluate from a programming architecture standpoint.
TDE is mostly a checkbox for auditors. Lots of companies like this, and if that's what you need, use it. I see TDE as a "defense in depth" addition to your security, but not providing much data protection by itself.
April 20, 2017 at 5:45 am
I'm not an encryption expert like these guys, but I'll add one more wrinkle from a performance aspect. Column encryption generally means you won't be indexing on these columns and you're willing to forego search performance on these columns. The random nature of the data, it's size, and the requirement that it go through encryption algorithms can negatively impact performance and performance tuning. Further, depending on the data, you sacrifice some functionality. LIKE statements or CONTAINS (a couple of examples) statements won't work within T-SQL. Just one more thing to consider when working on this sort of thing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply