The commoditization of technology has reached its pinnacle with the advent of the recent paradigm of Cloud Computing. Infosys Cloud Computing blog is a platform to exchange thoughts, ideas and opinions with Infosys experts on Cloud Computing

« Big Data and Cloud Computing | Main | Practicing Agile Software Development on the Windows® Azure™ Platform »

Step by step approach to expose on-premise database using Azure infrastructure - Part 2

In the last blog we understood the usage of Azure-connect to expose on-premise SQL database and accordingly the points of concern while doing that and also the benefit. In this blog we will understand another approach using Azure appfabric Service Bus.

Option 2 - Expose the on-premise database to the consumer (WCF service) in the Azure webrole (or any external consumer) and over HTTP(s) using Azure appfabric Service Bus

Later in the post, we will understand the different obligations/benefits that one should take into consideration before making the decision for this approach.

Steps to be followed

1.     Install the ADO.NET Entity Framework. Currently the latest is 4.1 and could be installed from here. This will add the required supporting libraries and the Visual Studio templates.

2.     Create a WCF Service Application project and make sure to set the target framework to 3.5 or higher. To the newly created project add a new ADO.NET Entity Data Model project item:


3.     In the Entity Data Model wizard, choose "Generate from database" and click "Next":


4.     In the next screen, click on Connection and in the pop-up provide the Server Name,  Authentication details and then select the concerned database:


And once connection to the SQL server database is established successfully, on click of OK, the connection details are shown:


5.     Select the table(s) of concern and click on Finish to show the resultant entity model details:



6.     Once the data model is created for the concerned tables, these tables need to be exposed as OData RESTful interfaces, and for this add a new WCF Data Service project item to the project:


7.     In the newly added service code behind file (e.g. WcfDataService1.svc.cs), there is a placeholder for the database entities created earlier in step 4 & 5:


Substitute with the correct object defined in the data model code behind (e.g. Model1.edmx à Model1.Designer.cs inherited from ObjectContext):




8.     To define the rules for the different entities for their availability for operations like readable, updatable, etc, config.SetEntitySetAccessRule could be used. For example to allow all operations on all entities:

config.SetEntitySetAccessRule("*", EntitySetRights.All);



9.     Now it is required to expose the so-formed service endpoint to the client outside the corporate network (firewall) using Azure appfabric service bus. For this it is needed to explicitly add a service with end point binding -" webHttpRelayBinding"  to the project configuration file:


      <service name="ExposeOnPremiseSQL.WcfDataService1" behaviorConfiguration="SQLExposeServiceBehavior">

        <endpoint name="RESTEndPoint" address="" behaviorConfiguration="webhttpSharedSecretClientCredentials" contract="System.Data.Services.IRequestHandler" binding="webHttpRelayBinding" bindingConfiguration="webHttpRelayEndpointConfig" />






        <binding name="webHttpRelayEndpointConfig">

          <security mode="None" relayClientAuthenticationType="None" />






a.     services-bus-namespace- the appfabric service bus namespace created in the Azure account subscription though which the service will be exposed

b.     webhttpSharedSecretClientCredentials- the service endpoint behavior having the credentials to be used by the service to authenticate and authorize itself to use the Azure service bus:


      <behavior name="webhttpSharedSecretClientCredentials">

        <transportClientEndpointBehavior credentialType="SharedSecret">


            <sharedSecret issuerName="issuer-name" issuerSecret="secret-key" />



        <webHttp />




Issuer-name and secret-key are like user-name and password generated during the creation of the service bus namespace to authorize a service or client for service, to expose service through service bus or call a service endpoint exposed through the service bus respectively. webHttp is to support RESTful WCF service.

c.     System.Data.Services.IRequestHandler- the contract for the WCF service to be exposed as OData WCF Data Service.

10.     Makes sure to comment:

<serviceHostingEnvironment multipleSiteBindingsEnabled="true" aspNetCompatibilityEnabled="true" />


Because Azure service bus currently doesn't support multiple IIS bindings per site.

11.     Once the above configurations are done, try to browse the WcfDataService1.svc


        e.g. http://localhost:20918/WcfDataService1.svc/ .

This will list all the database tables set to be exposed through the data service (in accordance with the step- 5 above). This operation will also register the service to the service-bus with the configured end point address. E.g.: (will also list the table(s) available to be browsed)

Now giving any table name try to view its contents as:

12.     While hosting the so-formed service in IIS, make sure to enable auto-start for the service.

Points to be considered

The below few paragraphs will try to highlight some points that may be considered while making the right decision:

1.     Since the WCF data service is bound to the table schema, in case there is any change in the table schema in future, the new entity data model in liaison with the new schema needs to be updated explicitly in the WCF data service otherwise the response may not be as expected.

2.     Since the RESTful WCF service is exposed through Azure appfabric service bus as the intermediate layer, message mode security will be more applicable than the transport mode based security.

3.     Once the service is deployed in the on-premise IIS, make sure to enable auto-start for the service. When any wcf service is hosted in IIS, the service is ignited /started and made to be consumable only when the first request is received. But in this case, service in concern needs to be started before even any request for the service is made. This is because only once the service is started, the service is registered to the service bus and a public URL is exposed which a client in the public (internet) may refer to while making a service request. To achieve this we need to enable the auto-start for the service i.e. the moment the hosting service is started (i.e. IIS), the service hosted also gets started.

4.     When we are exposing an on-premise service through Azure appfabric service bus there is no concept of load balancing even at the service-bus end, as there will be only one instance running to respond. And more over in this scenario, multiple IIS bindings per site is also not allowed currently.


1.     Without making any changes to the on-premise machine having the SQL server, the entire data can be exposed and consumed from outside firewall, i.e. even the physical access to the said machine is not required.

2.     Since now windows integrated authentication can be used to connect to the SQL server instance in concern from the on-premise service, proper access control can be put in place.

3.     Defining proper service endpoints, both internal and external (to corporate firewall) clients for the service could be handled independently. For example over scheme "http" for internal and "https" for external clients.

4.     With this approach, while defining the entity data model, even the access to the database could be restricted in terms of type of accessibility. For example only "read" access to the clients outside of corporate firewall.

5.     One can easily consume these OData services by making a service reference in the .net client application. This is will create proxy having container for each of the database tables and using simple LINQ query like the below we can fetch the data

var result = from entity_name in new service_proxy.dataEntityModelNameContainer(new Uri(serviceUrl)).table_name

where entity_name.property_name == <property value>

select entity_name;



Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Please key in the two words you see in the box to validate your identity as an authentic user and reduce spam.