A powershell script objective is to copy a database from one server to another, then execute a simple query on destination server. the current user does not have permissions on the database servers, therefore a login was created in both servers, an the password stored in file as a secure string:
$sourceServer = "ServerA"
$destServer = "ServerB"
$restoreUser = "replicationuser"
$encPass = Get-Content -Path ".\SecuredPass.spf"
$secString= $encPass | ConvertTo-SecureString -Key (1..16)
$restoreCredential=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $restoreUser, $secString
Copy-DbaDatabase -Source $sourceServer -Destination $destServer -Database "MyDatabase" -SourceSqlCredential $restoreCredential -DestinationSqlCredential $restoreCredential -BackupRestore -NetworkShare "\\MyShare\Databases" -WithReplace -EnableException
$sqlQuery = 'select 1+1'
Invoke-DbaSqlQuery -Query $sqlQuery -SqlInstance $destServer -SqlCredential $restoreCredential
when the script runs, the database is copied, but the query execution gives the following message:
WARNING: [15:53:29][Invoke-DbaQuery] Failure | Property LoginSecure cannot be changed or read after a connection string has been set.
What should be done to use the credential when running the query?
We had some issues with the way credentials were handled in Invoke-DbaQuery. You might try the latest release and see if this issue still exists.