How to encrypt data at rest

How can you encrypt data at rest on Sql Server Web Edition? TDE is apparently not an option, but I don't know what else is available. Any recommendations?

Solution 1:

What threat are you trying to protect against? Accidental media loss? Server access compromise? Physical hardware loss? The options and solutions vary vastly with the threat being mitigated.

A disk encryption system, like BitLocker, will encrypt the entire disk in a very efficient fashion but it only protects against physical loss of the hardware or accidental loss of the hard drive. A solution like TDE will protect the media (the database files) including backups, and including workload spills in tempdb. File-level encryption (EFS) does not work with SQL Server. More precisely, it works with abysmal performance.

These are the only options that work out of the box for any application. Other solutions include encrypting the data in the application or using the SQL Server cryptographic API (EncryptByKey). But these all require you to design the application specifically for using the cryptographic API (ie. are not transparent).

But again, the most important decision to make is to clearly state the threat you're trying to mitigate.