SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server 2012 Download and T-SQL Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014



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.

create contained database error on SQL Server 2012
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.







SQL Server Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers










Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems