Using Azure Hybrid Connections to connect to SQL On-Prem Database from Azure WebJob.


Updated = 23rd September 2018.

  • Hybrid Connections allows Azure Web Sites and Services to securely connect to the on-premises resources hosted within the corporate network, without requiring any change to firewall or network.

Hybrid

Prerequisites: –

  • Azure Subscription
  • TCP/IP protocol needs to be enabled.
  • SQL Authentication (1433 Default Port for Default Instance)
  • Windows Server 2008 or later and outbound internet connection and can talk with LOB application – SQL Database.

 

  • Multiple Hybrid Connection Manager can be installed in separate server that can do the load balancing.
  • Hybrid connection implements Transport Layer Security between the cloud and the on-premise endpoint for data encryption.
  • Hybrid Connection Uses Shared Access Signature for securing the End Points.

 

Here we’d take a simple example of creating an Azure WebJob that will connect to the SQL On-Prem Database.

To start with, create a console application that connects to SQL On-Prem Database and pulls information from a table inside it.

Publish it as Azure WebJob.

Click on Publish to publish the WebJob.

Back in Azure Portal, we’d see our WebJob. Click on Run to start the WebJob. And click on Logs.

As expected we’d get the SQL Exception as Web Job will not be able to connect to the On-Prem Database.

Now let us configure Hybrid Connection to get the WebJob up and running properly. Inside the App Service select Networking and click on Configure your hybrid connection endpoints.

Click on Add hybrid Connection.

Here Endpoint Host will be the name of our OnPrem Machine\Server, Endpoint Port will be the Port for the SQL Server (1433 for the default instance).

Next step is to install and configure Hybrid Connection Manager.

Once installed, open the Hybrid connection manager and click on Add a new Hybrid Connection

Log in with the Azure Subscription Credentials and select the Hybrid Connection created there.

If everything is correct, it should show the status as connected.

*I had to restart the Azure Hybrid Connection Manager Service after adding the connection for the Azure status to show as connected

Now back in our WebJob, let us click on Run.

In logs,

we’d see the data successful fetched from our SQL On-Prem DB.

In case of named instance of SQL, we need to create a new hybrid connection with the specific TCP Port used by that named instance.

To find the port used by the named instance, open SQL Server Configuration Manager, select the named instance of SQL and copy the value of TCP Dynamic Ports.

This is how our Hybrid Connection string will look like

The other important point to remember is that the connection string used should also specify the port.

Hope it helps..

Advertisements

Author: Nishant Rana

I love working in and sharing everything about Microsoft.NET technology !

4 thoughts on “Using Azure Hybrid Connections to connect to SQL On-Prem Database from Azure WebJob.”

  1. Does this mean that it will work only with SQL Authentication? I’m stuck in a case where I need to use a Windows authentication to pull data from an on-premises SQL Server

    Like

  2. did the exact steps and getting below error
    05/23/2018 22:55:04 > 5d6388: ERR ]

    [05/23/2018 22:55:04 > 5d6388: ERR ] Unhandled Exception: System.Data.SqlClient.SqlException: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

    [05/23/2018 22:55:04 > 5d6388: ERR ] at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)

    Like

Please share your thoughts

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.