Share via

Connecting on-premises SQL Server using Azure Service Bus Relay

  • Article

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

image

3. Add the ADO.NET EF Implementation

image

4. Implement the interface

image

5. Then add the configuration App.config

image

6. Self host

image

Step 3: Build the Web client.

1. Add Microsoft Azure Service Bus NuGet package

2. Add the following client configuration in Web.config

image

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

image

4. Initialize the Channel and call the method.

image

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!!!

Comments

  • Anonymous
    November 20, 2017
    so who is the caller here? is the same employee who is added via WCF is also now calling the webapi in Azure?i have an O365 site which needs to call an Azure webapi. that is wired to Azure AD. so the caller is authenticated. now i want the same caller to use another windows account to make the call to SQL. will that kind of "impersonation" technique work here?If not, does it only work for "Integrated Security" option? just trying to call it out explicitly.