Requirement 2 requires the data be stored "normally": that is, you can't use encryption in the client for all data. Encrypting data in the client also contradicts requirement 1
Requirement 3 requires the media or the actual backups are encrypted. Encrypting the HDDs isn't reliable because once someone has the actual media then it can normally be unencrypted by a sysadmin.
So, I'd suggest using a 3rd party tool like Red Gate SQL Backup Pro or LiteSpeed by Quest to secure your backups. Points 1 and 2 are satisfied because the on-line database is unchanged
Since you are using Standard Edition, you cant use TDE. So other options are
Using encryption keys at instance/database level :
SQL Server has two kinds of keys: symmetric and asymmetric. Symmetric keys use the same password to encrypt and decrypt data. Asymmetric keys use one password to encrypt data (called the public key) and another to decrypt data (called the private key).
SQL Server has two primary applications for keys: a service master key (SMK) generated on and for a SQL Server instance, and a database master key (DMK) used for a database.
Also, you can have encryption at column level by creating a MASTER KEY ENCRYPTION along with CREATE CERTIFICATE and then CREATE SYMMETRIC KEY.
An example of how this can be done is described at Encrypt a Column of Data
Reference : SQL Server and Database Encryption Keys (Database Engine)
At Drive level :
Using BitLocker as it is a Drive Encryption data protection feature available Windows Server 2008 R2. Refer to : BitLocker Drive Encryption Overview There are many opensource or third party software to do the same job but at additional cost.
Note: The most important bit is ALWAYS backup your encryption keys.
You can use third party software like Redgate's sql backup which allows you to encrypt backups using passwords.
Depending on what level you need encryption will determine if it is worth upgrading to enterprise edition or not. You have to evaluate native TDE encryption vs encryption keys and certificates vs open source vs disk encryption.
Best Answer
There have been no significant enhancements in pgcrypto, and there's no functionality to allow functions to be marked sensitive such that queries that use them don't get logged. That'd fill one security hole and create another anyway.
So long as you don't actually store the key in the DB, you're not inherently giving the DB the ability to decrypt the data. You're just increasing the risk of key exposure through logs, network protocol captures (if not SSL),
pg_stat_activity
, etc. If you trust both the DB server and app server, this may not actually matter as much. Nonetheless, I still think it's better that the DB never see the decryption keys at all.See also: