Error SQL72016: Cannot open database XXX requested by the login. The login failed

Question

We are facing an issue related to bacpac import from local SQL instace to Azure SQL.

We generate a bacpac in our local SQL (DAC\140..\sqlpackage) and then we import this bacpac to Azure. We are facing this issue:

Error SQL72016: Cannot open database XXX requested by the login. The login failed

This problema does not happen always, we have also this warning:

Warning SQL0: A project which specifies SQL Server vNext CTP as the target platform may experience compatibility issues with Microsoft Azure SQL Database v12.

How can we solve that problem?

Thank you

Answer 1

Please update your version of Data-Tier Application Framework from here. The version you are using is too old.

Make sure the database you are migrating to Azure SQL does not have Windows logins that cannot exist on your Azure environment.

Answer 2

According the warning message, you have the compatibility issues with Microsoft Azure SQL Database v12. This may be the reason cause the import error.

Summary:

As of January 2018, in Azure SQL Database, the default compatibility level is 140 for newly created databases. We do not update database compatibility level for existing databases. This is up to customers to do at their own discretion. With that said, we highly recommend customers plan on moving to the latest compatibility level in order to leverage the latest improvements.

Database compatibility level is a valuable tool to assist in database modernization, by allowing the SQL Server Database Engine to be upgraded, while keeping connecting applications functional status by maintaining the same pre-upgrade database compatibility level. As long as the application does not need to leverage enhancements that are only available in a higher database compatibility level, it is a valid approach to upgrade the SQL Server Database Engine and maintain the previous database compatibility level.

Execute the following query to determine the version of the Database Engine that you are connected to:

SELECT SERVERPROPERTY('ProductVersion');

To determine the current compatibility level, query the compatibility_level column of sys.databases.

SELECT name, compatibility_level FROM sys.databases;

Sets certain database behaviors to be compatible with the specified version of SQL Server.

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 } 

For details, please see: ALTER DATABASE (Transact-SQL) Compatibility Level

I think your Azure SQL database compatibility is 140, but your SQL Server database engine compatibility is 130. That's the compatibility issues. Please update it to the latest version and test again.

Hope this helps.