26

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?

  • 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 '19 at 8:59
  • @RalphWillgoss do you want to add an answer? – Michael Green Jan 13 at 8:33
  • 1
    @MichaelGreen Its been a while but I believe the accepted answer is correct for this context. – Ralph Willgoss Jan 13 at 20:26
41

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.