CODEWITHSUNDEEP
azurepowershellazure-sql-databaseazure-powershell
3355307
3355307

Reputation: 1678

Azure SQL Server Firewall Rule add multiple IP address using PowerShell for database connectivity

How do i add multiple IP addresses to Azure SQL Server using PowerShell so that multiple adminstrator can connect to database from their machines using SSMS. It can be done via portal but we have closed that route and everything is done via PowerShell

I have this code but i think it does not fit our requirement.

 $ServerFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $ResourceGroupName `
    -ServerName $ServerName `
    -FirewallRuleName "AllowedIPs" -StartIpAddress $StartIp -EndIpAddress $EndIp

for example

admin 1 ipaddress-1: 158.****

admin 2 ipaddress-2: 196.****

Upvotes: 1

Views: 1382

Answers (2)

GagandeepT
GagandeepT

Reputation: 270

Set subscription for cmdlets to use in the current session:

Set-AzContext -Subscription <Subscription_ID>

Set variables for Resource Group and SQL server:

$resourceGroup = "Name_of_ResourceGroup"
$sqlserver = "Name_of_AzureSQLserver"

You can add multiple ip addresses in '$ips' array:

$ips = @(
"ipaddress-1",
"ipaddress-2",
"ipaddress-3",
"ipaddress-4",
"ipaddress-5",
"ipaddress-*",
)

PowerShell foreach statement:

foreach ($ip in $ips) {
$ruleName = "admin-$($ips.IndexOf($ip) + 1)"
New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroup -ServerName $sqlserver -FirewallRuleName $ruleName -StartIpAddress $ip -EndIpAddress $ip
}

Upvotes: 0

Alberto Morillo
Alberto Morillo

Reputation: 15608

Please try the following PowerShell that add the current client IP Address to the Azure SQL Firewall white list. Your administrators can run the PowerShell manually or they can schedule a Windows schedule task that can run the PowerShell when a computer starts.

$subscriptionName = 'Your Subscription'
$ipGetCommand = 'http://www.iplocation.net/find-ip-address' 
$firewallRule = 'My-Home'
$serverName = "Your Server Name";
$webclient = New-Object System.Net.WebClient
$queryResult = $webclient.DownloadString($ipGetCommand)
$queryResult -match '\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\b'
$currentPublicIp = $($matches[0])

Select-AzureSubscription -SubscriptionName $subscriptionName



If ((Get-AzureSqlDatabaseServerFirewallRule -ServerName $serverName -RuleName $firewallRule) -eq $null) {
    New-AzureSqlDatabaseServerFirewallRule -ServerName $serverName -RuleName $firewallRule -StartIpAddress $currentPublicIp -EndIpAddress $currentPublicIp
}
else {
    Set-AzureSqlDatabaseServerFirewallRule -ServerName $serverName -RuleName $firewallRule -StartIpAddress $currentPublicIp -EndIpAddress $currentPublicIp
}

Upvotes: 2

Related Questions