For this installment of the Premier Developer Blog, Application Development Manager Herald Gjura shares some PowerShell scripts from his toolbox to help automate Azure SQL DB Server firewall rules.
Problem to solve
This is the problem I am trying to solve. I usually work with multiple Azure clients, who have granted me access to their subscriptions to do the work. Part of that work involves writing code that accesses various SQL Azure databases in a few SQL Azure servers.
Also, all the .NET projects have test projects attached to them (including unit-tests, and integration tests, when necessary). From time to time I would run the integration tests, which would interact will all the aforementioned SQL Azure databases and servers.
I do all these from the office (which has a fixed IP, obviously), but the day’s work tends to continue as well at home, where I have a dynamic IP (the investment in procuring a fixed IP at this time is prohibitive, and what would be the fun in that, if I can’t write blog posts such as this).
With the changing of the dynamic IP, comes the burden now to add all the firewall rules to the SQL Azure servers, as well as the hot-red Post-It note at the center of my monitor reminding me to remove my home-bound IP from the clients SQL servers when I am done with the work.
This is a problem and I am determined to find an easier way to deal with it! I have built over time a Powershell-based solution (set of scripts and functions) to help me with this issue and I will try to put it all together in a cohesive solution so it can be put to good use by others such as me that have the same problem.
Building the toolset
To illustrate the steps followed, I will be placing screenshots of the sections of the Powershell file containing most of the necessary pieces to make all this work. The actual scripts will be attached to this post, so look for it for an easy cut-and-paste experience instead of trying to recreate everything form the screenshots.
The ultimate solution consists of 2 powershell files, pinned (via shortcuts) in the middle of my Desktop. One, named Add-SQLAzureFirewallRules.ps1, which will add firewall rules into all the SQL Azure servers bound at my dynamic IP, and the other, Remove-SQLAzureFirewallRules.ps1, which removes all the temporary rules for the same servers.
Here is a step-by-step overview of what these powershell files are made of:
Easier (semi-automated) Azure account login
For my needs I am using my Microsoft Account alias (the @live.com account that is bound to my MSDN subscription) to login to the various Azure subscriptions. The powershell code snipped is used to semi-automate this type of account. These functions are found in the SQLAzureTools.ps1 powershell script file that goes together with the other two, and resides in the same location (folder).
What this function does is as follows:
– It accepts as the first parameter the name of the subscription (in my case, one of the subscription I use is ‘MSDN’). Not to be confused with the subscription GUID id.
– It accepts as the second parameter the string representation of the path to the file where the Azure profile will be saved.
– The body of the function checks of a file with details of the Azure login profile it exists.
– If yes, it loads the details from that profile, and will not prompt the user to re-enter the login details. And it will set the context to the given subscription.
– If no, it will prompt the user to enter the login details, and it will save the Azure profile in the file with the path provided.
For an Azure AD-bound account the code is a bit different, and a draft example is provided below. Note that I have not tested this thoroughly, and I am providing the details as a reference only. Please test before use. The usage is self-explanatory.
Find the external IP address
In order to enter a new firewall rule, you will need to know the external IP from which you are accessing the SQL Azure server. In the SQLAzureTools.ps1 powershell file there is function that does this: Get-ExternalIpAddress.
What this function does is as follows:
– It accepts as a parameter a url of a web api that returns the external ip address form where the call is made. In my case I use: https://api.ipify.org. You can build your own if you prefer, but this one does the job well.
– The return is the external IP address of your local computer
Identify the name of the firewall rule
This is an important step. And this is why. If you have large and diverse teams, who connects to these servers form various locations, and also if you have individuals connecting from their dynamic-IP locations (homes and places in public wifi) you will end up with many firewall rules in these servers. So for each rule you need to know a few things:
– Who created the rule, or for whom the rule is created for?
– Is it permanent or temporary?
– Optionally, is the rule created for a specific application or use (such as to run unittests, or integration tests, etc.).
In the case of the teams I belong to we have decided in the following standards:
– Permanent rules are in lower case (for example, all_permanent_headquarters_financeapps). In this case is a rule that is to remain forever; it applies to all team members; access is granted for and from HQ offices; and is applies to general and operational usage of Finance Applications.
– Temporary rules are in upper case, so they stand out (for example, HG_TEMPORARY_INTEGRATIONTESTS).
– For temporary rules, the creator of the rule will place initials at the beginning of the rule, so it can easily be identified as creator. In case you will need to remove certain rules, you can contact the author and ask.
– The word “temporary” is part of the rule title. This is to identify the rule as temporary and remove it through automation (see below).
– The location, we found out it to be redundant in temporary rules. (Adding “Starbucks” at the rule does not add much in usability.) A temporary rule is made to be very temporary and could be removed by anyone at any given time. Also, if you’d use the same technique explained here, and have many locations, this would mean that you would need to edit the Powershell scripts before running them in each location. This, is too much to ask!
– So, type, author, lifespan, and purpose are the main pieces of details you will need in a rule.
Adding the rules to the server
This is done through the function found in the SQLAzureTools.ps1 boringly named as Add-SqlAzureFirewallRule.
What this function does is rather simple:
– It accepts as a parameter the server name, the resource group where the server is located, the name of the rule, cleverly crafted following the instructions above, and the IP address to add.
– It checks if a rule with the same name exists, and if it does, it removes it. Note that it does not verify the IP address or range. It is very possible that the old rule in the server is a left over and carrying an older IP address. To cover any eventuality, here it updates the rule if it does exists, or it creates a new one, if it doesn’t.
Removing the rules from the server
After the rule is added to the server, the job is done. You can carry on and complete the excellent work that you do. However, it would be inappropriate and a security concern, if you’d leave the temporary rule you just created permanently in the server. When you are done, you will need to remove the rule from the server. To do that, you will need the help of the Remove-SqlAzureFirewallRule function also found in the SQLAzureTools.ps1 script file.
What it does is simple:
– It accepts as a parameter the server name, the resource group where the server is located, and the name of the rule
– It checks if a rule with the same name exists, and if it does, it removes it.
At this point you have all the details you need to make this work successfully and customize it to your desires. Will add just a few extra lines how you can run these scripts, and glue them nicely together.
Running the scripts
To summarize, the SQLAzureTools.ps1 file has all the functionality in it. You will need 2 files AddSqlFirewallRules.ps1 and RemoveSqlFirewallRules.ps1, which will contain all personalized settings that belong to you and your location/machine. It is bets if these 3 files all stay in the same folder. Personally, I have them placed in a special Util folder in my development workspace(s), and have 2 shortcuts, for each file, placed in my Desktop.
What this does is as follows:
– Loads the SQLAzureTools.ps1 script file and its functions. (line 1)
– Gets and retains the external IP for your locations/machine (line 3)
– Collects a few details from you (lines 8-12). Logs you to Azure (line 14)
– Add the rule to the Azure SQL Server (line 17)
– Repeat this (lines 20-24) for multiple subscriptions or servers.
– Stops the script at it completion (line 29). The reason for this is that sometimes there are exceptions and errors, and you would want to review them before dismissing the powershell window.
The RemoveSqlFirewallRules.ps1, works in very similar fashion.
– You don’t need the external IP for this script to work.
– Removes the firewall rule with the same name from the server (line 14)
This is all that is required to add custom, and temporary, Azure SQL server firewall rules to be able to get around the issue with dynamic IPs.
The solution described above has served me and my teams very well, since the early days of the SQL Azure service came to be. However, some improvements to this process and additional features would not hurt. Here are a few:
– The SQL Azure Team could add a “temporary rule” feature that could be assigned to one person (one IP) for a finite period of time (20 minutes; 1 hour; 4 hours, etc.), and let the rule expire after a while.
– If you are like me, a bit concerned about forgetting and leaving behind pieces of information in the server, you can run the RemoveSqlFirewallRules.ps1 in a schedule to remove your firewall rules after you, in case you forgot or fell asleep. This two minutes video shows how to do just that (https://www.youtube.com/watch?v=3Joxkb9y_UA).
– You can use a more powerful solution such as Azure Automation (https://azure.microsoft.com/en-us/services/automation/) that could run the removal scripts in a predefined schedule and remove all temporary firewall rules (every 12 hours or every 24 hours)
Download the powershell-scripts here.