This is especially true if your crappy ISP repeatedly disconnects you throughout the day and assigns you new IP addresses. I don't want to disparage any company in particular, but I really hope there's some bandwidth competition on the horizon.
So, I wrote a PowerShell script to fix this problem. Now, I just say do the following:
![image image](http://lh4.ggpht.com/-gs176XsQELs/T-sd0MZk_2I/AAAAAAAAG-Y/7r_O5Aoezj4/image_thumb%25255B2%25255D.png?imgmax=800)
Notice, the script checks the current rule and then replaces it, if needed. If it is not, it will say "Current Rule Is Correct".
In order to use this script, you will need to download and "import" the WAPPSCmdlets from CodePlex using "Import-Module".
You will also need to fill in the 4 variables at the top of the script. Note that the Certificate you use must be a Management certificate that is installed into the Azure Portal.
#TODO: set these up as parameters (with defaults) $ruleName = 'Rule Name' #Whatever you want to see in the Azure UI for this Rule $server = '' # the server name (without the ".database.windows.net" on the end) $subId = '' # The Subscription ID $cert = (Get-Item cert:\\CurrentUser\My\[YOUR KEY GOES HERE]) #the key is the "Thumbprint" - use "dir cert:\CurrentUser\My" to find yours function RemoveRule() { Write-Verbose 'Removing Firewall Rule' Remove-SqlAzureFirewallRule -ServerName $server -RuleName $ruleName -SubscriptionId $subId -Certificate $cert | Format-Table } function AddRule($myIP) { Write-Output 'Adding rule for IP: ' + $myIP New-SqlAzureFirewallRule -StartIpAddress $myIP -EndIpAddress $myIp -ServerName $server -RuleName $ruleName -SubscriptionId $subId -Certificate $cert | Format-Table } ## Function to retrieve external IP address. ## the external address is retrieved from the ## title header of the webpage "www.myip.dk" function Get-ExternalIP { $source = "http://automation.whatismyip.com/n09230945.asp" $client = new-object System.Net.WebClient #add the header that whatismyip.com suggests from (http://www.whatismyip.com/faq/automation.asp) $client.Headers.Add('user-agent', 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:12.0) Gecko/20100101 Firefox/12.0') $client.downloadString($source) } function Update-AzureDBFirewallRules { # get current IP. $currentIp = &Get-ExternalIP $rules = (Get-SqlAzureFirewallRules $server -SubscriptionId $subId -Certificate $cert) $curRule = $rules | ? {$_.RuleName -eq $ruleName} $shouldAdd = $false if ($curRule -ne $null) { $ruleIp = $curRule.StartIpAddress if ($ruleIp -ne $currentIp ) { Write-Warning "Current IP [$ruleIp] is incorrect, removing Rule" &RemoveRule $shouldAdd = $true } else { Write-Output 'Current Rule is Correct' } } if ($shouldAdd -eq $true) { &AddRule ($currentIp) } }Hope this helps some of you out there...
No comments:
Post a Comment