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:
- Generate a PFX file
- 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)
- Change the MEK to use the new key (this is technically called 'rotate the key')
- 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:
- generate a PFX file and load it into the original key vault
- make this key a new MEK for the database
- load the same PFX file into the key vault in the new Azure tenant
- export the database as a bacpac file
- unzip the bacpac file
- replace in the model.xml file key URI with the new URI fromstep #3
- calculate new SHA-256 hash of the model.xml file and replace the hash in origin.xml
- zip the files up into a new bacpac file
- 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
}
Comments
Post a Comment