How to Access Perm SQL Data in Azure Web App Services

This post explains how to create a hybrid connection and install a hybrid connection manager in the database server to access its data

Introduction 
 
After publishing a web app in Azure web app service, there may be a scenario where we need to query data from perm data. A hybrid connection needs to be created to connect the Azure app service to our perm data. If we try to query the data which is present in perm, we will get an unresolved error to find the server/SQL server details, as the database server resides in a private network in the organization rather than on the internet.
 
We will only be able to query the data present in the DB server if the DB server has a public IP/Host name that can be resolved with the Azure DNS server. There are multiple ways to expose the public IP.
  1. Creating VNet Integration with Azure VPN Gateway
  2. Azure App service Hybrid connection.
This post explains how to create a hybrid connection and install a hybrid connection manager in the DB server to access its data. Please follow the steps to successfully establish a connection between Azure and Perm Server.
 
Note - Hybrid connection is only possible in a standard tier, like [ B1-B3]. It is not available in the free tiers subscription. The main difference is that these tiers enable multiple connections for one app service. B1 is the least with 5 connections.
 
Step 1 - Navigate to App Service, scroll down to the setting option 'Creating Hybrid Connection in App server'
 
 
 
Step 2 - Click on the configuration of Hybrid connection
 
 
 
Step 3 - Please note we need to provide the hostname of the SQL server details where the query is going to be made. It will not work if the IP address is provided unless it is a public IP. Since it is an SQL server, we need to provide the port number as 1433.
 
Best practice is to use the namespace to include the word as BUS. For hybrid connection use Con.
 
Host Endpoint -> SQL server Hostname
Port -> 1433
 
 
 
 
 
Step 4 - Download the “ Azure Hybrid connection Manager “ and Install it in your SQL Server (or) any network where the SQL server is part of it.
 
Note – the connection string property can be copied once the hybrid connection is created in Azure
 
 
 
 
Step 5 - Once everything is set up properly, the status of the connection will be “Connected” in the Azure portal. If we run the SQL query, the output will be printed in a web application.
 
Troubleshooting if issues arise
  1. Named pipe server is not open [ Unable to connect to server ]
    Solution - Please enable the setting by going to SQL Configuration Manager
  2. Unable to find the server 10.xx.xx.xxx
    Solution - Rather than using the IP server of the SQL server, use the hostname
  3. Status Unknown in Azure Hybrid Connection Manager
    Solution - Restart the Azure Hybrid connection manager by navigating to >> Service.msc
  4. Port Issue - Please check if the port 1433 is open in SQL DB server by telnet the SQL server IP