Copy-EncryptedDB.ps1
|
<#PSScriptInfo .VERSION 1.4 .GUID 7dcce4d8-6df3-413a-9699-631b0dc915e4 .AUTHOR Dave Farinelli .LICENSEURI https://www.gnu.org/licenses/gpl-3.0.en.html .PROJECTURI https://github.com/dfar-io/copy-encrypted-db-script #> <# .DESCRIPTION This script allows for copying a Azure SQL Database encrypted using Always Encrypted. #> [Diagnostics.CodeAnalysis.SuppressMessageAttribute( "PSAvoidUsingPlainTextForPassword", "", Justification = "Using for existing calls.")] Param( [Parameter(Mandatory = $True)] [string]$destDB, [Parameter(Mandatory = $True)] [string]$destServer, [Parameter(Mandatory = $True)] [string]$destRG, [Parameter(Mandatory = $True)] [string]$destKeyVaultName, [Parameter(Mandatory = $True)] [string]$destServerUsername, [Parameter(Mandatory = $True)] [string]$destServerPassword, [Parameter(Mandatory = $True)] [string]$destDbAppUsername, [Parameter(Mandatory = $True)] [string]$destDbAppPassword, [Parameter(Mandatory = $True)] [string]$sourceDB, [Parameter(Mandatory = $True)] [string]$sourceServer, [Parameter(Mandatory = $True)] [string]$sourceRG ) function ExecuteSqlWriteConstruct { Param( [Parameter(Mandatory = $true)][string]$connectionString, [Parameter(Mandatory = $true)][string]$command ) $gscn = New-Object System.Data.SqlClient.SqlConnection $gscn.ConnectionString = $connectionString $wrCmd = New-Object System.Data.SqlClient.SqlCommand $wrCmd.Connection = $gscn $wrCmd.CommandTimeout = 0 $wrCmd.CommandText = $command try { $gscn.Open() $wrCmd.ExecuteNonQuery() | Out-Null } catch [Exception] { Write-Warning $_.Exception.Message Write-Warning $command } finally { $gscn.Dispose() $wrCmd.Dispose() } } ################################################################################ #Requires -Modules SqlServer $ErrorActionPreference = "Stop" az sql db delete -n $destDB -g $destRG -s $destServer Write-Output ` "Creating DB '$destDB' on server $destServer (copying from '$sourceDB' on $sourceServer)" az sql db copy --dest-name $destDB --dest-server $destServer ` --dest-resource-group $destRG --name $sourceDB ` --server $sourceServer --resource-group $sourceRG Write-Output "Creating Key Vault Key..." $akvKey = az keyvault key create --vault-name $destKeyVaultName ` --name "AlwaysEncrypted" | ConvertFrom-Json $cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings ` -KeyURL $akvKey.key.kid Write-Output "Key Vault Key Created." Write-Output "Connecting to database server $destServer..." $connStr = "Server=" + $destServer + ".database.windows.net" + ";Database=" + $destDB + ";User ID=" + $destServerUsername + ";Password=" + $destServerPassword $connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection $connection.ConnectionString = $connStr try { $connection.Connect() } catch { $errorMessage = $_.Exception.Message Write-Error $errorMessage exit 1; } $server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection) $database = $server.Databases[$destDB] Write-Output "Connected to database server $destServer and database $database..." $cmkName = "CMKAuto" $existingCmkName = "" Get-SqlColumnMasterKey -InputObject $database -Name $cmkName ` -ErrorAction 'SilentlyContinue' | Out-Null if ($?) { Write-Output "Found existing automatic CMK, using alternative automatic CMK." $existingCmkName = $cmkName $cmkName = "CMKAuto2" } else { Write-Output "Getting existing CMK..." $existingCmk = Get-SqlColumnMasterKey -InputObject $database $existingCmkName = $existingCmk.Name Write-Output "Found existing CMK $existingCmkName." } Write-Output "Creating a column master key for DB $dbName..." try { New-SqlColumnMasterKey -Name $cmkName -InputObject $database ` -ColumnMasterKeySettings $cmkSettings | Out-Null } catch { $errorMessage = $_.Exception.Message Write-Error $errorMessage exit 1; } Write-Output "Column master key created." Write-Output "Rotating Keys (will require sign-in)..." Add-SqlAzureAuthenticationContext -Interactive Invoke-SqlColumnMasterKeyRotation ` -SourceColumnMasterKeyName $existingCmkName ` -TargetColumnMasterKeyName $cmkName ` -InputObject $database Complete-SqlColumnMasterKeyRotation ` -SourceColumnMasterKeyName $existingCmkName ` -InputObject $database Write-Output "Keys rotated." Write-Output "Deleting existing CMK..." Remove-SqlColumnMasterKey -Name $existingCmkName -InputObject $database Write-Output "Existing CMK deleted." Write-Output "Setting app user/pass..." ExecuteSqlWriteConstruct -connectionString $connStr ` -command "DROP USER IF EXISTS $destDbAppUsername" ExecuteSqlWriteConstruct -connectionString $connStr ` -command "CREATE USER $destDbAppUsername WITH PASSWORD = '$destDbAppPassword'" ExecuteSqlWriteConstruct -connectionString $connStr ` -command "EXEC sp_addrolemember N'db_owner', N'$destDbAppUsername'" Write-Output "App user/pass set." |