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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




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



sp_configure Contained Database Authentication for Database Attachment

While trying to attach database files from previous SQL Server installations on SQL Server 2017 I got The sp_configure value 'contained database authentication' must be set to 1 in order to create a contained database error message from SQL Server Management Studio. Since contained databases require additional configuration on SQL Server, attaching data files of a contained database on a new SQL Server instance also requires similar configuration using sp_configure stored procedure to modify contained database authentication configuration option.


I launched SQL Server Management Studio (SSMS) and connected to the SQL Server instance (SQL Server 2017 Developer Edition instance on my local computer) where I want to add KodyazSEO database.

I have already copied .mdf and .ldf data and log files in DATA folder of the target SQL Server instance.
C:\Program Files\Microsoft SQL Server\MSSQL14.KODYAZ2017\MSSQL\DATA

data file to attach as new database to SQL Server

On Object Explorer window, on Databases node I right-click to display context menu and then choose Attach...

attach new database on SQL Server 2017 Management Studio

Select mdf data file to attach database to SQL Server instance

SQL Server database mdf data file to attach

Click OK

If necessary database administrator can make changes on this screen. For example, the new database that will be generated on the current SQL Serve instance can be renamed. This is especially a necessity if there is already another database with the same name on the SQL Server instance.

The same naming changes can be done for the data files or for the log files of the target database.

SQL Server databases to attach and database details

Since I did not require to make changes on the attachment configuration on this screen, I tried to complete the database attachment task by pressing OK button. But I got following error message:

An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.

An error occurred when attaching the database

If you did not see the hyperlink, check the last column in the table in first section.

error details for database attachement on SQL Server 2017

TITLE: Microsoft SQL Server Management Studio
Attach database failed for Server 'myserver\kodyaz2017'. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
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)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.3015&EvtSrc=MSSQLServer&EvtID=12824&LinkId=20476

The sp_configure value contained database authentication must be set to 1 in order to create a contained database

Exit screen with Cancel options

Launch SQL Editor on a database for example master on the target SQL Server instance.
Execute sp_configure

sp_configure

You see my SQL Server 2017 instance configuration for contained database authentication is currently 0 as seen in run_value column.

SQL Server 2017 instance configuration parameter contained database authentication

Now execute following command to alter configuration settings

sp_configure 'contained database authentication', 1
reconfigure

You will see message "Configuration option 'contained database authentication' changed from 1 to 1. Run the RECONFIGURE statement to install." but we have already executed reconfigure in above script.
So do not bother if you have already executed using above SQL code block.

If you execute sp_configure, you can see the run_value is now set to 1.

We are now ready to attach contained database file on SQL Server 2017 instance again.







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2018 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems