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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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)
Code

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
Code

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
Code

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
Code

Or using SQL Server Management Studio the contained database in SQL Server can be created without any configuration error.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.