x

Unencrypted Database Backup Risks (SQL Server 2016 SP1)

What are the risks should someone get a hold of a SQL Server 2016 SP1 full database backup?

I went to the Windows store and got Hex Editor Pro. When I opened my employee database full backup I searched for sensitive information and could not find any. My conclusion is that SQL Server 2016 SP1 BACKUP must write to the .bak file so a HEX editor can not get useful information. Is this correct?

My hypothesis then is the only way a stolen unencrypted .bak file can comprised is if one installed SQL Server 2016 SP1 or higher and did a RESTORE. As SQL Server Express and SQL Server Developer Edition are freely available I understand this is a serious risk, provided a person understands that the release has to be same or higher release than the .bak file.

I am currently writing a research paper on why one should encrypt SQL Server backups and the easy of doing such a task with SQL Server 2016 and above.

Any feedback on the risks of unencrypted backups would be appreciated.

more ▼

asked Oct 12 at 08:40 AM in Default

avatar image

brockmank
51 4

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first
  • Is "backup compression" enabled? If that's the case, You cannot find any "readable" data. But this doesn't mean the backup is encrypted. it's just "zipped"

  • You can always restore unecnrypted backup on SQL instances with the same or newer version

Backup encryption is mostly provided with a certificate, which resides on a server. Make sure you have a safe copy of the certificate (if you loose it, you cannot restore your database). Which also means you can only restore an encrypted database on a server where the (Original) certificate is installed.

more ▼

answered Oct 16 at 08:54 AM

avatar image

Wilfred van Dijk
3.5k 28 39 49

(comments are locked)
10|1200 characters needed characters left

The point of using encryption is to secure your backups. As you have pointed out, any person with access to the .bak file can restore with header only to get the version of SQL Server, download the developer edition and have full access to all the data. With encrypted backups, you protect against that. In most organizations, any storage admin, system admin, etc have access to non encrypted database backup files and the organization is as risk. Organizations spends lots of money on time and hardware to protect data assets using least privileges, firewalls, etc, yet store backups on shared NAS, Data Domain, Net App, etc where the files can be easily copied and restored.

With native backup encryption being available with SQL Server 2014, more people should be using it. SQL Backup Pro offers encryption for lesser versions, and with SQL Server 2016, it takes it a step further to introduce Always Encrypted so that you can encrypt certain columns of data that contain sensitive information.

Great topic for an article!

more ▼

answered Oct 19 at 03:26 PM

avatar image

Tim
40.9k 39 92 168

(comments are locked)
10|1200 characters needed characters left

Wilfred,

I have been using the same stored procedure for more than 3 years.
BACKUP DATABASE @db_name TO DISK = @primary_backup_filename WITH NAME = @primary_backup_filename, retaindays=90, compression, description=@backup_description;

Thanks for making the compression point, that would explain why I have failed to be able to read the backup in a Hex editor. With my backup unencrypted it looks like the only way to make use of the data in a backup is to restore it with the same version or higher that created the backup.

Last week I started encrypting my development environment with a certificate and I practiced copying it to a laptop with SQL Server 2017 Express edition. Without the certificate I got an error. Creating the same certificate on the laptop a restore occurred just fine.

Thank you for the feedback. I welcome anyone else that may experiences on backup encryption. As for the certificates I need to wait for the rest of my team before implementing into production.

more ▼

answered Oct 16 at 11:41 AM

avatar image

brockmank
51 4

(comments are locked)
10|1200 characters needed characters left

Another option is to encrypt the database (called Transparent Data Encryption). This was an Enterprise feature, but not anymore since 2016SP1. Works also with certificates (so same points of attention). Be aware: this gives some CPU overhead and in order to use this with backup compression, see this link: link text

more ▼

answered Oct 16 at 12:04 PM

avatar image

Wilfred van Dijk
3.5k 28 39 49

Wilfred,

I am running SQL Server 2016 SP1 Standard Edition. https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017 says that Transparent Data Encryption is only Enterprise Edition feature.

6 days ago brockmank

You're correct. I mixed-up 'Always encrypted' with TDE

6 days ago Wilfred van Dijk
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x215
x65

asked: Oct 12 at 08:40 AM

Seen: 50 times

Last Updated: 3 days ago

Copyright 2017 Redgate Software. Privacy Policy