Powershell: Add Firewall Rule for SQL in Azure


In The previous article we discussed about How to add a co-administrator in Microsoft Azure Management Portal

<#
.SYNOPSIS
    Add Firewall Rule in SQL Server in Azure Environment to access it remotely

.DESCRIPTION

    This script adds a Firewal Rule in the existing SQL Server so SQL Server is accessed from SQL Management Studio

.EXAMPLE

    .SQLFirewallRule.ps1 -AzureSubscriptionName “DT – Dev” -ResourceGroupName RG-Dev-Storage-WestEurope -ServerName mypstestsqlserver -FirewallRuleName DTPSTestFWRule1 -StartIpAddress x.x.x.x -EndIpAddress x.x.x.x
#>

Powershell Script to add Firewall Rule in SQL Server in Microsoft Azure



[CmdletBinding(SupportsShouldProcess = $true)]

param(
    # The name of valide Azure Subscription associated with Account
    [Parameter(Mandatory = $true)]
    [string]$AzureSubscriptionName,

    # The name of the Resource Group

    [Parameter(Mandatory = $true)]
    [string]$ResourceGroupName,

    # The name of the SQL Server name on which database will be imported.

    [Parameter(Mandatory = $true)]
    [string]$ServerName,

    # The name of the SQL Server name on which database will be imported.

    [Parameter(Mandatory = $true)]
    [string]$FirewallRuleName,

    # Users machine IP.  Used to configure firewall rule for new SQL DB.

    [Parameter(Mandatory = $true)]
    [ValidatePattern(“b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?).(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?).(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?).(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)b”)]
    [String]$StartIpAddress,

    # Users machine IP.  Used to configure firewall rule for new SQL DB.

    [Parameter(Mandatory = $true)]
    [ValidatePattern(“b(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?).(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?).(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?).(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)b”)]
    [String]$EndIpAddress

)


# The script has been tested on Powershell 3.0

Set-StrictMode -Version 3

[System.Net.WebRequest]::DefaultWebProxy.Credentials = [System.Net.CredentialCache]::DefaultCredentials


# To authenticate through Windows Azure Active Directory and downloads associated subscriptions.

#Add-AzureAccount

# To select specific subscription from available subscription “Visual Studio Enterprise with MSDN”

Select-AzureSubscription -SubscriptionName $AzureSubscriptionName

# Check if Windows Azure Powershell is avaiable

if ((Get-Module -ListAvailable Azure) -eq $null)
{
    throw “Windows Azure Powershell not found! Please install from http://www.windowsazure.com/en-us/downloads/#cmd-line-tools”
}
else
{
    Write-Host “Windows Azure Powershell is available!!!”
}

New-AzureRmSqlServerFirewallRule -ResourceGroupName $ResourceGroupName -ServerName $ServerName -FirewallRuleName $FirewallRuleName -StartIpAddress $StartIpAddress -EndIpAddress $EndIpAddress


New-AzureSqlDatabaseServerFirewallRule -ServerName $ServerName -RuleName “AzureServices” -StartIpAddress “0.0.0.0” -EndIpAddress “0.0.0.0”


# Apply Firewall Rules

# $clientFirewallRuleName = “ClientIPAddress_DT”

# Write-Host “Creating client firewall rule ‘$clientFirewallRuleName’.”


#New-AzureSqlDatabaseServerFirewallRule -ServerName $ServerName -RuleName $clientFirewallRuleName -StartIpAddress $ClientIPAddress -EndIpAddress $ClientIPAddress | Out-Null


$azureFirewallRuleName = “AzureServices_DT”

# Write-Host “Creating Azure Services firewall rule ‘$azureFirewallRuleName’.”
# New-AzureSqlDatabaseServerFirewallRule -ServerName mypstestsqlserver -RuleName “AzureServices” -StartIpAddress “0.0.0.0” -EndIpAddress “0.0.0.0”

Leave a comment

Your email address will not be published. Required fields are marked *