Problem using Get-AutomationPSCredential for Invoke-Sqlcmd password

Question

Hello

I have an Azure Runbook which needs to write data into an Azure SQL database. My password is stored in the automation account's
credential object and I use Get-AutomationPSCredential to pull out the .Password property.

For some reason the returned value, even though I am explicitly casting as a [string] gives a lgin error from the SQL server. When I hardcode
the same value it works.

[string] $Password =  ((Get-AutomationPSCredential -Name 'myCred').Password) # fails 

[string] $Password =  "123456" # works

Calling the SQL like this.

Invoke-Sqlcmd `
            -ServerInstance $ServerInstance `
            -Username  $User `
            -Password  $Password `
            -Database $Database `
            -Query $Query

Tried Trim() and ToString() - does not make any difference.

Invoke-Sqlcmd : Login failed for user '###########'.
At line:60 char:13

• Invoke-Sqlcmd `
• ~~~~~~~~~~~~~~~
• CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
• FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Answer 1

Hi @David Gray , welcome to Microsoft Q&A forum.

When we are reading the password from credentials, we cannot use it directly for safety reasons. However, there are multiple ways to read and use the password as mentioned below:

Code Scenario 1: Extracting the password from credential object and use it

$myCredential = Get-AutomationPSCredential -Name 'myCred'  
$userName = $myCredential.UserName  
$password = $myCredential.GetNetworkCredential().Password  
$ServerInstance = 'yourserver'  
$Database = 'yourdatabase'  
$Query='select top 10 * from [SalesLT].[Product]'  
Write-Output $password  
Write-Output $userName  
Write-Output $securePassword  
invoke-sqlcmd -ServerInstance $ServerInstance -Database $Database -Username $userName -Password $password -Query $Query  

Code Scenario 2: Using the credentials directly in sqlcmd

$myCredential = Get-AutomationPSCredential -Name 'myCred'  
$ServerInstance = 'yourserver'  
$Database = 'yourdatabase'  
$Query='select top 10 * from [SalesLT].[Product]'  
invoke-sqlcmd -ServerInstance $ServerInstance -Database $Database -Credential $myCredential -Query $Query  

Please let me know if this helps or else we can discuss further.

----------

If answer helps, please mark it 'Accept Answer'