Invoke-DbaDbDataMasking support for Azure SQL DB

[EmanueleMeazzo]

Before submitting a bug report:

• Ensure you are able to reproduce it on the latest released version (we release often)
• Verified this bug is not already reported in an issue
• Verified errors are not related to permissions
• Can reproduce in a clean PowerShell session (clean = powershell -NoProfile)
• If issue is with Copy-DbaDatabase, using Backup-DbaDatabase | Restore-DbaDatabase still does not work

Steps to Reproduce

After creating the Json file with New-DbaDbMaskingConfig, applying the Masking configuration to an Azure SQL DB Fails

<#
    $password = "mypassword." | ConvertTo-SecureString -asPlainText -Force
    $username = "myusername"
    $instance = "myazuresqlinstance.database.windows.net"

$cred = New-Object PSCredential($username,$password)

Invoke-DbaDbDataMasking -SqlInstance $instance -SqlCredential $cred -Database "DBToMask" -FilePath C:\Users\emeazzo\Desktop\Scripts\PowerShell\DataMasking\myazuresqlinstance.database.windows.net.DBToMask.tables.json
#>

Expected Behavior

The command should run and mask all the tables according to the configuration file

Actual Behavior

The "ChangeDatabase" function fails inside the cmdlet, because in Azure SQL DB you cannot change database via this command but the connection should be established directly to the Database that you want to work on.

Since the cmdlet connects by default to the master DB, and not the database specified in the -Database parameter, all the masking queries fail since they're against the wrong database.

Proposed Solution

The cmdlet should connect directly to the DB specified in the -Database parameter, as opposed to connecting to master and then trying to switch databases with ChangeDatabase
This could pose an additional issue on premise, in case a user is trying to mask a database without having access to master

Environmental data

• PowerShell:

Name Value

---

PSVersion 5.1.17763.316
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.316
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

• • SQL Server: Microsoft SQL Azure (RTM) - 12.0.2000.8 Dec 19 2018 08:43:17 Copyright (C) 2018 Microsoft Corporation