-2

Our requirement is that the data at rest is to be encrypted. MDF/LDF files to be encrypted, backup files to be encrypted. I found 3 options. We use Sql Server 2016 (SP1)

  1. Always encrypted
  2. AppScan EFS (ibm product)
  3. Enable full disk encryption.

We hesitate to go with option 1 as it is very slow. Moreover our requirement is data at rest must be protected. We are looking for high performance solution.

Please point me right direction here. Are there any alternative tools available?

closed as off-topic by Jack Douglas Feb 5 at 16:18

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – Jack Douglas
If this question can be reworded to fit the rules in the help center, please edit the question.

4

Make sure you consider Transparent Data Encryption (TDE) as well; that would be my recommendation for your needs:

  • Files are encrypted on disk
  • Backups are encrypted
  • No impact to clients
  • No need to change anything in the applications themselves

Performance impact is not zero, but is only a one-time CPU hit as the SQL server opens the SQL pages from disk (or writes changed pages to disk). In my experience this impact is low to negligible.

EDIT: TDE is not column-level encryption, and requires no changes to any of your queries or stored procs (hence "transparent").

The SQL engine decrypts the data pages when they are loaded from disk into memory, and so are available for your queries to operate normally, while still leaving the data encrypted "at rest" (on disk, and in backups).

Further links:

Transparent Data Encryption (TDE) is a feature introduced in SQL Server 2008 and available in later versions for bulk encryption at the database file level (data file, log file and backup file) i.e. the entire database at rest. Once enabled for a database, this feature encrypts data into pages before it is written to the disk and decrypts when read from the disk. The best part of this feature is, as its name implies, it’s completely transparent to your application. This means literally no application code changes (only administrative change to enable it for a database) are required and hence no impact on the application code\functionalities when enabling TDE on a database being referenced by that application.

This technology was designed to have the entire encryption process be completely transparent to the applications accessing the database. It does this by using either Advanced Encryption Standard (AES), or Triple DES, encrypting the file pages and then decrypted as the information goes into memory. This inhibits limitations from querying the data in an encrypted database. This is essentially real time I/O encryption and decryption and does not increase the size of said database.

Also note, that as a result of TDE, database backups will also be encrypted. In the event that a backup of the database gets lost or stolen, the culprit will not be able to restore the database without the appropriate certificate, keys and passwords.

Conclusion: The average elapsed-time overhead was 6.36%. This confirms that “in some of the testing ..., the overhead was measured to be less than 5%.” Given the benefit of having “at rest” data encrypted, this seems like a very reasonable cost.

Having run a number of similar tests on new features in SQL Server, I am amazed that TDE has such a minimal effect on the time to run a workload, and it has no obvious affect on CPU utilization. In my mind, this is a clear win. TDE adds very little cost to your processing, it costs nothing (assuming you’ve already paid for Enterprise Edition), and you don’t have to change your application at all. Bravo to the development team for delivering such a strong new feature.

  • Thanks @BradC, it is very helpuful. But I just checked it is not available in Standard Edition. We cannot afford Enterprise Edition. Any simple alternative tools? – Pandiarajan Feb 4 at 19:03
  • @Pandiarajan Sorry, not sure; you might have to price out some 3rd-party vendor solutions. FYI they did add support for "Always Encrypted" to Standard edition as of SQL 2016 SP1, if that helps. You could also consider one solution for "at rest" encryption (maybe a storage vendor encryption product), and a different one for SQL backup encryption (either a 3rd-party backup product, or just native SQL encrypted backups, which is supported by standard edition). – BradC Feb 4 at 20:22
2

You can use TDE (Transparent Data Encryption) is what you should look and test. It provides encryption at rest with some CPU hit. I wrote about my experience here with TDE.

Since you are using SQL 2016 SP1, you should upgrade to CU4 as I encountered a bug and its fixed in 2016SP1CU4 and up.

Make sure you backup your certs as well so that you can ensure that a restore is doable when you need.

Not the answer you're looking for? Browse other questions tagged or ask your own question.