Connect Azure Hybrid Connection to SQL Express

27/01/2015

Azure Hybrid Connection is a really easy way to implement a hybrid solution were you keep some of your data onpremise while still hosting your website in the cloud.

In a recent project I had to connect an MVC application hosted in Azure Web Sites to an onpremise database storing the SQL membership information. Unfortunately my database server is a cheap SQL express server, and all the documentation for setting up Azure Hybrid Connections clearly states that you must an SQL Server with a static port and no named instances. I’m not a SQL Guru so I had to double check my configuration, the following is some screenshots of what I ended up with that is working.

Azure-Hybrid-Connection-Sql-Express

In the SQL Server Configuration Manager you have to be sure that you have enabled TCP/IP and under properties for that protocol, you have to select a TCP Port and clear the field for Dynamic Ports. I selected the standard SQL port of 1433 but any available port should do. I would also assume that you can set it only for the IP address that you want the server to use, but I didn’t test that.

Setting up the Azure Hybrid Connection is a matter of following the documentation. It’s very simple, once everything is setup in the Azure Portal, you can download the hybrid connection client and install it with the ClickOnce installer. It installs the Azure Hybrid Connection Manager Service on your machine, which is responsible to talking to the Biztalk “Relay Agent” (Relay Agent is not the official term, I actually don’t know if they named the component anything) in Azure and that way establish a connecting between the cloud and your onpremise machine, without opening the firewall to access from the outside. Azure Hybrid Connection Manager

The last thing I want to note is that it’s very important that you use the right type of connection string in your Web Site for hybrid connection to work. It is obvious if you follow every step of the guide, but if you are like me and think you know better then you might not notice that the connection string format must be like:

Server=simtex,1433;Database=aspnet-SJKP.MembershipWeb.Local-20150114085805;User Id=sjkp; password=[YOURPASS];Connection Timeout=5

Obviously windows integrated login will not fly in this setup, so you have to do SQL authentication.