Azure automation runbooks access to Azure SQL Database

Question

I am trying to run some SQL statements against my Azure SQL database using an Azure Automation runbook. In all examples I can find on https://docs.microsoft.com they are using an Automation credential and an connectionstring like in the code below:

 $SqlCredential = Get-AutomationPSCredential -Name $SqlCredentialAsset 

# Get the username and password from the SQL Credential 
$SqlUsername = $SqlCredential.UserName 
$SqlPass = $SqlCredential.GetNetworkCredential().Password 

# Define the connection to the SQL Database 
$Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$SqlServer,$SqlServerPort;Database=$Database;User ID=$SqlUsername;Password=$SqlPass;Trusted_Connection=False;Encrypt=True;")

Simple enough, but the request will get stopped in my Azure SQL Firewall since I do not know what IP-address the request will come from!

How can I allow my powershell runbook to authenticate and run SQL commands against a Azure SQL database without enabling "Allow access to Azure Services" , the checkbox you see in the screenshot below below (That will allow all resources on azure, not only within my subscription)

score 2 · Accepted Answer · answered Mar 29 '17 at 12:39

The example provided on the MS site assumes you have the "Allow access to Azure services" option enabled. If you don't want to do this then you will either need to configure your script to determine the IP address when it is run and add this to the SQL firewall rule as part of your script, or you would need to look at using a hybrid automation worker that you can place in your own vnet and assign a static IP.

If you want to add your IP in the script you could do something like:

 $response = Invoke-WebRequest ifconfig.co/ip
    $ip = $response.Content.Trim()
    New-AzureSqlDatabaseServerFirewallRule -StartIPAddress $ip -EndIPAddress $ip -RuleName <Name of Rule> -ServerName <your database server name here>

score 0 · Answer 2 · answered Mar 18 '18 at 15:27

Having the same issue and taking input from Sam I have elaborated on his suggestion with the following implementation:

param(
[parameter(Mandatory=$true)]
[string] $resourceGroupName,

[parameter(Mandatory=$true)]
[string] $azureRunAsConnectionName,

[parameter(Mandatory=$true)]
[string] $serverName,

[parameter(Mandatory=$true)]
[string] $firewallRuleName
)

filter timestamp {"[$(Get-Date -Format G)]: $_"} 

$runAsConnectionProfile = Get-AutomationConnection -Name $azureRunAsConnectionName
Add-AzureRmAccount -ServicePrincipal -TenantId $runAsConnectionProfile.TenantId `
    -ApplicationId $runAsConnectionProfile.ApplicationId `
    -CertificateThumbprint $runAsConnectionProfile.CertificateThumbprint | Out-Null
Write-Output "Authenticated with Automation Run As Account."  | timestamp

$ipResponse = Invoke-WebRequest ifconfig.co/ip -UseBasicParsing
$ip = $ipResponse.Content.Trim()
Write-Output "Automation IP Address: $ip" | timestamp

Write-Output "Check for Firewall Rule For Server $serverName" | timestamp
$fwResponse = Get-AzureRmSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
    -ServerName $serverName -FirewallRuleName $firewallRuleName `
    -ErrorAction SilentlyContinue

if ($fwResponse -ne $null -and $fwResponse.StartIpAddress -ne $ip)
{
    $removeResponse = Remove-AzureRmSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
        -ServerName $serverName -FirewallRuleName $firewallRuleName
    if ($removeResponse -ne $null)
    {
        Write-Output "Removed FW Rule For IP Address: $($removeResponse.StartIpAddress)" | timestamp
    }
    else
    {
        throw "Unable to Remove FW Rule For IP Address: $($removeResponse.StartIpAddress)" | timestamp
    }
}
elseif ($fwResponse -ne $null)
{
    Write-Output "FW Rule Already in Place for IP Address: $($fwResponse.StartIpAddress)" | timestamp
    return
}


Write-Output "Adding Firewall Rule For IP Address $ip" | timestamp
$newResponse = New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
    -ServerName $serverName -FirewallRuleName $firewallRuleName `
    -StartIpAddress $ip -EndIpAddress $ip
if ($newResponse -ne $null)
{
    Write-Output "Added FW Rule For IP Address: $($newResponse.StartIpAddress)" | timestamp
}
else
{
    throw "Unable to Add FW Rule For IP Address: $($removeResponse.StartIpAddress)" | timestamp
}

Azure automation runbooks access to Azure SQL Database

2 Answers2