Azure Automation Runbook ADAL SQL support

Question

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.

Answer 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.

Answer 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'