Click here to monitor SSC
  • Av rating:
  • Total votes: 2
  • Total comments: 0
Bud Aaron

Writing a small application to manipulate the SQL Database firewall

18 May 2012

How can you getting through the Azure firewall to allow remote access to SQL Azure, oops, Windows Azure SQL Database (formerly SQL Server Data Services, then SQL Services and SQL Azure)? The easy way is to use the Windows Azure Portal  but you can also script the process to access the RESTful service

Arg – it’s no longer SQL Azure, it’s now SQL Database! This article started out as a simple discussion of how to manipulate the SQL Database (was SQL Azure) firewall through REST calls, but on the way Microsoft threw me under the bus by completely changing branding names for what was Azure. To crawl out from under the bus I decided to use the new naming conventions which are listed below for all the world to see.

I have to say I’m not terribly happy about the changes but that’s probably not going to change anything so I guess I just need to update my thinking.

Now I know just enough about firewalls to be dangerous. I know they’re designed to help prevent uninvited guests from messing with my data, and that you can poke holes in them to allow invited guests into the database. I call it poking holes in the firewall, here’s a walkthrough on getting through a firewall to allow sqlserver.exe remote access.

Doing it the easy way

The Windows Azure Portal provides a way to Add, Edit and Delete firewall rules. First Select Database in the portal and navigate to the SQL database you’ve set up.

Windows Azure Portal

Select the Firewall Rules: button under Server Information to get this:

Firewall Information

Notice that the button says Firewall Rules followed by “: 2” indicating that 2 rules are in place. Click the Add button to get this:

Add new firewall rule

Fill in a new firewall rule:

Fill in firewall rule information

And click OK to get this:

Firewall rule overview

 You can also Update or Delete the rules here. This is truly the simplest way to poke holes in the firewall as needed but now let’s do it by writing a program in Visual Studio 11 using Visual Basic.

First Things First

In order to get this done you will need to create a self-signed certificate. In order to make the certificate easily findable just make a temp folder on the C: drive. It’s a very short navigation trip when you need to point to files such as the certificate we’re going to generate.

Creating a certificate

In order to do many of the things we’re planning to, we’ll need an encryption certificate. In a production environment you will want to get your certificate from one of the many companies that who issue certificates but for development you can use the makecert command to generate a self-signed certificate. To do this I suggest you create a C:\temp folder making it easy to retrieve. In the Start menu under Visual Studio 2010 Tools you will find a command prompt. Click this to bring up the VS 2010 command prompt and navigate to your newly created temp folder.

It may be that I’m the only person in the world who just recently learned how to ‘paste’ in a command window but I’m so proud. On the off chance that you’ve never used it, I’m going to explain how it’s done. Clicking the little C:\ icon in the upper left corner of the command window brings up the following menu.

Now you can copy the makecert command shown below into the command window and press return to execute the command. Better yet copy it into notepad and edit it to suit your needs, then copy and paste to execute the command.

makecert -sky exchange -r -n "CN=dnccert.cer" -pe -a sha1 -len 2048 -ss My "dnccert.cer"

I tell you this because I HATE trying to type long commands into the command window because I invariably mistype at least a half dozen times and then frequently get an error. So now I compose the command in notepad and then copy and paste it. I wish I’d known about this years ago. You’ll get the following message when your certificate has been successfully created in the C:\temp folder.

You can find more detailed information about the makecert command here:http://msdn.microsoft.com/en-US/library/bfsktky3(v=VS.80).aspx. Add the certificate to your portal, Open your Windows Azure management portal and select Hosted Services, Storage Accounts & CDN. Then select Management Certificates. You should see this screen:Hosted Services, Storage Accounts & CDN

Click the Add Certificate icon in the top left corner. In the dialog box, browse for the certificate in the C:\temp folder if that’s where you saved the certificate.

Add Management Certificate

 Click OK to import the certificate and then make a copy of the Thumbprint in the text file you should be using to save things you need for this project.

Managing the Azure Firewall

The app we’re building is named SQLAzureFirewallManagement. The main form will be named FirewallDetails. I will use the capture below to give you layout details for the main form. I’ve given each of the controls a number and the table following the dialog will show that number followed by the control type, its name and text, followed by its left and top position, and finally its height and width.

Now the code:

Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks
Imports System.Windows.Forms
Imports System.Xml.Linq
Imports System.Security.Cryptography.X509Certificates
Imports System.IO
Imports System.Net

Public Class FirewallDetails

    Private Sub btnBrowse_Click(sender As Object, e As EventArgs)
Handles btnBrowse.Click
        Dim input As String = String.Empty
        Dim ofd As New OpenFileDialog()
        ofd.Filter = "cer files (*.cer | *.cer"
        ofd.InitialDirectory = "C:\temp"
        ofd.Title = "Select a certificate"

        If ofd.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            txtCertPath.Text = ofd.FileName
        End If

    End Sub

    Private Sub btnOK_Click(sender As Object, e As EventArgs) Handles btnOK.Click
        Dim certfile As String
        Dim subscriptionID As String
        Dim servername As String

        certfile = txtCertPath.Text
        subscriptionID = txtSubID.Text
        servername = txtServerName.Text

        firewallList.Items.Clear()
        GetServerFirewallRules(certfile, subscriptionID, servername)

    End Sub

    Private Sub GetServerFirewallRules(certfile As String, subscriptionID
As String, server As String)
        Try
            Dim url As String = String.Format("https://management.database
.windows.net:8443/{0}/servers/{1}/firewallrules", subscriptionID, server)
            Dim webRequest As HttpWebRequest = TryCast(HttpWebRequest.Create(url),
HttpWebRequest)

            webRequest.ClientCertificates.Add(New X509Certificate(certfile,
"private key password"))
            webRequest.Headers("x-ms-version") = "1.0"
            webRequest.Method = "GET"

            comboList.Items.Clear()

            Using webresponse As WebResponse = webRequest.GetResponse()
                Using stream As Stream = webresponse.GetResponseStream()
                    Using sr As New StreamReader(stream)
                        Dim xml As String = sr.ReadToEnd()
                        Dim doc As XDocument = XDocument.Parse(xml)

                        Dim sc As XNamespace = "http://schemas.microsoft.com
/sqlazure/2010/12/"

                        Dim query = From s In doc.Elements(sc + "FirewallRules")
.Elements(sc + "FirewallRule") Select s

                        firewallList.Items.Add("=========================")

                        For Each elm As XElement In query
                            firewallList.Items.Add(elm.Element(sc + "Name")
.Value.ToString())
                            comboList.Items.Add(elm.Element(sc + "Name")
.Value.ToString())
                            firewallList.Items.Add(elm.Element(sc +
"StartIpAddress").Value.ToString())
                            firewallList.Items.Add(elm.Element(sc +
"EndIpAddress").Value.ToString())

                            firewallList.Items.Add("=========================")
                        Next
                    End Using
                End Using
            End Using
        Catch webEx As WebException
            Dim errorResponse As HttpWebResponse = DirectCast(webEx.Response,
HttpWebResponse)

            Try
                Using errrs As Stream = errorResponse.GetResponseStream()
                    Using sr As New StreamReader(errrs)
                        MessageBox.Show(sr.ReadToEnd().ToString())
                    End Using
                End Using
            Catch innerex As Exception
                MessageBox.Show(innerex.ToString())
            End Try
        Catch ex As Exception
            MessageBox.Show(ex.ToString() + vbLf)
        End Try
    End Sub

    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles
btnAdd.Click
        Dim certfile As String
        Dim subscriptionID As String
        Dim servername As String
        Dim ruleName As String
        Dim startIP As String
        Dim endIP As String

        certfile = txtCertPath.Text
        subscriptionID = txtSubID.Text
        servername = txtServerName.Text
        ruleName = txtRuleName.Text
        startIP = txtStartIP.Text
        endIP = txtEndIP.Text

        SetServerFirewallRule(certfile, subscriptionID, servername, ruleName,
startIP, endIP)

    End Sub

    Private Sub SetServerFirewallRule(certfile As String, subscriptionID As String,
server As String, ruleName As String, startIP As String, endIP As String)
        Try
            Dim url As String = String.Format("https://management.database
.windows.net:8443/{0}/servers/{1}/firewallrules/{2}", subscriptionID, server,
ruleName)
            Dim webRequest As HttpWebRequest = TryCast(HttpWebRequest.Create(url),
HttpWebRequest)

            webRequest.ClientCertificates.Add(New X509Certificate(certfile,
"private key password"))
            webRequest.Headers("x-ms-version") = "1.0"
            webRequest.Method = "PUT"

            Dim xmlbody As String = "<?xml version=""1.0"" encoding=""utf-8""?
>" + vbLf + "<FirewallRule " + vbLf + "
xmlns=""http://schemas.microsoft.com/sqlazure/2010/12/"" " + vbLf +
" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" " + vbLf +
" xsi:schemaLocation=""http://schemas.microsoft.com/sqlazure/2010/12/
FirewallRule.xsd""> " + vbLf + " <StartIpAddress>" + startIP.ToString()
+ "</StartIpAddress>" + vbLf + " <EndIpAddress>" + endIP.ToString()
+ "</EndIpAddress" + vbLf + ">" + "</FirewallRule>"

            Dim bytes As Byte() = Encoding.UTF8.GetBytes(xmlbody)
            webRequest.ContentLength = bytes.Length
            webRequest.ContentType = "application/xml;charset=uft-8"

            Using requestStream As Stream = webRequest.GetRequestStream()
                requestStream.Write(bytes, 0, bytes.Length)
            End Using

            Using response As WebResponse = webRequest.GetResponse()
                MessageBox.Show("Rule Added")
            End Using
        Catch webEx As WebException
            Dim errorResponse As HttpWebResponse = DirectCast(webEx.Response,
HttpWebResponse)

            Try
                Using errrs As Stream = errorResponse.GetResponseStream()
                    Using sr As New StreamReader(errrs)
                        MessageBox.Show(sr.ReadToEnd().ToString())
                    End Using
                End Using
            Catch innerex As Exception
                MessageBox.Show(innerex.ToString())
            End Try
        Catch ex As Exception
            MessageBox.Show(ex.ToString() + vbLf)
        End Try
    End Sub

    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles
btnDelete.Click
        Dim certfile As String
        Dim subscriptionID As String
        Dim servername As String
        Dim ruleName As String

        certfile = txtCertPath.Text
        subscriptionID = txtSubID.Text
        servername = txtServerName.Text
        ruleName = comboList.SelectedItem.ToString()

        DeleteServerFirewallRule(certfile, subscriptionID, servername,
ruleName)

    End Sub

    Private Sub DeleteServerFirewallRule(certfile As String, subscriptionID As
String, server As String, ruleName As String)
        Try
            Dim url As String = String.Format("https://management.database.windows
.net:8443/{0}/servers/{1}/firewallrules/{2}", subscriptionID, server, ruleName)
            Dim webRequest As HttpWebRequest = TryCast(HttpWebRequest.Create(url),
HttpWebRequest)

            webRequest.ClientCertificates.Add(New X509Certificate(certfile,
"private key password"))
            webRequest.Headers("x-ms-version") = "1.0"
            webRequest.Method = "DELETE"

            Using wr As WebResponse = webRequest.GetResponse()
                Using stream As Stream = wr.GetResponseStream()
                    Using sr As New StreamReader(stream)
                        MessageBox.Show("Rule Deleted")
                        firewallList.Items.Clear()
                    End Using
                End Using
            End Using
        Catch webEx As WebException
            Dim errorResponse As HttpWebResponse = DirectCast(webEx.Response,
HttpWebResponse)

            Try
                Using errrs As Stream = errorResponse.GetResponseStream()
                    Using sr As New StreamReader(errrs)
                        MessageBox.Show(sr.ReadToEnd().ToString())
                    End Using
                End Using
            Catch innerex As Exception
                MessageBox.Show(innerex.ToString())
            End Try
        Catch ex As Exception
            MessageBox.Show(ex.ToString() & vbLf)
        End Try
    End Sub
End Class
Bud Aaron

Author profile:

Bud Aaron has been actively involved in electronics, computers and software for over 70 years. Bud taught military and civilian courses in electronics, computers and programming and has sold over 100 magazine articles and books to major publishers. In 1976 Bud helped found BusinessMaster to write accounting software. That led to the founding of CheckMaster in 1992. CheckMaster changed its name to DotNetChecks in 2007. Bud has been actively involved as a Microsoft beta tester for many years. Bud is a biographee in Marquis Who's Who in America, Who's Who in the West, Who's Who in the World, and Who's Who in the Media and Communications.

Search for other articles by Bud Aaron

Rate this article:   Avg rating: from a total of 2 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.
 

Top Rated

Data Science Laboratory System – Object-Oriented Databases
 Object-Oriented Databases (OOD) avoid the object-relational impedence mismatch altogether by tightly... Read more...

Tales from a Cloud Software Firm
 Following on from a discussion about how people are using the cloud, the Simple-Talk Editorial Team sat... Read more...

Data Science Laboratory System – Document Store Databases
 A Document Store Database (DSD) is similar to a Relational Database Management system with the... Read more...

Data Science Laboratory System - Instrumentation
 It is sensible to check the performance of different solutions to data analysis in 'lab' conditions.... Read more...

Testing the StreamInsight Service for Windows Azure
 Getting 'up to speed' with StreamInsight is easier if you take the time to run it and test it out.... Read more...

Most Viewed

Windows Azure Virtual Machine: A look at Windows Azure IaaS Offerings (Part 2)
 We continue our introduction of the Azure IaaS by discussing how images and disks are used in the Azure... Read more...

PHPFog and Pagoda Box: A Look at PHP Platforms
 Cloud platforms such as Heroku, AppEngine, PHPFog and Pagoda Box are ideal for companies who just want... Read more...

An Introduction to Windows Azure BLOB Storage
 Azure BLOB storage is persistent Cloud data storage that serves a variety of purposes. Mike Wood shows... Read more...

Managing session state in Windows Azure: What are the options?
 Because you can't maintain session state for ASP.NET applications in Azure using the default in-process... Read more...

Creating a custom Login page for federated authentication with Windows Azure ACS
 Windows Azure Acess Control Service (ACS) provides a way of authenticating users who need to access web... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.