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.
- 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.
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.
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!