sp_configure Contained Database Authentication to Create Contained Database in SQL Server 2012
SQL Server 2012 database administrators must configure contained database authentication SQL Server configuration option before they create contained database in SQL Server 2012 using sp_configure system procedure. Other wise, if "contained database authentication" is not set to 1 and enabled, an attempt to create SQL Server 2012 contained database will cause an error.
Contained databases are new with SQL Server 2012 and enables easy migration of a database from one SQL Server server to another with all its authenticated user information like login users, etc.
sp_configure value 'contained database authentication' must be set to 1 in order to create a contained database
Recently I tried to create contained database on a fresh SQL Server 2012 installation.
In order to create contained database on SQL Server 2012, in Options tab database admins can set the containment type to Partial instead of default None option value.
Since most of SQL Server configuration options are still as default which are set during installation, I got the following error message during SQL Server contained database creation process.
SQL Server error during create contained database in SQL Server 2012 before configure contained database authentication using sp_configure
Below I copied some partial error message text shown with above SQL Server 2012 database create error.
TITLE: Microsoft SQL Server Management Studio
Create failed for Database 'Kodyaz'. (Microsoft.SqlServer.Smo)
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. (Microsoft SQL Server, Error: 12824)
Database administrators and SQL developers will also have the same SQL Server configuration error even they try to create contained database using T-SQL script as follows:
CREATE DATABASE [Kodyaz] CONTAINMENT = PARTIAL
Set 'contained database authentication' using sp_configure in SQL Server 2012
In SQL Server 2012, to set contained database authentication using sp_configure, following SQL script can be used. When a database administrator set 'contained database authentication' SQL Server configuration option to 1, he or she can create contained database on SQL Server 2012.
-- First enable advanced options
sp_configure 'show advanced options', 1
RECONFIGURE
GO
-- Set contained database authentication to create contained database on SQL Server
sp_configure 'contained database authentication', 1
RECONFIGURE
GO
-- Set back show advanced options configuration option
sp_configure 'show advanced options', 0
RECONFIGURE
GO
After 'contained database authentication' SQL Server configuration option is successfully set from 0 to 1, SQL professionals are now enabled to create contained database in SQL Server 2012 instance. Either using below SQL create database command,
CREATE DATABASE [Kodyaz] CONTAINMENT = PARTIAL
Or using SQL Server Management Studio the contained database in SQL Server can be created without any configuration error.