Fetching SQL Azure firewall rules programmatically

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

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. Send 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
   
   
   

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

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
   
   
   

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(Firewall firewall)
        {
            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("Your SQL Azure Login Name ", "SQL Azure Password", "SQL Azure Database server");
            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,