Azure BACPAC restore fails randomly from Azure Devops

0
votes

I'm trying to import a bacpac in an Azure DevOps build via the Azure SQL Database Deployment plugin. The problem that it fails randomly(about 50% of the time it succeeds) with the following error:

*** Error importing database:Could not import package.
Error SQL72016: Cannot open database "TestDb" requested by the login. The login failed.
Login failed for user 'TestUser'.
Cannot open database "TestDb" requested by the login. The login failed.
Login failed for user 'TestUser'.
The Azure SQL DACPAC task failed. SqlPackage.exe exited with code 1.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-

I ran the Azure DevOps build with debugging and saw the following error:

##[debug]Failed to reach SQL server tcp:testurl.com,1433. Cannot open database "TestDb" requested by the login. The login failed.
##[debug]Login failed for user 'TestUser'.

Other times the dacpac import succeeds without any problems. I've exported the dacpac from azure and do not get compatibility warnings.

I suspect that creations of the database in SQL takes so long that the import function fails is there any way to solve this.

1
azureazure-devopsazure-sql-databasedacpacbacpac

1 Answers

0
votes

I struggled a lot with the same issue. I did manage to fix it by doing below two steps

  1. Changed database connection to a different server and once that is a the success I revert it back to the current server.
  2. Changed the database compatibility level to below 150.
  3. Created fresh release

below references helped me. Hope this helps.

https://geeklearning.io/dacpac-and-azure-sql-updates/

https://github.com/microsoft/azure-pipelines-tasks/blob/master/Tasks/SqlAzureDacpacDeploymentV1/README.md#troubleshooting-i