SQL Server Portal

The sp_configure value ‘contained database authentication’ must be set to 1 in order to %S_MSG a contained database. You may need to use RECONFIGURE to set the value_in_use is one of the new error messages come in SQL Server 2012. This error message is related to Contained database, a new database feature shipped with SQL Server 2012.

Let’s discuss this in detail:
Message Number: 12824

Severity : 16

Error Message: The sp_configure value ‘contained database authentication’ must be set to 1 in order to %S_MSG a contained database. You may need to use RECONFIGURE to set the value_in_use.

Error Generation:

Basically, I re-installed my test database server, and I had plenty of database to attach it back to the server. So I was attaching one by one and this process was performing well. Suddenly, in one database it gave me this error. Given below are the error details.

Ooopps…… I am unable to attach this database.

Resolution:
Basically, the database I was trying to attach is a contained database and please note that whenever you need to create or attach any contained database, you must enable the contained database authentication in the database server. But how ?
Given below is the script to enable it.

sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO
--OUTPUT

Configuration option ‘contained database authentication’ changed from 0 to 1. Run the RECONFIGURE statement to install.

Once you receive the above output saying  ‘contained database authentication’ has been changed from 0 to 1, you can attach or create any contained database in your database server.

Reference : MSDN