Hi all,

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 – (Google  Bing it to find out how to do so)

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

SELECT Servername FROM [ServerPatching].[dbo].[PW_Servers]

Step 10: Create the Runbook in Azure Automation

Here’s the RunBook: note, we’re only going to pull back Servername from the table.

#region - Set Global variables
$runbookName = 'PW-FE-START-PW'
$PWDBLogin = Get-AutomationVariable -Name 'PW-DB-Login'
$PWDBName = Get-AutomationVariable -Name 'PW-DB-Name'
$PWDBPwd = Get-AutomationVariable -Name 'PW-DB-Password'
$PWDBSvrName = Get-AutomationVariable -Name 'PW-DB-ServerName'
$ConnString = "Server=$PWDBSvrName;Database=$PWDBName;User ID=$PWDBLogin;Password=$PWDBPwd;"
#endregion

#region - Get Servers based on SQL Query
#Fnd Servers to Start PatchProcess by Owner Email
$ServerQuery = "SELECT Servername FROM [ServerPatching].[dbo].[PW_Servers]"
$ServersResults = Invoke-Sqlquery -Query "$ServerQuery" -ConnectionString $ConnString -QueryTimeout 420
If ($ServersResults.Servername -ne $NULL){
 Write-Output $ServerQuery
 write-output $ServersResults
}
#endregion

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.