CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008encryptioncryptography
Gennady Vanin Геннадий Ванин
Gennady Vanin Геннадий Ванин

Reputation: 10384

What are advantages of SQL Server 2008 transparent encryption TDE over encrypting db backup?

What are advantages of SQL Server2008+ TDE ( Transparent Data Encryption) over encrypting just database backup file (with a password)?

Update:
I am sorry - removed Oracle from this question (SQL Server TDE is for the whole database and encrypted data is stored in database).
Encrypting is not difficult to organize using C# without 3d-party tools, though there are plenty of 3d party tools.

Upvotes: 1

Views: 1796

Answers (3)

nvogel
nvogel

Reputation: 25526

There is no feature in SQL Server to encrypt just the backups without encrypting the database. You need to use third party software to do that. TDE encrypts both database and backups.

Upvotes: 0

DCookie
DCookie

Reputation: 43523

Advantages of TDE:

  1. Encrypting the backup file only obviously protects only the backup files, not the actual datafiles. If you miss encrypting a backup, it's unprotected.
  2. TDE encrypts/protects only the table/columns you need. Encrypting the entire backup can be very slow for large backup sets.

Disadvantages of TDE:

  1. Inserts/updates of encrypted data are slower.
  2. There are certain features that cannot be used in conjunction with TDE.

Here is a nice overview of TDE.

Upvotes: -1

Pondlife
Pondlife

Reputation: 16240

I've never used either feature, but a cursory review of the 2008 Books Online documentation makes it clear that the PASSWORD option for the BACKUP DATABASE command (I'm guessing that's what you meant?) doesn't encrypt anything:

The protection provided by this password is weak ... [it] does not prevent the reading of the backup data by other means or the replacement of the password

And apparently you shouldn't use it at all anyway:

This feature will be removed in the next version of Microsoft SQL Server

So whatever your security requirements, PASSWORD is unlikely to be useful. Whether or not TDE is useful depends on the risk you're trying to mitigate, e.g. it encrypts data on disk but not during transmission over the network. If you do implement some form of encryption, make sure you have extremely well planned and tested backup/restore procedures for a range of scenarios.

Upvotes: 1

Related Questions