Error calling one Azure Automation Runbook from another

Question

Background

I have one Azure Runbook (named RunStoredProcedure2) defined as such:

param(
[parameter(Mandatory=$True)]
[string] $SqlServer,

[parameter(Mandatory=$False)]
[int] $SqlServerPort = 1433,

[parameter(Mandatory=$True)]
[string] $Database,

[parameter(Mandatory=$True)]
[string] $StoredProcedureName,

[parameter(Mandatory=$True)]
[PSCredential] $SqlCredential
)

# Get the username and password from the SQL Credential
$SqlUsername = $SqlCredential.UserName
$SqlPass = $SqlCredential.GetNetworkCredential().Password

$Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$SqlServer,$SqlServerPort;Database=$Database;User ID=$SqlUsername;Password=$SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")

$Conn.Open()

$Cmd=new-object system.Data.SqlClient.SqlCommand("EXEC 
$StoredProcedureName", $Conn)
$Cmd.CommandTimeout=120


$Cmd.ExecuteNonQuery()
# Close the SQL connection
$Conn.Close()

I'm now attempting to call this runbook from another Azure Runbook using this command:

& .\RunStoredProcedure2.ps1 -Database 'adventureworksnh.database.windows.net' -SqlServer 'AdventureWorksDW' -SqlServerPort 1433 -StoredProcedureName 'TestJob1' -sqlCredential 'awadmin'

Issue

When I attempt to run this, I get this error:

C:\Temp\uzahthmc.su1\RunStoredProcedure2.ps1 : Cannot process argument transformation on parameter 'SqlCredential'. A command that prompts the user failed because the host program or the command type does not support user interaction. The host was attempting to request confirmation with the following message: Enter your credentials.

I am able to run RunStoredProcedure with these parameters successfully

What I've Tried

• Adding/removing the preceding ampersand
• Using Invoke-Expression

Answer 1

Do you have a Credential asset named "awadmin" in your Automation account? When you start a runbook directly (using the Start button in the portal or the Start-AzureRmAutomationRunbook), Azure Automation allows you to pass a credential asset name as a parameter value, and it will retrieve the specified credential automatically. However, when you invoke a runbook from another runbook, you have to follow plain PowerShell rules and pass a PSCredential object, as declared:

$sqlCred = Get-AutomationPSCredential -Name 'awadmin'
& .\RunStoredProcedure2.ps1 ... -sqlCredential $sqlCred