Often, interaction with SQL is an important piece of any automated workflow. In Orchestrator and PowerShell, the process of running SQL queries and stored procedures is fairly straightforward. However, in Service Management Automation (SMA) and Azure Automation, this process can be a bit more complex.

Module used
The module we will use for interacting with SQL is called GMI SQL and is available for download here.

Creating the Portable Module
Once downloaded, the module will need to be packaged into a “portable module” which allows it to be imported into Service Management Automation and Azure Automation.
To create the portable module, install the SMA PowerShell module using the Orchestrator installer, and then open up the PowerShell console. Create a session to the host that has the PowerShell module you want to create a portable module from. Then pass this session as well as the module name to New-SmaPortableModule:

$session = New-PSSession localhost
New-SMAPortableModule – Session $session -ModuleName Azure -OutputPath C:\Outputpath -ErrorAction SilentlyContinue

This will output the newly created portable module to the path specified by the OutputPath switch
More information about Portable Modules can be found in this great post by Joe Levy from Microsoft.

Importing the Portable Module into SMA/AA
The steps for importing the newly created portable module into SMA/AA have been detailed by Rob Davies from Microsoft on his blog

Cmdlets used in scripts
The cmdlet we will be using in both SMA and Azure Automation is Invoke-SQLQuery. The parameters needed for this cmdlet are:
• Query – Specifies the SQL Query to run
• ConnectionString – Requires Server, Database, UserID and Password
• [Optional] QueryTimeout – Specifies a timeout in seconds for the cmdlet

SMA Example:

$UserID = “Contoso\User”
$Password = “P@ssw0rd”
$SQLServer = “SQL01.contoso.com”
$SQLInstance = “TESTINSTANCE”
$SQLDatabase = “TestDB”
$SQLQuery = “Select ID, Name from .[TestTable]”
$ConnString = "Server=$SQLServer\$SQLInstance;Database=$SQLDatabase;User ID=$UserID;Password=$Password;"
Invoke-SQLQuery -Query $SQLQuery -ConnectionString $ConnString -QueryTimeout 60


Azure Automation Example:

In Azure Automation, this script must be run as an inline script.

InlineScript{
$UserID = “Contoso\User”
$Password = “P@ssw0rd”
$SQLServer = “SQL01.contoso.com”
$SQLInstance = “TESTINSTANCE”
$SQLDatabase = “TestDB”
$SQLQuery = “Select ID, Name from .[TestTable]”
$ConnString = "Server=$SQLServer\$SQLInstance;Database=$SQLDatabase;User ID=$UserID;Password=$Password;"
Invoke-SQLQuery -Query $SQLQuery -ConnectionString $ConnString -QueryTimeout 60
}

Hope you found this helpful. Leave any comments or questions below.

Happy Scripting, Everyone!

About the Author: steve bowman

Three Minutes For A More Secure & Efficient Infrastructure

Short and to the point, Steve’s Email Blasts give you endpoint management tips, tricks, and news in three minutes or less email read-time, guaranteed.

Model says no to spam. Privacy Policy

Model Technology Solutions

Model Technology Solutions is a small but mighty band of infrastructure experts. We’ve helped companies in diverse industries to modernize and automate their infrastructures through effectively managing their Microsoft endpoint suite.

With us on your team, you’ll watch your security and compliance go up and your IT team’s costs (and headaches) go down. You’ll relax in knowing that your endpoints will be secure and online when your users need them most. And you’ll finally get back to your most-important tasks.

Model Technology Solutions
12125 Woodcrest Executive Drive, Ste. 204 Creve Coeur, MO 63141

Phone: (314) 254-4138
General Inquiries: model@model-technology.com
Sales and Quotes: sales@model-technology.com