Editing a .bacpac file

First published on MSDN on Aug 16, 2017
There are times when for whatever reason you just wish you could make a change to a .bacpac file before you import it into Azure.

Inside a bacpac file

The inside of a bacpac is usually a closed book, you generate it and that is it you have the bacpac file and SQL Server Management Studio or one of the other tools available will take this file and import it. This is only half the story, the bacpac file itself is a zip file in disguise and if we change its name it will play happily with Windows and we can browse or extract the files. If you do then you are going to find something like this.



The three part that are of most interest to us are the Data directory, model.xml and Origin.xml.

The data directory is fairly straightforward and self explanatory it is the data from each table ready to be bcp’d into the newly created database.

The model.xml file is the schema for the database and other related data, for example the logins, and if we are so minded we can make changes here.

This file can be quite large but the top of it might be something like this, as you can see there is all the general DB meta-data and config bits and the start of a table definition.



The Origin.xml file looks like this



The checksum is very important when we are making changes to the model.xml file as this checksum is there to check that no one has gone in and hacked the model.xml file.

Editing the model.xml file

OK so let us take a look at editing the schema/metadata for a database. I am not going to say what change is being made, this is just the guide on how to do it…

1.       As noted a bacpac is actually a ZIP archive with a different extension. So first rename the bacpac and unzip it.

2.       Open the model.xml file in a text editor (Visual Studio works well for this as does notepad), and then find the part you want to change. For example if we wanted to set a password we would locate the following for user1


<Element Type="SqlUser" Name="[user1]">

<Property Name="AuthenticationType" Value="2" />

<Property Name="Password" Value="kjlasfdiowerljk" />

</Element>



3.       Make the changes as required in this example the password , it must meet the password requirements for Azure DB

For reference, the password must:

·         Not contain any portion or the user name (3-characters in length or greater)

·         Must be 8 or more characters long

·         Must contain 3 out of 4 of:

·         Lower-case characters (a-z)

·         Upper-case characters (A-Z)

·         Numbers (0-9)

·         Special characters: (`~!@#$%^&*_-+=|\\{}[]:;"'<>,.?)/

4.       Save the changes to model.xml

Editing Origin.xml

We need to generate a new checksum to put into the Origin.xml file this is done as follows.

5.       Compute a new checksum for the model.xml file using this PowerShell script:  Copy the below into a file computeHash.ps1 and execute it



Please provide the path to the model.xml file, like so: C:\temp\model.xml



$modelXmlPath = Read-Host "model.xml file path"

$hasher = [System.Security.Cryptography.HashAlgorithm]::Create("System.Security.Cryptography.SHA256CryptoServiceProvider")

$fileStream = new-object System.IO.FileStream ` -ArgumentList @($modelXmlPath, [System.IO.FileMode]::Open)

$hash = $hasher.ComputeHash($fileStream)

$hashString = ""

Foreach ($b in $hash) { $hashString += $b.ToString("X2") }

$fileStream.Close()

$hashString







5.       Open the Origin.xml file, find the line that contains the checksum value, and replace it with the newly computed checksum. The checksum line in Origin.xml looks like this:

<Checksum Uri="/model.xml">D3B38EF111CF3919FA042177D5251D5581B82BA453AC598FB1FB624B3A58B375</Checksum>



6.       Finally, re-zip the bacpac and change the archive’s extension to .bacpac.

7.       You can now import the bacpac file to Azure SQL DB.

Note:

·         It's important when zipping the bacpac file that the contents are not inside of a folder.  Instead, they must be in the top-level of the zip file.

·         The file name of Origin.xml is case sensitive – please verify that the file name is exactly Origin.xml.



Specific Example: Import fails with error 40632 creating user due to password complexity.

As you know, when a user is exported via a bacpac their password is not exported with them, instead a ‘dummy’ password is placed in the bacpac for the user. This password is generated automatically and is usually fine, however occasionally the password generated doesn’t meet the complexity requirements for Azure DB and this causes the following error to occur when importing the file.

Error Message : Exception Microsoft.SqlServer.Management.Dac.Services.ServiceException:Error encountered during the service operation.

at Microsoft.SqlServer.Management.Dac.Services.ImportJobHandler.Run(JobContext jobContext, CancellationToken jobCancellationToken)

at Microsoft.SqlServer.Management.Dac.Services.RequestProcessingHandler.Run()

Inner exception Microsoft.SqlServer.Dac.DacServicesException:Could not import package.

Warning SQL72012: The object [data_0] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

Warning SQL72012: The object [log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

Error SQL72014: .Net SqlClient Data Provider: Msg 40632, Level 16, State 3, Line 1 Password validation failed. The password does not meet policy requirements because it is not complex enough.

Error SQL72045: Script execution error.  The executed script:

CREATE USER [User1_ svc ]

WITH PASSWORD = N'uHefunyc svc <dm1cgqusiaelmsFT7_&#$!~<ZkTu4ozwfIda';



(It is usually easier to see this if you are using sqlpackage.exe/powershell  to perform the import.)

There are two ways round this the first is to just simply generate the bacpac again, chances are unless you are very unlucky it will be fine. However if this is not possible or you want to because you can you can edit the bacpac file as described above to change the password to be more pleasing to Azure DB.