Connecting on-premises SQL Server using Azure Service Bus Relay

This issue seems very common rather we can call it a requirement.

Business Need. We have a Web Site (assume hosted in Azure). Now we have a SQL Server located locally behind the firewall. We cannot move this database outside of our data center. One of the easiest ways to use it through Service Bus Relay. To know more about Service Bus Relay please refer Azure Documentation at https://azure.microsoft.com/en-us/documentation/articles/service-bus-relay-overview/ 

Assumption. We assume that you know what is Service Bus Relay is and comfortable writing basic WCF Service with Data Access code using ADO.NET Entity Framework.

Probable Solution. Our SQL Server Database in within on-premises data center. This SQL Server uses Windows Authentication. We would have a WCF Service created inside the same datacenter so that we can access it locally.

Actual Code.

Step 1: We will create a Service Bus namespace in our Azure Portal. Let us name is wgonpremsql. This would have following information,

1. DNS wgonpremsql.servicebus.windows.net

2. Root Manage Shared Access Key

Step 2: Now we will create a Console Application to write our WCF Service.

1. Add Microsoft Azure Service Bus NuGet package.

2. Add the Interface for Service Contract

3. Add the ADO.NET EF Implementation

4. Implement the interface

5. Then add the configuration App.config

6. Self host

Step 3: Build the Web client.

1. Add Microsoft Azure Service Bus NuGet package

2. Add the following client configuration in Web.config

3. Copy the Interface as-is and keep in the project, including the same namespace

4. Initialize the Channel and call the method.

Now if you first run the Console and then run the web site, they would be able to communicate. You can check the Web Site in Azure Web App to see if it is inserting the data.

Note:

1. This is not a first-class data access code so none of the conventions are followed here. This is just to help kick start.

2. Try not to do mistake in XML file. Because it is tough to debug if there is any issue.

3. Remember Service Bus relay does not need any code to deploy, so for service bus your code will be deployed on-premises and hosted by you. Service bus would give you an endpoint that's it. Beauty!!!!

4. Follow all possible security guidelines when implanting database access through Service Bus. Don't just rely on Shared Access Key.

5. Service Bus Relay elegantly solves the Firewall issue. You neither need to expose your internal resource or open Firewall. For all possible Service Bus Port listing please refer here https://msdn.microsoft.com/en-us/library/azure/ee732535.aspx 

Namoskar!!!