Contained Databases – Server Setting Matters
In doing some additional testing on contained databases, I decided to create a new database on a new test VM.
CREATE DATABASE cdb1 containment = PARTIAL ;
To my surprise, I got this error:
Msg 12824, Level 16, State 1, Line 1
The sp_configure value ‘contained database authentication’ must be set to 1 in order to create
a contained database. You may need to use RECONFIGURE to set the value_in_use.
I checked the server setting, and sure enough the instance property was set to 0 (false).
At first you might think this shouldn’t matter, but imagine you go to attach a backup of a contained database to an instance that doesn’t have this enabled. However there are a few security and administrative concerns over contained databases. We have the password policies, the potential collision of user names, and more.
The easy fix is to enable the instance level setting. That’s easily done with this code:
-- Set advanced options EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure 'contained database authentication', 1; EXEC sp_configure 'show advanced options', 0; GO RECONFIGURE WITH OVERRIDE; GO
What about restoring a contained database backup? Surely it will just come online without the contained authentication?
I tried it, before running the script above, and I got this error:
Clearly the instance level setting matters. It’s easy to change, either in script or the GUI. However if you use the GUI, please don’t click OK and save the changes. Use the script button, save that for your logging/documentation, and then run the script.