SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

 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;
EXEC sp_configure 'contained database authentication', 1;
EXEC sp_configure 'show advanced options', 0;

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.

Filed under: Blog Tagged: contained databases, sql server, syndicated, T-SQL

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...