cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

Powershell Script that also decrypts the trusted ID

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

0 Kudos
0 Replies