Microsoft SQL Server 2008 encryption over multiple DB versions

Question

I have about 5 clients deployed running different versions of SQL Server 2008 (Some standard, some enterprise, etc.) And I am being tasked with encrypting the data so:

1. The software we are using can still query the data with decent efficiency.
2. DBA's can still get into the data through Management Studio.
3. If someone were to obtain a copy of the backup files (being stored on an external HDD), they would be unable to restore the database.

It seemed as if Transparent Data Encryption would be perfect, and I have it functioning, but "This feature is only available in Enterprise and Developer Editions of SQL Server 2008".

I want to have the method of encryption standard over all of the deployed servers. What is a good method to do so? Should I simply encrypt the HDDs?

Also, am I missing any other way that a person could potentially get a copy of database (Assuming no passwords are breached)?

Answer 1

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

• MrZander: I forgot to accept this last year... better late than never!