Azure Automation Runbook to interact with On-Prem SQL via Hybrid Runbook Worker

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

 

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.

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

By |2016-07-29T15:25:21+00:00July 29th, 2016|Azure|0 Comments

About the Author:

Jason Rutherford
Managing Partner – Model Technology Solutions With over 18 years of Systems Management experience, Jason’s focus on people, process, and delivery has shaped Model into the consulting practice 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.

Model Technology

Let us help you get your end point and data center strategy on cruise control!  Ask about our Calibration Assessment.

CONTACT US

  • 12125 Woodcrest Executive Drive, Ste. 204 Creve Coeur, MO 63141
  • (314) 254-4138
  • sales@model-technology.com

RECENT TWEETS