If you have ever tried to bring your Azure SQL Database data to local instance through .bacpac file then there are high chances that you must have seen this error as shown below specially if it was your first time –
Before explaining this error and the solution let me assure the beginners here that the step you have followed by navigating to the following context menu is absolutely correct –
Yes, .bacpac file is restored to local instance database by doing Import Data-tier Application. I have seen some people suspecting this as that they relate data-tier to .dacpac file which is just a schema file whereas bacpac file is schema plus data.
Let us now refer back to the error shown above and if you read it carefully this clearly says that the source (i.e. your bacpac file and eventually your source database) has users which rely on external provider authentication and is not supported by the target i.e. your local instance. If you recall, Azure SQL Database is a database-as-a-service (DBaaS) which means this is an independent database with no dependency on instance level features and hence the users are contained users there i.e. not driven by the login concept. So, in order to restore this database to you local instance, contained users must be supported.
And that’s why you get this error if contained users have not been enabled on the target instance. Let us see how to check if this is enabled on your machine or not and how to enable this.
Simply run the following query in SQL Server Management Studio –
USE master
GO
sp_configure 'contained database authentication'
GO
On my instance, this gives me the following result –
i.e. contained database authentication is disabled so let’s enable it as by running the following query –
USE master
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE
GO
Once this is enabled and you retry to restore the bacpac file again, it gets restored successfully.
If you have not faced this issue then this setting must have already been enabled on your instance. However, if you want to experiment it again, you may disable this setting and then make an attempt to restore a bacpac file. Note that if you try to disable this setting and if there are already some databases with contained users then you will get the following error –
which can simply be overcome by using RECONFIGURE WITH OVERRIDE option than RECONFIGURE.
At the end, note that this post referred Azure SQL Database as this was asked by a colleague but this error is not necessarily for Azure SQL Databases as a source. It may happen for your on-premise SQL Server databases too which have contained users. Data-tier applications (bacpac file) and contained users concept is not native to cloud implementations.