'Azure Automation Runbook ADAL SQL support

I am attempting to use an Azure Automation Runbook to run a query against an Azure SQL database using Azure AD credentials:

$cred = Get-AutomationPSCredential -Name 'SqlAdminUser'
$Username = $cred.UserName
$Password = $cred.GetNetworkCredential().Password
$Server = 'server.database.windows.net'
$Port = 1433
$cxnString = "Server=tcp:$Server,$Port;Database=$Database;Authentication=Active Directory Password;UID=$UserName;PWD=$Password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"

$cxn = New-Object System.Data.SqlClient.SqlConnection($cxnString)
$cxn.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand($query, $cxn)
$cmd.CommandTimeout = 120
$cmd.ExecuteNonQuery()
$cxn.Close()

and I get the following error:

Keyword not supported: 'authentication'.

So it seems like Azure Automation doesn't have the ADAL SQL library installed? Is there any easy way to work around this (and still use an Azure AD account to connect)?

I used this example as a starting point.

sql-serverpowershellazureautomationrunbook


Solution 1:[1]

In the end I created hybrid workers which allowed me to install the latest version of the ADAL SQL library, which supports Azure AD authentication.

Solution 2:[2]

You are fetching username and password from $SqlCredential but you stored credentials in $cred variable. These variables need to be same. Also make sure an automation credential object is created in azure automation account with same name as 'SqlAdminUser'

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 gregjhogan
Solution 2

Related Questions