How To Integrate Database Server With AppService Through The Private Virtual Network

Introduction

 

In this article, I want to go through the integration of SQL Server 2019 virtual machine(database server) with AppService via the virtual network, because as AppService is a public service that is available in public, and anyone on the Internet can actually access it.

• How To Create SQL Server 2019 Virtual Machine And Use As Database Server With Minimum Cost 

Hence in normal scenarios, you might have a Web server that is hosted on a virtual machine in a virtual network and that might be exposed over the Internet and then the webserver internally communicates with the database server. 

 

Let's say that you want the AppService to interact with a database that is hosted on a virtual machine in a virtual network, but as the virtual network is an isolated network in a shell and normally when your database server is hosted on the virtual machine, that doesn't have a public IP address.

 

So let's suppose you want to alter this behavior to interact with that database server with AppService, so one way to do that is to actually expose the public IP address of this database so that you then expose it over to the Internet.

 

But this is a security risk. Instead, what you can do that you can actually go and connect the AppService onto your virtual network and integrate the database server also in the same network.

 

So let's go and see how we can accomplish this as per the below architecture diagram,

It can be achieved in the below steps,

1. Create an AppService and integrate it with a virtual Network.
2. Create a database server with PublicIP and Integrate the database server with a virtual network
3. Verify the database server connectivity via PublicIP.
4. Deploy a web application to AppService that will connect to the database server.
5. Remove the PublicIP of the database server.
6. Verified data is being retrieved via PrivateIP by the web application.

Since this is a lengthy process hence, I have already created Steps 1 and 2 in my other articles as below,

• Part 1 - How to enable virtual network integration in app service
• Part 2 - How to create SQL Server 2019 virtual machine and use as database server with minimum cost

Let’s focus on step 3 now,

 

Verify the database server connectivity via publicIP

1. As I have already created a database server virtual machine “SqlDbVM”, go to the virtual machine and copy the PublicIP of it.
   
   
   
   
2. Connect to SQL Server Management Studio of your local machine,
   
   
   
   
3. Connect to SQLVM with the help of PublicIP and SQL server credentials. we have already done this exercise in Part 2.
   
   

   
   
4. Connectivity is established with our SQL Server VM.
   
   

   
   
5. To confirm the connectivity, use the below queries to create a database, table, and insert some records.
   

   create database EmpDB
   GO
   use EmpDB;
   GO
   create table Employee
   (
      EmpId int identity (1,1) not null,
      [Name] nvarchar(50) null
   )
   GO
   insert into Employee([Name]) values('Employee1');
   insert into Employee([Name]) values('Employee2');
   insert into Employee([Name]) values('Employee3');
   GO
   select * from Employee;
   GO

   
   Note
   This connectivity is via public internet which is not recommended, hence we will dissociate the PublicIP of the virtual machine once we are done and use the VM via virtual network as private. Now move to Step 4.

Deploy a web application to AppService that will connect to the database server

1. I have created a simple MVC application to connect to the database and retrieve the employee records from Employee Table.
   
   

   
   
   
2. Update the connection string as below, use connection details of “SqlDbVM”.
   
   

   
   
   
3. After updating the connection details, simply run the application and we can see the employee details are displayed. These details are coming from our database server “SqlDbVM” now.
   
   

   
   
   
4. Now publish the web application using Visual Studio, to the already created AppService in Step 1.
   
   

   
   
   
   
5. Verify the published application using the AppService URL, once deployed.
   
   Copy the URL from the overview Tab of AppService “apptosqldb”
   
   

   
   
6. Browse the copied URL in chrome, we can see the same results as we saw in the local environment. Great! connectivity is working fine in the Azure environment as well.
   
   

So we have completed step 3 and step 4 and everything is working well till now but still, database connectivity is public via PublicIP of the database server, which is a security risk. Hence, we will remove the PublicIP of the database server now and then will verify the connectivity in next step 5.

 

Remove the PublicIP of the Database server

1. Go to resource group “demo_rg” and click on “sqldbvm-ip” resource.
   
   

   
   
   
2. Click on “Dissociate”
   
   

   
   
   
3. Click on “yes”, It will confirm the action to dissociate the public IP from the network interface and public IP address will be lost.
   
   

   
   
   
4. Wait for 2-3 min until settings are saved.
   
   

   
   Go to the “Networking” section of “sqldbvm” and we can see that there is no public IP associated with the machine now, only private IP is showing.
   
   

 

Verify data is being retrieved via private IP by the web application

1. Now again try the browse the application, it should still work meaning the connectivity between AppService and database is private and secure now.
   
   The database server cannot be accessed via its public IP from anywhere.
   
   

Conclusion

 

We have successfully implemented the architecture that how we can secure the connectivity between web application and database by using virtual network service integration between AppService and database server. We have also explored the implementation of a database server at minimum cost.