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.
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.
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