So we have an Azure SQL database that previously we have downloaded onto a test server every night both as a backup and also to act as a source of metrics and a test copy of the database. Apart from a few hiccups over the years (mostly when I updated the Azure instance and didn't check the backup process), it works OK.

The backup is a .net exe which runs in Task scheduler and calls an old SOAP web service to create the bacpac and store it in Blob storage. It is then downloaded and I call SqlPackage.exe to restore the bacpac onto a local instance of SQL Server.

Then the other day, we created a Contained user - a user that is linked to the database rather than a server login and which allows geo replication to work without screwing up permissions during the copying (since the users are really a security ID not just the display name). This all worked online but again, it broke the backup process or specifically the database restore process.

Long story short, the web service we were using to create the bacpac does not add the "Containment" setting in the output. This might be because Azure seems to cheat this setting and automatically have it enabled regardless of what settings your database has. I tried to use their new service API and I just couldn't get it to work. I then looked at the documentation for their .Net libraries which was woefully lacking in guidance. I decided for now to manually modify the bacpac to at least get a working database.

The bacpac is just a zip file with another name so renaming it from bacpac to zip, you can then extract the files to make them easier to work with. I then edited model.xml and added  at the top where the other settings are. This actually breaks the package because there is a checksum in origin.xml.

Fortunately, there is a program which calculates the new hash for you called dacchksum and which is hosted on github here. The idea is to put back all the stuff into the bacpac again and run the program against it which will tell you what is currently in the file by way of check sum and what should be. You then copy the value it should be, replace the current value in origin.xml and package it up again.

IMPORTANT. I had a problem with the app telling me the package was invalid and the github source doesn't build in order to debug it. The problem is caused by the way that windows sends files to a compressed folder. If you right-click the entire folder, you get a zip file of that name but the zip has a root folder inside it before adding the child files. In other words, if you want to make the package, you must select the files to exist at the root level of the package (i.e. model.xml, origin.xml etc.) send those to the compressed folder and then rename it to what you want.

I did this and then imported the bacpac manually, which looked like it worked OK but it did not correct import the password for an Azure contained user. I checked the model and it does have some encrypted password information but for reasons I have yet to work out, this doesn't work - possibly because it uses a server key for security reasons, I don't know.

So be careful anyway, these portable users are not obviously portable - although the geo-replication seems to work OK!