Azure SQL Database Always Encrypted - How to move a database to a new Azure Tenant

Always Encrypted  is a feature designed to protect sensitive data, stored in Azure SQL Database or SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). 

If a database has Always Encrypted enabled then it has at least one Column Encryption Key (CEK) with can be found in the sys.column_encryption_key_values system catalog view.

If you run this query

select column_encryption_key_id, column_master_key_id, encryption_algorithm_name, encrypted_value from sys.column_encryption_key_values

It returns something like that

 

The encrypted_value column is a CEK - random-generated key encrypted with the Master Encryption Key (MEK) which is stored in an Azure Key Vault. 

To see the master encryption key details use this query

select name, column_master_key_id, key_store_provider_name, key_path from sys.column_master_keys

the output would look like this

The master encryption key which leaves inside the key vault (the key_path column stores its URI) and is used to decrypt the CEK value. If we move the database to a different Azure AD tenant to access the encrypted data we have to find a way to get the same key into our new key vault in the new tenant. This is problem #1 for us to solve.

Azure Key Vault Key can be backed up and restored using, for example, PowerShell cmdlets Backup-AzKeyVaultKey and Restore-AzKeyVaultKey.

Seems simple, but the key vault must use the same subscription and be in an Azure region in the same geography (see https://azure.microsoft.com/en-ca/global-infrastructure/geographies/).

For example, Canada Central and Canada East are regions within the same geography but Canada Central and East US are not.

Problem #2 is that the key_path column stores the master encryption Key URI and if we need to copy the database to a different Azure AD tenant we have to find a way to update this value with a new URI pointing to a key in a Key Vault in the new Azure AD tenant.

To solve problem #1 we can:

1. Generate a PFX file
2. Load the PFX file into the original key vault (as a new Key or as a new version of the existing key - this doesn't matter)
3. Change the MEK to use the new key (this is technically called 'rotate the key')
4. Load the same PFX into a new key vault in the new Azure AD Tenant

What can we do to solve problem #2?

Any SQL Database whether it has always encrypted columns or not can be exported using either SQL Server management studio or the

If you export a database it will result in a BACPAC file. the bacpac file is just a zip archive. If you unzip a bacpac file you'll see the following file structure

The Data folder has sub-folders for each of the tables in the original database. within the sub-folder the data exported using the BCP command.

Two files we need to deal with are

• model.xml
• origin.xml

In the model.xml file there is a section with the details about the master encryption key (as you can see it stores exactly the same values as reported off the 

And in the origin.xml file at the very bottom there is the checksum section which stores SHA-265 hash of the model.xml file.

  <Checksums>

    <Checksum Uri="/model.xml">2C59676C5542CBE3BFE36DC1A3F954FA561193357F04A214A3DEE010757F641A</Checksum>

  </Checksums>

Thus, to move an Azure SQL database with always encrypted we need:

1. generate a PFX file and load it into the original key vault
2. make this key a new MEK for the database
3. load the same PFX file into the key vault in the new Azure tenant
4. export the database as a bacpac file
5. unzip the bacpac file
6. replace in the model.xml file key URI with the new URI fromstep #3
7. calculate new SHA-256 hash of the model.xml file and replace the hash in origin.xml
8. zip the files up into a new bacpac file
9. import the bacpac file to an Azure SQL server in the new tenant.

Now let's explain what the PowerShell script at the bottom of this blog is doing:

• Various checks on the source and the target:
• checks if 7zip files required for unzip/zip operation are  available
• The built-in cmdlets Compress-Archive & Expand-Archive are slower and couldn't zip 3 GB archive without throwing out of memory error and that's why 7zip is used by the script
• checks if SqlPackage.exe exists (64-bit version only)
• checks if the source Database exists and has a MEK
• checks if the target Azure SQL server exists
• checks if the Key Vault and the key the MEK points to exist
• checks if there is no database with the same name as the source DB on the target server
• checks if the Key vault in the target Tenant exists
• creates a new name for the MEK (increments the existing MEK name. If the current MEK name is csp5001-CMK-9 then the new one would be csp5001-CMK-10)
• generates a self-signed certificate with Key Spec = Signature (this is required for the private key to be used for a new MEK)
• exports the certificate in a PFX file with a random password
• Loads the PFX file into the source Key Vault as a new version of the existing key
• Makes the new key a new MEK (rotates the key)
• Loads the PFX file into the new Key Vault in the Target Azure AD and names this key as the key in the source Key vault
• exports the database using SqlPackage.exe
• unzips the bacpac file
• replaces in the model.xml file the original key URI with the URI pointing to the key in the Key Vault in the target Azure AD
• calculates SHA-265 hash for the updated model.xml file 
• replaces the original hash in the origin.xml with the new hash
• compresses the folder into a zip file
• renames the zip file to be a bacpac file (changes the extension)
• imports the bacpac into the target Azure SQL server using the SqlPackage.exe command

And that's it. Now the database is in a new Azure tenant with always encrypted columns.

You may want to rotate the MEK in the target SQL server as soon as the migration completes.

Here is the script:

<#

    Script moves an Azure SQL Database with AlwaysEncrpyted columns to a new Tenant.

    See 

    

    http://smizrohi.blogspot.com/2020/07/azure-sql-database-always-encrypted-how.html

    

    for the detailed description of the script.

    The script uses 7zip portable for compression.

    Downlowd it from --> https://www.7-zip.org/a/7z1900-extra.7z

                           PowerShell cmdlets Compress-Archive & Expand-Archive 

                                      throw out of memory error even on 3GB archive

                                      and much slower than 7zip

    Parameters:

        srcTenantID          - source Tenant ID                             

        srcSubscriptionID    - source Subscription 

                             

        srcApplicationID     - source Application ID

                                 the application must have permisisons to:

                                    1. add Access Policy to the Key Vault where Database master Encryption Key is stored

                                         The access policy will grant Get,Import,Verify,unwrapKey,wrapKey,sign to Application ID

                                    2. rotate the Master Encryption Key within the Database

                             

        srcApplicationSecret - source Application Secret

        srcSQLserverName     - source Azure SQL Server name

        srcSQLUserName       - SQL User Name with access to the database (db_owner)

        srcSQLUserPassword   - password for srcSQLUserName

        tgtKVname            - target Key Vault name where the key will be created 

                                  (the key name will be the same as in the Key Vault used by the source Database)

        tgtTenantID          - target Tenant ID

        tgtSubscriptionID    - target Subscription ID

        tgtApplicationID     - target Applicaiton ID

                                 the application must have permisisons to add Access Policy to the Key Vault

                                 where Database master Encryption Key will be stored.

                                 The access policy will grant the Import import permission to Application

        tgtApplicationSecret - target Applicaiton Secret

        tgtSQLserverName     - target Azure SQL Server name 

        tgtSQLUserName       - target SQL User Name (db_manager)

        tgtSQLUserPassword   - target SQL user password

        DatabaseName         - Database name with Alwyas Encrypted settings

Example:

CopyDbToNewTenant `

    -srcTenantID 'ee7*******************'   `

    -srcSubscriptionID '9bc*************************' `

    -srcApplicationID '053***************************' `

    -srcApplicationSecret '******************************' `

    -srcSQLserverName '************' `

    -srcSQLUserName '***********'  `

    -srcSQLUserPassword '***************' `

    -tgtKVname '***************'  `

    -tgtTenantID  '86fe********************'  `

    -tgtSubscriptionID 'c115***************************' `

    -tgtApplicationID '393b*************************' `

    -tgtApplicationSecret '******************' `

    -tgtSQLserverName '**************' `

    -tgtSQLUserName '*********' `

    -tgtSQLUserPassword '**********' `

    -DatabaseName 'myDb' `

  

#>

function CopyDbToNewTenant {

    param (  

        [Parameter(Mandatory=$true)][ValidateScript({try {[System.Guid]::Parse($_) | Out-Null; $true } catch {$false} })][String]$srcTenantID,

        [Parameter(Mandatory=$true)][ValidateScript({try {[System.Guid]::Parse($_) | Out-Null; $true } catch {$false} })][String]$srcSubscriptionID,

        [Parameter(Mandatory=$true)][ValidateScript({try {[System.Guid]::Parse($_) | Out-Null; $true } catch {$false} })][String]$srcApplicationID,

        [Parameter(Mandatory=$true)][ValidatePattern('\S{8,64}')][string]$srcApplicationSecret,

        [Parameter(Mandatory=$true)][String]$srcSQLserverName,

        [Parameter(Mandatory=$true)][String]$srcSQLUserName,

        [Parameter(Mandatory=$true)][String]$srcSQLUserPassword,

        [Parameter(Mandatory=$false)][String]$7zipPath = 'C:\BDO\BVI-ESS\Scripts\BVI-DataMigration\',

        [Parameter(Mandatory=$false)][String]$CMKstring = 'CMK',

 

        [Parameter(Mandatory=$true)][ValidatePattern('^[a-zA-Z0-9-()]{3,24}$')][string]$tgtKVname = 'KeyVault',

        [Parameter(Mandatory=$true)][ValidateScript({try {[System.Guid]::Parse($_) | Out-Null; $true } catch {$false} })][String]$tgtTenantID,

        [Parameter(Mandatory=$true)][ValidateScript({try {[System.Guid]::Parse($_) | Out-Null; $true } catch {$false} })][String]$tgtSubscriptionID,

        [Parameter(Mandatory=$true)][ValidateScript({try {[System.Guid]::Parse($_) | Out-Null; $true } catch {$false} })][String]$tgtApplicationID,

        [Parameter(Mandatory=$true)][ValidatePattern('\S{8,64}')][string]$tgtApplicationSecret,

        [Parameter(Mandatory=$true)][String]$tgtSQLserverName,

        [Parameter(Mandatory=$true)][String]$tgtSQLUserName,

        [Parameter(Mandatory=$true)][String]$tgtSQLUserPassword,

        [Parameter(Mandatory=$true)][ValidatePattern('^[a-zA-Z0-9-_()]{3,90}$')][string]$DatabaseName

    )

# Check if SqlServer is available

$StartTime = Get-Date

if ( ! ((Test-Path -Path "$7zipPath\7za.exe") -and (Test-Path -Path "$7zipPath\7za.dll") -and (Test-Path -Path "$7zipPath\7zxa.dll"))){

    Write-Output "7za.exe or 7za.dll or 7zxa.dll not found in [$7zipPath]. Exiting"

    return

}

$7zip = "$7zipPath\7za.exe"

Remove-Variable ev -ErrorAction SilentlyContinue

Import-Module sqlServer -MinimumVersion 21.1.18221 -ErrorVariable ev -ErrorAction SilentlyContinue | Out-Null

if ($ev) {

    Write-Output "Error: [$ev]`n`nExiting ..."

    return

}

# Check if SqlPackage.exe exists (64-bit)

$sqlPackageAll = (Get-ChildItem -Path $env:ProgramFiles -Filter 'sqlpackage.exe' -Recurse -file -erroraction SilentlyContinue | sort -Property LastWriteTime -Descending)

if (!$sqlPackageAll){

    Write-Output "Could not find sqlpackage.exe under $($env:ProgramFiles). Please install the latest version of SqlPackage.exe and re-run the script"

    return

}

$sqlPackage = $sqlPackageAll[0]

# Check if source DB exists

# .database.windows.net

$srcConn = New-Object System.Data.SqlClient.SQLConnection

$ConnectionString = "Data Source=$srcSqlServerName.database.windows.net;Initial Catalog=$DatabaseName;Connect Timeout=30; User ID = $srcSQLUserName; Password = $srcSQLUserPassword"

$srcConn.ConnectionString = $ConnectionString

Write-Output "`t`t>>Trying to connect to Database [$DatabaseName] on [$srcSqlServerName]."

try { $srcConn.Open() }

catch {

    Write-Output "`n`tFailed to open Database [$DatabaseName] on [$srcSqlServerName].`nError: $($_.Exception.Message) Exiting it ..."

    return

}

# read CMK from the DB

$Query = "select [name], key_path from sys.column_master_keys where [key_store_provider_name] = 'AZURE_KEY_VAULT'"

$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($Query, $srcConn)  

$Reader = $Command.ExecuteReader()

$Datatable = New-Object System.Data.DataTable

$Datatable.Load($Reader)

$srcConn.Close()

$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection

$connection.ConnectionString = $ConnectionString

try {

    $connection.Connect()

}

Catch {

    Write-Output "`n`tFailed to create Database object for Key Rotation. Exiting it ..."

    return

}

$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)

$database = $server.Databases[$DatabaseName]         # will be used for setting up MEK/CEK below

$connection.Disconnect()

if ($datatable.item.Count -eq 0) {

    Write-Output "`n`tDatabase [$DatabaseName] has no Master Encryption Key. Exiting"

    return

}

if ($datatable.item.Count -gt 1) {

    Write-Output "`n`tDatabase [$DatabaseName] has [$($datatable.item.Count)] Master Encryption Keys. Should have only one. Exiting"

    return

}

$cmkName = $Datatable.name

$KeyPath = $Datatable.key_path

Write-Output "`t`t>>Trying to authenticate for Sql AlwaysEncrypted."

try {Add-SqlAzureAuthenticationContext -ClientID $srcApplicationID -Secret $srcApplicationSecret -Tenant $srcTenantID}

catch {

    Write-Output "`n`tCannot authenticate to Azure SQL with Appid = [$srcApplicationID] and TenantId = [$srcTenantID] . Exiting ..."

    return

}

$kvName = $KeyPath.tolower().replace('https://','').split('.')[0]

$keyName = $KeyPath.tolower().replace('https://','').split('/')[2]

# $kvName

# Check if target Azure SQL server exist

Write-Output "`t`t>>Checking if [$tgtSqlServerName] exists"

Remove-Variable ev -ErrorAction SilentlyContinue

$tgtSrv = get-AzSQLServer -Name $tgtSqlServerName -DefaultProfile $tgtAzCtx -ErrorAction SilentlyContinue -ErrorVariable ev

if ( $ev){

    Write-Output "`n`Azure SQL Server [$tgtSqlServerName] was not found. Exiting ..."

    return

}

Write-Output "`t`t>>Checking if [$DatabaseName] does not exist on [$tgtSqlServerName]"

$tgtConn = New-Object System.Data.SqlClient.SQLConnection

$tgtConn.ConnectionString = "Data Source=$tgtSqlServerName.database.windows.net;Initial Catalog=$DatabaseName;Connect Timeout=30; User ID = $tgtSQLUserName; Password = $tgtSQLUserPassword"

$NotgtDB = $false

try { $tgtConn.Open() }

catch {

    # the DatabaseName does not exist on the server. That's what we need

    $NotgtDB = $true

}

if (! $NotgtDB) {

    $tgtConn.Close()

    Write-Output "`n`Found [$DatabaseName] database on [$tgtSqlServerName]. The target database must not exist. Exiting ..."

    return

}

# Authentication to the source Azure AD

Write-Output "`t`t>>Authenticating to the source AAD as service principal"

$srcpwd = ConvertTo-SecureString $srcApplicationSecret -AsPlainText -Force

$srcCred = New-Object System.Management.Automation.PSCredential ($srcApplicationID, $srcpwd)

$srcAzCtx = Connect-AzAccount -TenantId $srcTenantID -ServicePrincipal -Credential $srcCred -Subscription $srcSubscriptionID -ErrorAction SilentlyContinue

if (! $srcAzCtx) {

    Write-Output "`n`tFailed to authenticate to Tenant=[$srcTenantID]/subscriptionId = [$srcSubscriptionID] as AppId=[$srcApplicationID]. Exiting"

    return

}

Write-Output "`t`t>>Checking if key vault [$kvName] exists"

Remove-Variable ev

$curKV = Get-AzKeyVault -VaultName $kvName -ErrorAction SilentlyContinue -ErrorVariable ev

if ($ev) {

    Write-Output "`n`tFailed to find AKV=[$kvName]. Exiting"

    return

}

$srcObjectId = (Get-AzADServicePrincipal -ApplicationId $srcApplicationID).Id

Write-Output "`t`t>>Configuring Access Policy on key vault [$kvName]"

Remove-Variable ev

Set-AzKeyVaultAccessPolicy -VaultName $kvName -ObjectId $srcObjectId -PermissionsToKeys Get,Import,Verify,unwrapKey,wrapKey,sign -ErrorAction SilentlyContinue -ErrorVariable ev

if ($ev) {

    Write-Output "`n`tFailed to create AKV=[$kvName] Access Policy for AppId=[$srcApplicationID]. Error: [$ev] Exiting"

    return

}

Write-Output "`t`t>>Checking if key  [$keyName] exists"

Remove-Variable ev -ErrorAction SilentlyContinue

$curKey = Get-AzKeyVaultKey -VaultName $kvName -Name $keyName -ErrorAction SilentlyContinue -ErrorVariable ev

if ($ev) {

    Write-Output "`n`tFailed to find key=[$keyName]. Exiting"

    return

}

# Authentication to the target Azure AD

Write-Output "`t`t>>Authenticating to the target AAD as service principal"

$tgtpwd = ConvertTo-SecureString $tgtApplicationSecret -AsPlainText -Force

$tgtCred = New-Object System.Management.Automation.PSCredential ($tgtApplicationID, $tgtpwd)

Remove-Variable ev -ErrorAction SilentlyContinue

$tgtAzCtx = Connect-AzAccount -TenantId $tgtTenantID -ServicePrincipal -Credential $tgtCred -Subscription $tgtSubscriptionID -ErrorAction SilentlyContinue -ErrorVariable ev

if (! $tgtAzCtx) {

    Write-Output "`n`tFailed to authenticate to Tenant=[$tgtTenantID]/subscriptionId = [$tgtSubscriptionID] as AppId=[$tgtApplicationID]. `nError: $ev.Exiting"

    return

}

Write-Output "`t`t>>Checking if the target key vault [$tgtKVname] exists"

Remove-Variable ev

$curKV = Get-AzKeyVault -VaultName $tgtKVname  -DefaultProfile $tgtAzCtx -ErrorAction SilentlyContinue -ErrorVariable ev

if ($ev) {

    Write-Output "`n`tFailed to find AKV=[$tgtkvName]. Exiting"

    return

}

$tgtObjectId = (Get-AzADServicePrincipal -ApplicationId $tgtApplicationID).Id

Write-Output "`t`t>>Configuring Access Policy on key vault [$tgtKVname]"

Remove-Variable ev

Set-AzKeyVaultAccessPolicy -VaultName $tgtKVname -ObjectId $tgtObjectId -DefaultProfile $tgtAzCtx -PermissionsToKeys Import -ErrorAction SilentlyContinue -ErrorVariable ev

if ($ev) {

    Write-Output "`n`tFailed to create AKV=[$tgtKVname] Access Policy for AppId=[$tgtApplicationID]. Error: [$ev] Exiting"

    return

}

# remove files created by the previous run (if any)

Remove-Item -Path "$($env:tmp)\$DatabaseName.bacpac" -Force -ErrorAction SilentlyContinue

Remove-Item -Path "$($env:tmp)\mypfx.pfx" -Force -ErrorAction SilentlyContinue

Remove-Item -Path "$($env:tmp)\$DatabaseName.zip" -Force -ErrorAction SilentlyContinue

Remove-Item -Path "$($env:tmp)\$DatabaseName.unzipped" -Recurse -Force -ErrorAction SilentlyContinue

# Creating a new CMK name

Write-Output "`t`t>>Creating a new CMK name (from current CMK: [$cmkName])"

try{

    $cmkIndex = $CMKname.tolower().IndexOf($CMKstring.ToLower())

    $keySuffix = $CMKname.substring($cmkIndex+3,$CMKName.length-$cmkIndex-3)

    if (! $keySuffix) { $newCMKName = "$CMKName-1"

    } else {

        $newCMKName = "$($CMKname.substring(0,$cmkIndex+3))$(([int]$keySuffix-1))"

    }

}

catch{

    Write-Output "`n`tFailed to create a new CMK name of [$cmkName] - Check if it has [$cmkstring] in the name and the numerix suffix after"

    return

}

Write-Output "`t`t>>New CMK name for [$DatabaseName] will be [$newCMKName])"

# Generate a new Pfx

Write-Output "`t`t>>Generating a new PFX file"

$Cert = New-SelfSignedCertificate -DnsName "DataMigration" -CertStoreLocation "cert:\CurrentUser\My" -KeySpec Signature

(33..126) | Get-Random -Count 32 | % -begin {$CertPwdTxt=''} -process {$CertPwdTxt+=[char]$_} 

$CertPwd = ConvertTo-SecureString -String $CertPwdTxt -Force -AsPlainText

Get-ChildItem -Path "cert:\CurrentUser\my\$($Cert.thumbprint)"  | Export-PfxCertificate -FilePath $env:tmp\mypfx.pfx -Password $CertPwd -Force | Out-Null

# load the new key version into the source Key Vault using the PFX file

Write-Output "`t`t>>Loading the PFX file into $kvName as a new version of [$keyName]"

$newSrcKey = Add-AzKeyVaultKey -VaultName $kvName -Name $keyName -KeyFilePath $env:tmp\mypfx.pfx -KeyFilePassword $CertPwd -Tag @{'Type' = 'Pfx'; 'Purpose' = "DataMigration"; 'DestinationSQL' = "$tgtSQLserverName"; 'DestinationAKV' = "$tgtKVname"} -DefaultProfile $srcAzCtx

Write-Output "`t`t>>Creating new CMK settings"

$newCMKSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyUrl $newSrcKey.ID

# Create metadata for your new column master key in the database

Write-Output "`t`t>>Creating metadata for the new CMK"

New-SqlColumnMasterKey -Name $newCmkName -InputObject $database -ColumnMasterKeySettings $newCMKSettings | Out-Null

# Initiate the rotation from the current column master key to the new column master key.

Write-Output "`t`t>>Initiating CMK rotation"

Invoke-SqlColumnMasterKeyRotation -SourceColumnMasterKeyName $CMKName -TargetColumnMasterKeyName $newCmkName -InputObject $database

# Complete the rotation of the old column master key.

Write-Output "`t`t>>Completing CMK rotation"

Complete-SqlColumnMasterKeyRotation -SourceColumnMasterKeyName $CMKName -InputObject $database

# Remove the old column master key metadata.

Write-Output "`t`t>>Removing old CMK from the database"

Remove-SqlColumnMasterKey -Name $CMKName -InputObject $database

Write-Output "`t`t>>Loading the PFX file into $tgtkvName as [$keyName]"

$newTgtKey = Add-AzKeyVaultKey -VaultName $tgtKVname -Name $keyName -KeyFilePath $env:tmp\mypfx.pfx -KeyFilePassword $CertPwd -Tag @{'Type' = 'Pfx'; 'Purpose' = "DataMigration"; 'SourceSQL' = "$srcSQLserverName"; 'SourceAKV' = "$KVname"} -DefaultProfile  $tgtAzCtx

# remove port (:443) from the KeyId because in bacbac there is no ':443' in the key name

$newSrcKeyId = $newSrcKey.ID.Replace(':443','')

$newTgtKeyId = $newTgtKey.ID.Replace(':443','')

# Export DB with AlwaysEncrypted columns

Write-Output "`t`t>>Exporting database [$DatabaseName] from [$srcSQLserverName]"

& $sqlPackage.FullName /a:export /ssn:"$srcSQLserverName.database.windows.net" /sdn:$DatabaseName /tf:"$($env:tmp)\$DatabaseName.bacpac" /p:storage=file /su:$srcSQLUserName /sp:$srcSQLUserPassword

Rename-Item -Path "$($env:tmp)\$DatabaseName.bacpac" -NewName "$DatabaseName.zip" -Force

# expand-Archive -Path "$($env:tmp)\$DatabaseName.zip" -DestinationPath "$($env:tmp)\$DatabaseName.unzipped" -Force

& $7zip x -y "$($env:tmp)\$DatabaseName.zip" -o"$($env:tmp)\$DatabaseName.unzipped"

# calculate the original hash

$srcHash = (& certutil -hashfile "$($env:tmp)\$DatabaseName.unzipped\model.xml" sha256)

if ($srcHash.count -ne 3){

    Write-Output "`n`tSomething wrong with hash calculations`n`nOutput: [$srcHash]. Exiting"

    return

}

$originalHash = $srcHash[1].toUpper()

# $originalHash

# Replace - Model.xml with the new MEK uri

Write-Output "`t`t>>Replacing MEK uri in model.xml"

$modelXml = Get-Content -Path "$($env:tmp)\$DatabaseName.unzipped\model.xml" -Raw

$KeyPathIndex = $modelXml.IndexOf($newSrcKeyId)

if ($KeyPathIndex -eq -1) {

    Write-Output "`n`tCould not find the KeyPath property with value: [$newSrcKeyId] in [$($env:tmp)\$DatabaseName.unzipped\model.xml)]. Exiting"

    return   

}

$modelXml = $modelXml.Replace($newSrcKeyId, $newTgtKeyId)

$modelXml | Out-File -FilePath "$($env:tmp)\$DatabaseName.unzipped\model.xml" -Force -Encoding utf8

# Calculate SHA256 for the update model.xml (certutil -hashfile rr\model.xml sha256)

$newHash = (& certutil -hashfile "$($env:tmp)\$DatabaseName.unzipped\model.xml" sha256)

if ($newHash.count -ne 3){

    Write-Output "`n`tSomething wrong with hash calculations`n`nOutput: [$newHash]. Exiting"

    return

}

$updatedHash = $newHash[1].toUpper()

# $updatedHash

# Update the checksum in the origin.xml

Write-Output "`t`t>>Replacing hash in origin.xml"

$originXml = Get-Content -Path "$($env:tmp)\$DatabaseName.unzipped\Origin.xml" -Raw

$hashIndex = $originXml.IndexOf($originalHash)

if ($hashIndex -eq -1) {

    Write-Output "`n`tCould not find Checksum with value: [$originalHash] in [$($env:tmp)\$DatabaseName.unzipped\origin.xml)]. Exiting"

    return   

}

$originXml = $originXml.Replace($originalHash, $updatedHash)

$originXml | Out-File -FilePath "$($env:tmp)\$DatabaseName.unzipped\Origin.xml" -Force -Encoding utf8

#compress the files

Write-Output "`t`t>>Compressing updated files in [$($env:tmp)\$DatabaseName.zip]"

Remove-Item -Path "$($env:tmp)\mypfx.pfx" -Force -ErrorAction SilentlyContinue

Remove-Item -Path "$($env:tmp)\$DatabaseName.bacpac" -Force -ErrorAction SilentlyContinue

Remove-Item -Path "$($env:tmp)\$DatabaseName.zip" -Force -ErrorAction SilentlyContinue

# Compress-Archive -Path "$($env:tmp)\$DatabaseName.unzipped\*" -Force -DestinationPath "$($env:tmp)\$DatabaseName.zip" 

& $7zip a -tzip "$($env:tmp)\$DatabaseName.zip" "$($env:tmp)\$DatabaseName.unzipped\*"

Rename-Item -Path "$($env:tmp)\$DatabaseName.zip" -NewName "$($env:tmp)\$DatabaseName.bacpac" -Force -ErrorAction SilentlyContinue

# Importing the DB

Write-Output "`t`t>>Importing database [$DatabaseName] into [$tgtSQLserverName]"

& $sqlPackage.FullName /a:import /tsn:"$tgtSQLserverName.database.windows.net" /tdn:$DatabaseName /sf:"$($env:tmp)\$DatabaseName.bacpac" /p:storage=file /tu:$tgtSQLUserName /tp:$tgtSQLUserPassword

Remove-Item -Path "$($env:tmp)\$DatabaseName.unzipped" -Recurse -Force -ErrorAction SilentlyContinue

Remove-Item -Path "$($env:tmp)\$DatabaseName.bacpac" -Force -ErrorAction SilentlyContinue

$EndTime = get-date

$nts = New-TimeSpan -Start $StartTime -End $EndTime

"`n`n`t*** Completed migrating $DatabaseName at $EndTime in {0:d\hh\mm\ss} ***`n`n" -f $nts

}