sql server - How to prevent backup db file to load on any SQL server instance

Question

I am trying to find if there is any way we can restrict loading of a .bak file to be loaded on SQL server instance.

Example:

User named "Zack" created and is dba of a database named "AdventureWorks" he takes a daily backup of it's database and stores it in a folder.

User "Mike" gets access to one of the .bak file and loads the entire database with it's data locally.

How can we restrict this and let only authorized user to restore .bak file in SQL Sserver.

Tried to look at multiple documents by Microsoft but not able to find any resources on this use case. Your insight will be really helpful as I have ran into a roadblock.

• Charlieface: Encrypt your backups with a certificate that is backed up elsewhere learn.microsoft.com/en-us/sql/relational-databases/…

Answer 1

In order to secure data, think defence in depth. That is, you need layers of security. If one layer is breached, the next one might stop the intrusion and so on.

Start by thinking what kind of threats you are trying to protect against. Who can you trust and who you cannot? Are your servers secured? Do you trust all the admins? A runaway developer has different attack vector than a server admin. The former can, for example, extract data by running an application. The later can, again as an example, take a copy with storage snapshot and work around ACLs on a separate system.

The most straight-forward (but not easiest) thing to do is to make sure no unauthorised user gets access to the backup files. If that fails, backup encryption might be the next safeguard. If backup encryption keys are compromised, the next layer is to encrypt data within the database. This can be done with built-in features, or by handling the encryption on application layer with custom soluton or by using the always encrypted feature.

Start by making sure no untrusted user has permissions to back up a database. Backing up a SQL Server database requires at least db_backupoperator role membership, so review the role memberships. This should protect you against accidental data leak by, say, a developer making an ad-hoc backup to a random location.

The next step is to make sure all backups are stored in a location that has appropriate ACLs. That is, there should be a security group that allows SQL Server instance to access the location, maybe your backup application, and such sysadmins that do work with backups. Revoke any extra permissions. This should protect you against someone reading (or altering!) the backups.

The third step is to review what happens to the backup after it's taken. Is there a process that moves it to another a tier in storage? Maybe a cloud storage? Review all the locations and related permissions. This is to make sure that the backup isn't copied in, say, S3 bucket or Azure blob storage that has too letient read permissions.

The fourth step is to start thinking if encryption helps. After all, the first thing to do is to back up your encryption keys - and make sure no unauthorised party can read the keys. Setting up encryption in such a way that it works needs some serious planning, and regular testing of procedures.