19

I recently tried to restore to my local development SQL Server a backup from a network instance. To my surprised I received the following error message:

Msg 12824, Level 16, State 1, Line 3 The sp_configure value 'contained database authentication' must be set to 1 in order to restore a contained database. You may need to use RECONFIGURE to set the value_in_use. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating abnormally.

What steps must I follow to successfully restore the database?

closed as off-topic by Colin 't Hart, RLF, Vérace, Max Vernon, RolandoMySQLDBA Jun 11 '15 at 23:51

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Colin 't Hart, RLF, Vérace, Max Vernon, RolandoMySQLDBA
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    What version of SQL Server? The error is pretty clear about a resolution, did you try setting the contained database authentication value to 1 like it said to? – LowlyDBA Jun 11 '15 at 13:15
  • 1
    I think this is a valid question, if you are trying to restore a Azure Sql Database .bacpac file, into your local instance - you get this error. The answer is valid in fixing this. – Ralph Willgoss May 15 at 8:59
33

In order to restore a contained database into a different instance of sql server, in this case my local server, the "Enable Contained Databases" property must be set to True.

You can do this from management studio:

  1. Right-Click on the server instance, select Properties
  2. Select Advanced page, set under Containment the property value to True
  3. Proceed to restore the database backup.
  4. ALTER AUTHORIZATION ON DATABASE::ReplaceThisWithYourDatabaseName TO ReplaceThisWithLeastPrivilegeUser;

Here are the script lines I actually used for enabling/disabling containment:

-- Enable "contained database authentication"
EXEC sp_configure 'contained', 1;
RECONFIGURE;

-- Disable "contained database authentication"
EXEC sp_configure 'contained', 0;
-- Force disabling of "contained database authentication"
RECONFIGURE WITH OVERRIDE;

For additional information please refer to:
http://www.sqlsoldier.com/wp/sqlserver/protectionfromrestoringabackupofacontaineddatabase

2

Have you tried doing what the error is describing?

USE master
GO
sp_configure 'show   advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'CONTAINED DATABASE AUTHENTICATION', 1
GO
RECONFIGURE
GO
sp_configure 'show   advanced options', 0 
GO
RECONFIGURE
GO

Not the answer you're looking for? Browse other questions tagged or ask your own question.