public/New-SqlFirewallRule.ps1
<#
.Synopsis Adds a firewall rule to an Azure SQL Server for a single IP. .Description Adds a firewall rule to an Azure SQL Server for a single IP. .Parameter SubscriptionId The unique ID of the Azure subscription where the SQL Azure Server is located. .Parameter AzureSqlServerName The name of the SQL server .Parameter TenantId The unique ID of the tenant where the subscription lives in for faster context switch. .Parameter IpAddress The IP address for which to set the rule. If omitted the current machine public IP will be determined and used. .Example New-AzdSqlFirewallRule -SubscriptionId [ID] -SqlServerName mySQLServerName Add firewall rule for current IP #> Function New-SqlFirewallRule { [CmdLetBinding()] param ( [Parameter(Mandatory = $true)] [string] $SubscriptionId, [Parameter(Mandatory = $true)] [string] $AzureSqlServerName, [string] $TenantId, [string] $IpAddress, [switch] $NoLogo ) begin { if (!$NoLogo.IsPresent) { Write-Logo $MyInvocation.InvocationName } New-FunctionStartup # ensure that we are at the correct subscription Set-SubscriptionContext -TenantId $TenantId -SubscriptionId $SubscriptionId -NoLogo if (!$?) { Write-HostError "Could not set context." return } } process { if (!$IpAddress) { Write-HostDebug "Retrieving public IP address..." $IpAddress = (Invoke-WebRequest -uri "http://api.ipify.org?format=text").Content } Write-HostDebug "Using IP address $IpAddress" $server = Get-AzSqlServer | Where-Object -Property ServerName -EQ $AzureSqlServerName if (!$server) { throw "Could not find SQL Azure Server $AzureSqlServerName in subscription $SubscriptionId" } $existingRule = Get-AzSqlServerFirewallRule -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName | Where-Object -Property StartIpAddress -EQ $IpAddress if ($existingRule) { $ruleName = $existingRule.FirewallRuleName Write-HostDebug "Skipping because firewall rule for your IP $IpAddress already exists on server $AzureSqlServerName : $ruleName" return } $ruleName = "ClientIpAddress_" + (Get-Date).ToString("yyyy_MM_dd_HH_mm_ss") New-AzSqlServerFirewallRule -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName -FirewallRuleName $ruleName -StartIpAddress $IpAddress -EndIpAddress $IpAddress if (!$?) { Write-HostError "Failed to add SQL Server firewall rule: $_" } Write-HostSuccess "Firewall rule with name $ruleName successfully created on Azure SQL Server $AzureSqlServerName for IP $IpAddress" } } |