Azure Automation Runbook to interact with On-Prem SQL via Hybrid Runbook Worker
By Jason Rutherford
Published July 29, 2016
Estimated Reading Time: 4 minutes

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

Azure-Modules

 

 

 

 

 

 

 

 

 

 

Verify the Invoke-SQLQuery name displays in the activities area:

Azure-Modules -2

 

 

 

 

 

 

 

 

 

 

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.

Azure-Connections

 

 

 

 

 

 

 

 

 

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

azure-variables

 

 

 

 

 

 

 

 

 

 

Step 9: Get the SQL query syntax working in SQL management studio

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

 

SQL-Query

 

 

 

 

 

 

 

 

 

Step 10: Create the Runbook in Azure Automation

Azure-CreateRB1

 

 

 

 

 

 

 

 

 

 

 

 

Azure-CreateRB2

 

 

 

 

 

 

 

 

 

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

Test-Runbook

 

 

 

 

 

 

 

 

Change the run settings and click start.

Test-Runbook-2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As you can see the results are the same as delivered from SQL management studio.

Test-Runbook-3

Article By Jason Rutherford
Managing Partner – Model Technology Solutions With over 21 years of Enterprise IT, Jason’s focus on people, process, and delivery has shaped Model into the organization that it has become today. His approach to creating a consulting organization focused on creating IT efficiencies has led to strategic partnerships with Model’s clients. He believes in strong community support and that knowledge sharing is a critical factor to success.

Related Posts