Day 2 of 31 Days of Disaster Recovery: Protection From Restoring a Backup of a Contained Database
31 Days of Disaster Recovery
Welcome to day 2 of my month-long series on Disaster Recovery. For today’s post, I want to talk a little bit about restoring backups of contained databases. In particular, what protections are in place in case you are given a backup of a contained database to restore without being told it has containment enabled.
If you missed yesterday’s post in the series, you can check it out here:
- 31 Days of disaster Recovery
- Does DBCC Automatically Use Existing Snapshot?
Restoring a Contained Database
One of the first concerns I had when I learned about contained database was how am I as a DBA protected against giving someone access to a database without my knowledge. Well, there are several layers of protection starting with the fact that it is up to the administrator to enable containment before it even comes in to play.
First Layer of Protection
Containment has to be enabled at the server level before a contained database can be restored or created or before an existing database can have containment enabled. As long as you do not enable this setting at the server level, you’re completely protected. You can enable it via sp_configure or via the Server Properties dialog (Advanced tab). Despite the fact that it is listed on the Advanced tab, it is not an advanced option. You do not have to enable Show advanced options before changing or checking this setting. I see a lot of blog posts stating that it is an advanced option and showing that the Show advanced options has to be set, but it is not and does not.
In addition to sp_configure and the Server Properties dialog, you can check the current setting by querying the sys.configurations table. this is my preferred method for querying for configuration settings because it is a lot easier to use in automation scripts. However, if my intent to change it if it needs it, I generally use sp_configure.
If you have not enabled containment at the server level at you attempt to restore a database that has containment enabled, the restore will fail with an error telling you that containment has to be enabled first. This is a warning to the DBA that the database is contained. It is up to you to decide whether enabling containment is the proper thing to do or not. Please don’t just enable it because the error message said to. This affects the security of your server and databases.
The error message you will receive is:
Msg 12824, Level 16, State 1, Line 1
The sp_configure value 'contained database authentication' must be set to 1 in order to restore a contained database. You may need to use RECONFIGURE to set the value_in_use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.The option that you are enabling or disabling is contained database authentication and the command to enable/disable containment at the server level is as follows:
-- Enable "contained database authentication" Exec sp_configure 'contained', 1; Reconfigure; -- Disable "contained database authentication" Exec sp_configure 'contained', 0; Reconfigure;
Second Layer of Protection
You will notice that I used RECONFIGURE in that code and not RECONFIGURE WITH OVERRIDE. I did so for a very important reason. I will get to that later, but first, let’s take a look at the next layer of protection. Let’s move a step further and see how you are protected after you have enabled containment at the server level. Once the option is enabled, you will get no warnings whatsoever when you restore a contained database. It’s up to you to make sure you know whether or not the backup you are going to restore is contained or not. Fortunately, there is a built-in mechanism for discovering that.
If you are presented with a backup of a new database, you can check for containment yourself. You don’t have to rely on being told about it. A new column (Containment) has been added to the output of the RESTORE HEADERONLY command. You can use this command to inspect the header information for the backup before restoring it. If Containment = 1, containment is enabled in the database. If Containment = 0, it is not enabled.
In this example, I am checking the header information of a backup named c:\bak\CDTest.bak:
Restore HeaderOnly From Disk = 'c:\bak\CDTest.bak';
It’s very easy to check and there is no reason not to perform this check before restoring a database if the server has containment enabled.
Final Layer of Protection
Let’s say you are in a disaster recovery scenario and you need to get the database back online as quickly as possible, but when you attempt to restore the database on a replacement server, you discover that it has containment enabled. Now let’s assume that you had not been aware that this setting was enabled and to the extent of your knowledge and available documentation, the database does not use contained users. Furthermore, let’s assume that there may be some compliancy regulations or security policies that say that you can’t have undocumented contained users. You may not have time to hunt down someone that can explain why there are contained users. You need to get the database restored and also respect your security restrictions.
One option you can do is to enable containment at the server level, restore the database, and then disable containment at the server level. If you try to disable containment at the server level using sp_configure and RECONFIGURE, you will get an error message stating that you can not disable containment because there are contained databases. You can force disabling of containment at the server level by using RECONFIGURE WITH OVERRIDE. This is why I was sure to use just RECONFIGURE in the earlier example. I want to ensure that I don’t accidentally disable the setting if there are contained databases without realizing it.
Here is what happens when you try to disable containment at the server level when a contained database already exists:
-- Disable "contained database authentication" Exec sp_configure 'contained', 0; Reconfigure;
Configuration option 'contained database authentication' changed from 1 to 0. Run the RECONFIGURE statement to install.
Msg 12818, Level 16, State 1, Line 3
RECONFIGURE failed. Attempting to change the 'contained database authentication' value to 0 while there are existing contained databases requires a RECONFIGURE WITH OVERRIDE.-- Force disabling of "contained database authentication" Reconfigure With Override;
Command(s) completed successfully.
Summary
There are built-in protections and mechanisms for protecting yourself from restoring a contained database, but there is also a responsibility on us as administrators to make sure we understand our actions and don’t get blind-sided by a contained database. These built-in features should be your second line of defense, and good practices and being diligent about being aware of what we are doing and the implications our actions should be our first line of defense. It’s on us to secure our databases and servers and the built-in functionality is just a fail-safe.
