Azure automation runbooks access to Azure SQL Database

azureazure-sql

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)

enter image description here

Best Answer

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>
Related Topic