Can anyone recommend an encryption method for ALL user dbs using SQL Server 2016 STANDARD edition?

Question

Our clients are insisting that we now encrypt ALL their SQL Server data at rest, which must include tempdb. I think I have the following options available –

• Bitlocker entire drive(s)
• TDE (but the cost of Enterprise edition breaks our support model for these clients!)
• Always-Encrypted (but more a GDPR/Personal information solution, not for whole databases) – and cannot then "wildchar" search these fields, which is a requirement.
• EFS the User databases location on disk
• Some 3rd party application that does SQL Server encryption
• Others?

Any recommendations appreciated (for SQL Server 2016 STANDARD edition only)

• Jeroen Mostert: Don't forget to clarify who the encryption is supposed to be guarding against, because this determines which methods of encryption are acceptable (and how they should be deployed). For example, with Always Encrypted not even administrators of the SQL Server can decrypt the data (they simply don't have the key), whereas a solution like EFS can be circumvented by administrators who can control the SQL Server account. Going the other way, if "at rest" merely means that backups should be encrypted, that's obviously a lot cheaper to do than encrypting the data of a running server.

Answer 1

There are 3rd party tools which replicate TDE functionality.

I have tested the following during an exercise to evaluate whether we can move from Enterprise to Standard Edition:

• https://www.database-encryption.com/
• https://netlibsecurity.com/

In both cases, they seem to work by placing a driver between the SQL binaries and the storage layer, and after configuration are transparent to the connecting application. Queries work in the exact same way as for TDE-enabled databases. Once the data leaves the storage, it is unencrypted. It would appear as an unencrypted database to all authenticated connections.

They do cost, but I believe there are trial versions available.

In performance tests (10,000 small insert queries into a clustered index) I found that DBDefence closely mirrored the performance of a TDE-enabled database. Query times for Netlib increased by approx 8%. Obviously your specific scenario may differ.

All my tests were performed on SQL 2016 Standard.

• Neil Weicher: As a way of correction, NetLib Encryptionizer and DBEncrypt do not work the same: Encryptionizer sits between SQL Server and the operating system, while DBEncrypt "injects" itself into the SQL process space in memory. Our tests have shown Encryptionizer to be on average about 10% faster than TDE, so I'd be interested to see your test bed. Disclaimer - I am from NetLib Security..

Answer 2

Depending on the size you need for your tempdb using a ram disk is an option that should be considered. It wouldn't be written to disk ever and it provides great performance.

https://blogs.technet.microsoft.com/windowsinternals/2017/08/25/how-to-create-a-ram-disk-in-windows-server/

• Aaron Bertrand: This can help with tempdb, as long as tempdb isn't so large that it is not feasible, but it doesn't do anything for all the other dbs...