How to encrypt your SQL server backups and why it’s so important

This subject is a very interesting because every time we deliver a pen test, we encounter the same problem — unencrypted SQL server backups. One time we discovered it in… a hospital. Really NOT fun.

SQL Server Backups

In this episode, we are going to be talking about protection of the SQL server backups.

This subject is particularly interesting because every single time we deliver a pen-test, we find that problem – we are able to find unencrypted SQL server backups in the different kinds of network shares.

One pen-test in the hospital

There is an interesting story related to that. We’ve been doing a pen-test at the hospital, there were little kiosks. We’re able to get access to it, take over the kiosk. Then we found that particular user or a regular domain user could access unencrypted SQL server backups. And someone, absolutely from the outside, was able to get access to all the data that was related to patient information. So you could legally check the health problems of all the people.

Encrypt your database backups – it’s important!

In this episode, I will show you how to encrypt your database backups and why it’s so important. Also what you can do and what you cannot do with backups after they are done.

Let’s start by describing a little bit of our environment. Here we have the SQL02 virtual machine. And we have also another virtual machine which is called SQL01. On both of those virtual machines, we have the newest version of SQL server installed. On this one, we have an Enterprise version. And on the other one, we have Express edition.

We will create an encryption backup from the Enterprise edition. And we’ll be restoring it on the Express edition.

Phase #1: Let’s create a backup

We have the SQL02 database, which is called, CQURE. Inside this database, I have a very simple table. Right click on it, and select 1000 rows. Let’s close it. I have security user Bob with the permission on this table to select the recourse from it.

Bob is mapped to the user. The user Bob is mapped to the login, that’s also called Bob. But remember, this is on the server line. When we are going to create a backup and restore it on the different server, you need to remember that we are backing up only the information that is under the note of the database.

Everything below here is in the backup file. When I will be moving it, all the database tables also start procedure. The same with any other objects, the permissions and, of course, the users. The part which I’m not moving is, actually, the security level of the server. Logins. But, we will not take care of this topic.

SQL server backups

Let’s create a simple backup:

  1. First, I will create a folder, where I will be storing those backups on my drive in folder CQ backup.
  2. In this folder, I will create a simple backup: Task => Backup. This is the old and default medium.
  3. I remove it, add a new one. I can browse for it => CQ backup => CQURE dot backup.
  4. I select “copy” only.
  5. Inside this folder, I have my CQ dot back.
  6. I can copy it, and go to another SQL01.
  7. I will create on the C drive of this server: New folder => CQ restore. Just to have different names for different servers.
  8. I copy and paste the file to the second server.

I will NOT switch to the second server on remote desktop connection. I will just connect using SQL server management studio to a different server.

I will choose it. The second server is the instance. It’s named instance, so I need to provide the name of the instance after the backslash, after the name of the host on which it’s hosted.

So, connect.

SQL server backups

Remember to verify which node you are currently working on

Every time you are working with the different database servers, on single SQL server studio, remember to verify on which node you are currently working.

On the top, I have SQL02, the source of my database backup. And on the bottom, I have SQL01, which is, actually the destination, which I am going to restore the database.

Phase #2: Restoring the database

  1. To simply restore the database:
  2. Right click on the databases.
  3. Restore the database.
  4. Chose the device, and, we’re under the device file, which is actually CQ restore => CQ back.
  5. Here we have backup media and we can see the backup of the database CQURE at the
  6. destination. CQURE on the new server database at the user.
  7. I will choose to relocate all the files to be on the safe side. Click okay.
  8. I see here the CQURE database, all the tables.

SQL server backups

Of course, right click on it, select top 1000 rows, this shows me also the same information. If you want to verify which server you’re currently working on, look at this new tab here, which have appeared.

Verify at the bottom which server it is. Who is the login you are using currently? What is the database inside the server?

This is CQURE database, on the SQL01. I will do the following:

  • Close it.
  • Remove this database.
  • Delete.
  • And close existing connections to this database.

Okay, so, I removed the database from SQL01 and as you’ve seen, I’m able to restore the database. Just simply, without providing any passwords or anything, I need to have the file and access to it.

Phase #3: Let’s encrypt the backup

If you want to prevent the database backup files that contain all the data, your precious data that you want to protect, just simply encrypt the backup. Of course, you can also store the backup files in a secure location. This is a very good solution. But still, usually, when we are doing the penetration tests we are finding those backups laying around in some work folders, temp folders, sometimes desktops. And even on the removable medium that we can find around the workplace.

It’s always nice to have a backup that is encrypted.

To encrypt the backup I need to:

  1. Go to backup options and select encryption.
  2. Choose a certificate or a symmetric key, to perform the backup.
  3. Currently, I do not have any certificates on my server, so I will create a new one. I’m currently connected to SQL02 and verify on the bottom.
  4. I will use a master database. I switch to master database.
  5. Now I will try to create a certificate.
  6. And we’ll see what will happen.
  7. I need to have a master key for the database. Actually, it’s for the master database. So, I will create it (also encrypted by password).
  8. This is the password which I will be using. This master key or database master key, and this key is also encrypted by using the server master key.
  9. We just created, during the installation of SQL server instance, and it’s protected by the Windows data protection IPI.
  10. So let’s create a master database key.
  11. Execute.
  12. Now I’ll be able to create the certificate, but it is always nice to have a backup of the master key that I’ve just created.

SQL server backups

Remember to create a master key

I need to create the master key only once.

If it’s created, you do not need to recreate it.

Phase #4: Creating a backup of the master key

I will be placing it in the same folder, but of course, it’s not the best solution to have the database master key or the backup of the certificates stored together with the backups.

They are protected together with the password. But it is as strong as the password so we can use the brute force attack on it.

Let’s execute.

I have this file called MMK build key. And also I will create the backup service master key. This is also nice to have so you can verify how to do it.

Let’s execute.

This service master key is also protected with the password and I am using this one. So now I can create a certificate. Because I already have the database master key, I need to provide only the subject for which I am creating the certificate. I do not need to provide the validity period. I do not need to have the encryption algorithm.

This will be by default, but of course, if you want to modify those options, you can search for the documentation and verify all the switches that you need to provide, as well as all the options that you need to provide for this command.

This one is the simplest.

SQL server backups

I’ve created a certificate called CQURE backup encryption certificate. Now I want to back up this file, because if I want to restore the backup that will be encrypted by using this certificate.

Then, of course, I need to import this certificate into the new server that I’ll be using. I will create a backup of the certificate, but first I need to provide the file name for the certificate backup. And also, what is most important, the file for storing the private key for this certificate. So, it will also be encrypted with the password.

SQL server backups

Let’s verify what we have right now

We have:

  • unencrypted backup
  • the certificate backup file
  • the private key for the certificate
  • master database master key
  • the service master key.

Those two files are not necessarily for the restore of the encrypted backup, but they are very important for us currently.

Phase #5: Creating a backup of the database

So, let’s create a backup of the CQURE database. We will be using the encryption:

  1. Backup.
  2. I will create the backup in the new file: CQ encrypted backup.
  3. In media option I need to set that this is the new media set, otherwise, I will not be able to use encryption.
  4. Backup.
  5. Encrypt backup.
  6. I can choose different algorithms here.
  7. And I will use this certificate for the encryption.
  8. Click okay.
  9. The backup is successfully created.

SQL server backups

Let’s copy the files to the new server. I will need the CQURE backup certificate. And I will need this encrypted backup:
I’m copying it => Continue => I can see the certificate if I double-click on it => Verify what is inside of this certificate this folder => I can already close.

Self Sign Certificate

In the certificate, I see that it’s not trusted because the root CA is not trusted. And it was issued to the CQ backup encryption cert. You should buy yourself Self Sign. It’s valid for one year.

SQL server backups

This is the default. In the certification path, you see a self-signed certificate. So let’s reach to the SQL01 and try to restore the database. So: databases => restore the database => from the device => select the file.

We are doing the same way we were doing with the unencrypted backup, so in the CQ restore. This time I’m using encrypted back. That’s just the name of the file.

Now as you can see, I do not see any options to restore from the backup set.

SQL server backups

Let’s try to do it with the command. On the SQL01. You can see it’s here SQL01, I will try to restore header on I will try to see what is inside of this file. Then back up.
The name of the file is changed. Let’s see. Who’s restore currently.

SQL server backups

Now, cannot find service certificate with the thumbprint. So this time I have more meaningful information than using GUI where there was no information at all about the certificates, but I couldn’t restore the database because I haven’t seen the content of the encrypted backup.

Phase #6: Restoring the certificate

Let’s try to restore the certificate used for encryption. In the master database, I will create the CQ backup encryption certificate from the file. I’m using the same files that I copied to the new server. And the same password I used for encryption of the key.

It’s requiring me to create a master key for the database because I haven’t created it before. So I will create a new master key. Of course, after this operation, I should backup it, and store it securely. But for the demo purpose, I will not do it right now.

Once we have the certificate created successfully, let’s try to see the content of the encrypted backup. I see that there is a backup of the database named Cqure. Let’s try to restore it:

Restore => from the device => encrypted file =>In the files relocate all the files => Okay.
The backup was restored successfully. I can see the content. Select 1000 rows. And you can see it’s from.

SQL server backups

The database is not encrypted

You need to remember that the database, it’s not encrypted itself. But the backup file is encrypted, and you need to have access to this certificate to restore the database backup.

Now the backups are safe and we can focus on different part of protecting our SQL server. One more thing to remember when you are restoring the databases on different servers, you will see here in the database, CQURE database, that we have the user Bob, and we have the same information that he has access to the employee’s tables with the select. But, when you go to general, we see that there’s a SQL user without a login.

Phase #7: Remap the user to a different login

After restoring the database, to the different server, we have to remap the user to a different login. Otherwise, we cannot use it. Remember to change the database use CQURE.

We are in the CQURE database on SQL01. And now one comment to exec sp_change_user_login. You need to set up the action Update_one, username Bob and the login name Bob from the new server.

Actually, on this server, we have the same login name. You can see it here on the level of the server, logins – this is the login Bob. After we execute it, can verify that in the CQURE user Bob inside the CQURE database. Bob is again mapped with the login name Bob.

SQL server backups

And we can successfully use the login Bob to access this table, as on the previous server.

That’s all. This is how you can encrypt and protect your SQL server backups

But remember you can do also encryption inside the database. For example, transporting the encryption. And protect actual data that are stored in tables, not necessarily only backups. But I think this is for some other episode.

Don’t forget to make some comments about our content. Tell us if you like it or if you didn’t. But we hope you did. And definitely, let’s get into this discussion because SQL server is a very wide subject.

Did you like this article? See how much YOU really know about cybersecurity! Test yourself against Paula Januszkiewicz. 

>>>Take Her HARDCORE Windows Security Quiz<<<