How do we connect from Azure Cloud App Service to an on-prem MS SQL Server DB?

Question

We are looking for some guidance around accessing an on-prem MS SQL Server DB from the Cloud App Service.

Is there additional SW required?

Can provide an example connection string?

Is this task worth the effort if it is temporary?

Any guidance would be greatly appreciated.

Thanks!

Answer 1

Hi @Tomaine, Mark when you say "temporary" can you give a time duration? If you're referring to a few months to years and it is necessary for your scenario then yes, the effort is worth it.

To start off, here are the prerequisites:

• Make sure your on-prem SQL Server instance is accessible from the internet. This might involve configuring a firewall, VPN, or other network connectivity options. We recommend Azure Hybrid Connection ( more details below)
• Create an Azure App Service instance.
• Install the appropriate SQL Server driver for your programming language within the App Service environment. This can be done using the WEBSITE_RUN_FROM_PACKAGE setting or by installing the driver during deployment.

To connect your Azure App Service to an on-premises SQL Server, you typically need to set up a Hybrid Connection or use Azure VPN Gateway. Here’s a brief overview of each:

• Hybrid Connection is a feature of Azure App Service that allows you to securely connect to on-premises resources. It requires the installation of the Hybrid Connection Manager on your on-premises network.
• Azure VPN Gateway creates a secure site-to-site VPN connection between your on-premises network and Azure.

Here's an example of Connection string:

Server=your-vpn-dns-name,1433;Database=your-db;User Id=your-username;Password=your-password;Encrypt=True;TrustServerCertificate=True;

hope that helps. please let us know if you have other questions.

-Grace