I’ve been spending several of my minutes lately reading and writing to an on-prem SQL DB via a Hybrid Runbook Worker from Azure Auotmation; so I figured I’d share some learnings. In our custom developed solutions such as, our Automated Server Patching Solution for example, we’ll use custom SQL DB’s to track the workflows. This allows us to store tracking information for the workflows, but more on that later.
In this scenario we have the following setup:
- Azure Automation Account (Resource Manager)
- Local (Domain Joined) server living in our data center with the OMS agent acting as our Hybrid Runbook Worker
- Side note: the server has datareader and datawriter perms to the custom DB
- Side note #2: This server is also the SQL server, but doesn’t have to be
- To interact, we’re using the GMI-SQL module to run invoke-SQLQuery command from Runbooks
- Side note #3: the module is imported on the hybrid runbook worker and in Azure Automation Modules
Step 1: Sign up for an Azure OMS / Automation Account – (
Step 2: Build an On-Prem server, install SQL, Create a DB, Create a table, populate said table. Also, set the SQL Server for Mixed authentication, and create a new SQL user, but un-check the “enforce password policy” box (So you don’t have to change the password on first login), ensure the user has DBO datarader and datawriter permissions to the target DB.
Step 3: Download the Microsoft Monitoring Agent and configure it as a Hybrid runbook worker (Instructions)
Step 4: Download the GMI-SQL PowerShell module (Here) and place in the PowerShell modules directory on the hybrid runbook worker
Step 5: Make that file a Zip file so you can import it into Azure Automation modules (name the folder GMI-SQL, and ZIP it up)
Step 6: Import the GMI-SQL PowerShell module into azure automation
Verify the Invoke-SQLQuery name displays in the activities area:
Step 7: If you didn’t create the AzureRunAsConnection connection when you setup the automation account, read (this) to learn how to do so. This is not required for what we’re doing here, but if you’re launching Azure runbooks from a Hybrid runbook worker, you’ll want this… Trust me.
Step 8: Populate some Azure Automation variables you’ll need:
- SQL Server name variable
- SQL Account username variable
- SQL DB name variable
- SQL Account password ENCRYPTED variable
Step 9: Get the SQL query syntax working in SQL management studio
Step 10: Create the Runbook in Azure Automation
Here’s the RunBook: note, we’re only going to pull back Servername from the table.
Step 11: Test the runbook on thy Hybrid runbook worker using test pane
Change the run settings and click start.
As you can see the results are the same as delivered from SQL management studio.