In this post we will fetch firewall rules associated with Database server in SQL Azure programmatically.

First step is to create a FireWallRule class. This class contains three properties corresponds to three basic ingredient of firewall rules in SQL Azure.

1. Name of the firewall

2. Start IP address

3. End IP address

FirewallRule.cs


using System.Net;

namespace ConsoleApplication15
{
 public  class FirewallRule
 {
 public FirewallRule(string name, string startIPAddress, string endIPAdress)
 {
 this.Name = name;
 this.startIPAddress = IPAddress.Parse(startIPAddress);
 this.endIPAdress = IPAddress.Parse(endIPAdress);
 }

public string Name { get; set; }
 public IPAddress startIPAddress { get; set; }
 public IPAddress endIPAdress { get; set; }
 }

}

 

Once FirewallRule class is ready, we need a class to

1. Provide credential to SQL Azure

2. Create connection string to connect with SQL Azure.

3. Perform different operations like adding, fetching and deleting rules.

To perform above listed operation, let us create a class called Firewall. This class has four properties

1. Login

2. Password

3. Server name

4. Connection string. In constructor of Firewall class , we will construct connection string

clip_image002

To fetch Firewall rules, we will perform simple ADO.Net operation as below. Add a method in class.

clip_image004

 

1. Function is returning list of FirewallRule.

2. Creating SQL Connection by passing connection string constructed in constructor of class.

3. To fetch firewall rules , we need to execute the below select

clip_image006

Full code for Firewall class is as below,

Firewall.cs


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication15
{
 public class Firewall
 {

public string Login { get; set; }
 public string Password { get; set; }
 public string ServerName { get; set; }
 public string ConnectionString { get; set; }

public Firewall(string login, string password, string server)
 {
 this.ServerName = server;
 this.Login = login;
 this.Password = password;
 SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
 connectionStringBuilder.DataSource = string.Format(string.Format("tcp:{0}.database.windows.net,1433", ServerName));
 connectionStringBuilder.InitialCatalog = "master";
 connectionStringBuilder.UserID = this.Login;
 connectionStringBuilder.Password = this.Password;
 connectionStringBuilder.Pooling = true;
 this.ConnectionString = connectionStringBuilder.ToString();
 }

public List<FirewallRule> GetFireWallRules()
 {
 List<FirewallRule> lstFirewall = new List<FirewallRule>();
 using (SqlConnection conn = new SqlConnection(this.ConnectionString))
 {
 using (SqlCommand cmd = conn.CreateCommand())
 {
 conn.Open();
 cmd.CommandText = "select name , start_ip_address, end_ip_address FROM sys.FireWall_rules ";
 using (SqlDataReader reader = cmd.ExecuteReader())
 {
 while (reader.Read())
 {
 lstFirewall.Add(new FirewallRule(reader["name"] as string,
 reader["start_ip_address"] as string,
 reader["end_ip_address"] as string));
 }
 }
 }
 return lstFirewall;
 }
 }
 }
}

We have FirewallRule and Firewall class in place. Now we need to call GetFireWallRules() function to fetch and print firewall rules.

Program.cs


using System;
namespace ConsoleApplication15
{
 class Program
 {
 static void Main(string[] args)
 {
 Firewall firewall = new Firewall("dj", "dj", "ybee9uf6lq");
 var result = firewall.GetFireWallRules();
 foreach (var r in result)
 {
 Console.WriteLine("Firewall Rule Name {0} : Start IP {1} , End IP {2}", r.Name, r.startIPAddress, r.endIPAdress);
 }
 Console.ReadKey(true);

 }
 }
}

Press F5 to fetch all the firewall rules,

clip_image008

Advertisements

6 thoughts on “Fetching SQL Azure firewall rules programmatically

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s