I’ve been speaking about Azure at a number of events recently. One of the biggest challenges that I have had each time is due to local firewall restrictions. Firewalls will block port 1433/1434 outbound and this a major hassle to get changed, especially for a 20 minute demo. These ports are generally restricted due to the SQL slammer and SQL snake viruses (http://www.grc.com/port_1434.htm) from a few years ago.

SQL Azure uses port 1433 for communication, for instance when connecting from management studio to SQL Azure you get a dialog such as this:

With the port blocked, the connection attempt simply times out. One simple solution is to create a VPN connection to another network that has firewall rules configured to allow access. While this works, it does add latency, not only, to the SQL Azure communication but to all internet communication. However, there are other approaches.

Port Bridge

If you haven’t read Clemens Vasters’ post on Port Bridge, then I can highly recommend it: http://vasters.com/clemensv/CommentView.aspx?guid=3e35d8bd-b755-453f-8c63-1a57c570eb4c. Port Bridge is a sample application that provides point to point tunnelling between a client and server. It does this by wrapping the protocol and port used for the communication within a SOAP message. This message can then be relayed using the Windows Azure AppFabric Service Bus and unwrapped at the destination.

The Service Bus provides the ability to connect on-premise applications together or to connect an on-premise application to an application in the cloud. The Service Bus addresses the challenges presented by firewalls, NATs and dynamic IP, so your endpoints do not have to be published directly to the Internet just have Internet access.

The diagram is from Clemens’ original port and describes exactly the situation and solution to my current issue, where SQL Server is actually SQL Azure.

Using Port Bridge

Back to the original challenge: to demo SQL Azure by connecting from SQL Server Management Studio and an application on my local machine.

My office network is set up so that I have access to SQL Azure, so my first approach was to run the Port Bridge Service from my PC back in the office. With the Port Bridge Agent running on my demo laptop listening on port 1433. To set up the Port Bridge Agent to proxy message to SQL Azure, configure the local TCP port and remote TCP port to 1433 and set the target host as the SQL Azure database server name.

  <portBridgeAgent serviceBusNamespace="myservice" 
serviceBusIssuerName="owner" serviceBusIssuerSecret="x1x1x1x1x1x1xx1x1x1=">
    <portMappings>
      <port localTcpPort="1433" targetHost="x2wqed1zpk.database.windows.net" 
remoteTcpPort="1433">
        <firewallRules>
          <rule source="255.255.255.255" /> <!-- My local IP address -->
        </firewallRules>
      </port>
    </portMappings>
  </portBridgeAgent>

In order for the Port Bridge Agent to listen on port 1433, I need to ensure that anything else listening on port 1433 is stopped (such as the SQL Server service). To connect to the SQL Azure database I need to specify “localhost” as the server name when connecting and provide the login/password as usual. This traffic is intercepted by the Port Bridge Agent and routed via the Service Bus to the Port Bridge Service and then to the target host, “x2wqed1zpk.database.windows.net”, the SQL Azure database.

Using Port Bridge in Windows Azure

While I can conveniently run the Port Bridge Service on my PC as a windows service (or a console application), it would be useful to have a little more control over when this service is running and a little more resilience. These are exactly the features that a Windows Azure worker role would provide. I would be able to stop and start the Port Bridge Service remotely and would not need any access to my PC or my office network.

Starting from the Port Bridge sample application, to create a Port Bridge Service worker role requires a few steps.

• Add a new Cloud project and a worker role to the sample solution.
• To the worker role, add references to the “Microsoft.Samples.ServiceBus.Connections” and “PortBridge” projects in the solution.
• Also add a reference to Microsoft.ServiceBus.dll, ensuring that you set the “Copy Local” property to true.
• With a little refactoring of the PortBridge Program Main, this method can be used from the worker role (there is certainly scope to provide a much neater solution).
• Finally, update the app.config with the service settings:

  <portBridge serviceBusNamespace="myservice" serviceBusIssuerName="owner" 
serviceBusIssuerSecret="x1x1x1x1x1x1xx1x1x1=">
    <hostMappings>
      <add targetHost="x2wqed1zpk.database.windows.net" allowedPorts="1433" 
allowedPipes="sql/query" />
    </hostMappings>
  </portBridge>

NB: The Windows Azure instances do not have a copy of the Microsoft.ServiceBus.dll assembly installed in the GAC. Therefore to ensure that the application will run correctly, a copy of this assembly must be deployed as part of the package. This is achieved by setting the “Copy Local” property to true. This is one of the differences between the development fabric and the live Windows Azure instance.

With the Port Bridge Service deployed to a worker role, I can now connect to SQL Azure from my demo laptop without the challenges presented by firewalls. By using a Worker Role I also reduce the network latency as all the components are hosted in the Cloud. By ensuring that I select the same data centre i.e. North Europe for all three components, this adds very little latency to the whole communication compared to direct SQL Azure access.