question

brockmank avatar image
brockmank asked

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.
securitybackup-restore
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
- 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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

brockmank avatar image
brockmank answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
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][1] [1]: https://blogs.msdn.microsoft.com/sql_server_team/backup-compression-for-tde-enabled-databases-important-fixes-in-sql-2016-sp1-cu4-and-sql-2016-rtm-cu7/
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
You're correct. I mixed-up 'Always encrypted' with TDE
0 Likes 0 ·
Tim avatar image
Tim answered
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!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.