Can't get a runbook test (in portal) to ask for input parameters

Question

I'm brand new to automation, and am trying to get a runbook to connect to a sql database and run a stored procedure. The problem is, the code I'm using (adapted from https://azure.microsoft.com/en-us/blog/azure-automation-your-sql-agent-in-the-cloud/) is not asking for the server and credentials parameters when I try to test it. The test window says, "No input parameters."

Here is my (genericized) code:

workflow DB_DailyTasks 
{
    param
    (
        # Fully-qualified name of the Azure DB server 
        [parameter(Mandatory=$true)] 
        [string] $SqlServerName="mydb.database.windows.net",

        # Credentials for $SqlServerName stored as an Azure Automation credential asset
        # When using in the Azure Automation UI, please enter the name of the credential asset for the "Credential" parameter
        [parameter(Mandatory=$true)] 
        [PSCredential] $Credential
    )

    inlinescript
    {

        # Setup credentials   
        $ServerName = $Using:SqlServerName
        $UserId = $Using:Credential.UserName
        $Password = ($Using:Credential).GetNetworkCredential().Password

        # Execute the udp_Test procedure

        # Create connection for each individual database
        $DatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
        $DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand

        $DbName = "myDB"

        # Setup connection string for $DbName
        $DatabaseConnection.ConnectionString = "Server=$ServerName; Database=$DbName; User ID=$UserId; Password=$Password;"
        $DatabaseConnection.Open();

        # Create command for a specific database $DBName
        $DatabaseCommand.Connection = $DatabaseConnection

        Write-Output "Running udp_Test procedure"

        $DatabaseCommand.CommandText = "EXECUTE [dbo].[udp_Test]"
        $NonQueryResult = $DatabaseCommand.ExecuteNonQuery()

        # Close connection to $DbName
        $DatabaseConnection.Close()        
    }    
}

I have some stored credentials in the automation account, but I can't get the test to actually ASK for them! When I test, it says, "No input parameters." Is there something I'm doing wrong?

whats the point of workflow if all you do is run an inline script? also, i dont think you can have default value for mandatory parameter. I'm also fairly certain you are not retrieving credentials in a proper fashion: docs.microsoft.com/en-us/azure/automation/shared-resources/… — 4c74356b41
Once I get it working, I plan to put it on a schedule. Runbooks are apparently Azure's answer to SQL Agent. — Katerine459

Katerine459 Katerine459 · Accepted Answer · 2019-09-21T03:09:55

I'm not 100% sure of this, because I actually opted to remove the parameters and just go with a hard-coded server name and a hard-coded reference to the credentials (it's not like it will ever change). But when I did that, I ran into different issues, which I posted about here: Runbook test pane is not showing Write-Output ... and the answer was that the code was declaring a workflow runbook, while the runbook in Azure was a regular Powershell runbook. (I didn't realize there was a difference until now). But this resulted in the code not actually running at all. Once I removed the workflow definition and inline-script block (leaving just the code), and removed $using and fixed a few other things, it worked.

My guess is, because the script wasn't really running at all, that's why it wasn't asking for parameters earlier, and if I'd opted to keep the parameters, then removing the workflow definition and inline-script block would have fixed the issue.

Can't get a runbook test (in portal) to ask for input parameters

2 Answers