What the hell!? I can't deploy my dacpac to Azure SQL!

By Cyprien ,

When dealing with databases in the cloud, you have various choices of strategy to update its schema and run data migrations. Most of people will pick some ORM migration tooling. However if the database is not yours or if you want more control on it, you might have opted for the DAC tooling.

What's good about dacpac:

  • You can model pretty much everything inside a Database Visual Studio Project without an ORM constraints. You get exactly what you want
  • It is versioned. It might not sound like much, but it is not uncommon to have a range of environments each at different stage. Having a system which knows what to do when presented with a dacpac than has drifted by a couple of version is actually very helpful.
  • The snapshot capability enables complex migrations scenarios when combined with tools like SQL Incremental Deployment
  • It produces build artifacts which play along very well with a devops release pipeline
  • Deployment will not be running from your application processes (hence you don't need to give it excessive rights) but from a deployment process using SqlPackage.exe tool as a step of your release deployment

SqlPackage is great but...

It tends to have far from perfect error messages. The worst one is the following, it usually happens when you create a brand new environment:

##[error]*** Could not deploy package.
##[error]Unable to connect to master or target server 'database_name'. You must have a user with the same password in master or target server 'database_name'.

##[error]The Azure SQL DACPAC task failed. SqlPackage.exe exited with code 1. Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-

When you see that message you first triple check your username, password etc... Usually with no lock. Then you head to the link provided where you can read:

Possible cause of failure include connection failures :

  • Check if any firewall rules are preventing the connection.
  • Check if the supplied credentials have appropriate permission to either the master DB or the specific DB.
  • Check if the SQL server name resolution succeeds.

It drives you even more crazy. None of those apply to you. And also you other envs are behaving just fine...

It's because SQLPackage tend to fallback to that message for unrelated things.

What really happens

Well Sql Azure is a PaaS Service which means it receives update transparently and relatively often. Some of them are big ones and introduce a new compatibility level.

As you can see on the screenshot above, Sql Azure is now 150.

When you create a new database it will be configured using the latest version. However, your machine, your deployment agents might not have the latest tooling deployed.  

When the tooling does not match the compatibility level of your database, the deployment will fail with the message described earlier.

What can I do

Well SQL Azure team expects people to keep their DAC tooling up to date (which sounds fairly reasonable) and this is a good thing to do.

However, if you are deploying using VSTS Hosted Agent you are not in control, and it will always lag a little behind. In that case, the easiest fix is to reduce your database Compatibility level. This is quite easy as you just need to run an SQL Statement on the database as follow :

ALTER DATABASE database_name   
SET COMPATIBILITY_LEVEL =  130;

Conclusion

I hope this will help you if you run in the same problem. In the past I suggested to the vsts team that they introduce a tool installer (same as for node, dotnet or yarn). However for some reason they didn't follow up. An explicit error message would also be a huge winning step.