Hello,
I am not a great Powershell script creator. I can muddle my way thru, but I am at a loss on how to do the following:
We have a Powershell script that performs a SOAP call and Executes a SQL Query.
$query = "$query = "exec StoredProc_s @UEXML = '<request><header ****** /></header></request>', @OutXML = null, @ReturnResultSets = 1, @Debug = null, @in_xml = null, @xml = null"
Invoke-SqlCmd -Server "DBSERVER" -Database "DB" -Username "USERNAME" -Password "PASSWORD" -Query $query | Format-Table -Autosize
$status = "$?"
$success = "Success"
$failure = "failure"
if ($status -eq $True) {
$statistic = 0
Write-Host "Statistic: 0"
Write-Host "Message: $success"
Exit 0;
} Else {
$statistic = 1
Write-Host "Statistic: 1"
Write-Host "Message: $failure"
Exit 1;
}
It was working successfully, but I found out SQL is now using a Trusted ID. For the above script, how would I change Invoke-SqlCmd to use Trusted ID?
I was provided the code to decrypt the Trusted ID, but have no idea how to use it in a script. Any help would be awesome and really appreciated. Thanks in advance:
declare @ID varchar(50)
open symmetric key CCKey decryption by certificate MyCert
select
@ID = case when c.IsEncrypted = 1 then convert(varchar(100),DecryptByKey(c.EncryptedConfigValue)) else c.ConfigValue end
from Config c(nolock)
where c.ConfigName = 'ID'
close symmetric key CCKey
Thanks again,
Tom