AllPaul

programming, tech, hobbies and grief

7. April 2014 06:42
by Paul Apostolos
0 Comments

Create a simple MVC application to change SQL Azure firewall rules

7. April 2014 06:42 by Paul Apostolos | 0 Comments

I recently needed a way to allow one of our freelance developers to periodically change a SQL Azure firewall rule to cope with his dynamic IP address. The developer requested administrator access to our Azure portal, but that seemed a bit too much. With that access the developer would have full rights to the portal and that was, from a security standpoint, not an option. 

I decided there had to be a better way to allow him to change a particular firewall rule without sacrificing security.

After a bit of Web searching, I came across a great article series by Brady Gaster. In the series, he walks through the new Windows Azure Management Libraries for .NET (WAML). Of particular note to me was Managing Windows Azure SQL Databases Using the Management Libraries for .NET. Using a few nuggets from this article, I was able to create a simple MVC application to allow the developer to make this change without granting privileges to the Azure portal.

The application

The image below shows a screenshot of the application. It displays the current value of the firewall rule to the user and provides a textbox (prepopulated with the users current IP address) to allow them to save a new value. Note: the values in the screenshot are just placeholders.

For this application, I kept it basic...File->New Project->ASP.NET Web Application

After I tweaked a few style settings I was ready to move on to the WAML goodness.

To use the WAML, I needed to first create a management certificate, upload it to the Azure portal, then export a copy for use in my application. For detailed instructions see the following articles.

Create and Upload a Management Certificate for Windows Azure

Creating a Personal Information Exchange certificate

Once the management certificate was created, uploaded and exported, I was ready to get started with the code.

The code

All of the code for this application is in the HomeController.cs file. The most interesting is the createSqlManagementClient() method. This method uses the certificate .pfx file I created above to create a new X509 certificate. Then, I used that certificate and the Azure subscription ID to create a new SqlManagementClient. With this client I can manage all sorts of SQL Azure stuff including, for my purposes, the firewall settings.

One other item worth noting is the SqlManagementClient.FirewallRules.Update method. The method accepts three arguments: SQL server instance name, firewall rule name, and a FirewallRuleUpdateParameters object.  This object, I named parameters, has a few properties that need to be set:

  1. StartIPAddress - The start of the range of IP addresses to which the firewall rule applies
  2. EndIPAddress - The end of the range of IP addresses to which the firewall rule applies 
  3. Name - The firewall rule name (I know we already supplied that in the update method parameters, but this is to change the name if desired)

*Note: Specifying a StartIPAddress and EndIPAddress of 0.0.0.0 will effectively allow all IP addresses so you may want to add some validation.

Below is the code for the HomeController.cs file.

public ActionResult Index()
{
	var sqlManagementClient = createSqlManagementClient();
	return View(GetSqlFirewallRuleList().Where(f => f.Name == ConfigurationManager.AppSettings["FirewallRuleName"]).SingleOrDefault());
}

[HttpPost]
public ActionResult Index(string IPAddress)
{
	var sqlManagementClient = createSqlManagementClient();
	FirewallRuleUpdateParameters parameters = new FirewallRuleUpdateParameters();
	parameters.StartIPAddress = IPAddress;
	parameters.EndIPAddress = IPAddress;
	parameters.Name = ConfigurationManager.AppSettings["FirewallRuleName"];
	sqlManagementClient.FirewallRules.Update(ConfigurationManager.AppSettings["SQLServerName"], ConfigurationManager.AppSettings["FirewallRuleName"], parameters);
	return View(GetSqlFirewallRuleList().Where(f => f.Name == ConfigurationManager.AppSettings["FirewallRuleName"]).SingleOrDefault());
}

private SqlManagementClient createSqlManagementClient()
{
	//To use this, create and upload your own management certificate for Azure.  Then export the pfx file from Certificates MMC with the private key.
	//Save that file in the root directory called azure-mgt-cert.pfx and add the password to the web.config file
	//The file in this solution is empty and just a placeholder
	var cert = new X509Certificate2(Server.MapPath("/azure-mgt-cert.pfx"), ConfigurationManager.AppSettings["CertificatePassword"], X509KeyStorageFlags.MachineKeySet);
	SqlManagementClient sqlManagementClient = new SqlManagementClient(new CertificateCloudCredentials(ConfigurationManager.AppSettings["SubscriptionID"], cert));
	return sqlManagementClient;
}

private IList GetSqlFirewallRuleList()
{
	var sqlManagementClient = createSqlManagementClient();
	var firewallRuleList = sqlManagementClient.FirewallRules.List(ConfigurationManager.AppSettings["SQLServerName"]);
	return firewallRuleList.FirewallRules;            
}

All that is left to do is add the code for the view (/Views/Home/Index.cshtml) and publish.

Wrapping up

There it is. An easy way to allow non-administrators to configure a SQL Azure firewall rule without granting permission to the portal. 

If you want to look at the entire solution, I created a GitHub repository of the project so feel free to get it and play with it. I removed the confidential information regarding my subscription and certificate from the solution, so you will need to add your own to get it working.

Good luck!

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading