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

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

Select mdf data file to attach database to SQL Server instance

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.

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.

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

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

Exit screen with Cancel options

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

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

Now execute following command to alter configuration settings

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.