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.
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,
How To Integrate Database Server With AppService Through The Private Virtual Network
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,
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.

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

  2. Connect to SQL Server Management Studio of your local machine,

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

  3. Connect to SQLVM with the help of PublicIP and SQL server credentials. we have already done this exercise in Part 2.

    How To Integrate Database Server With AppService Through The Private Virtual Network
  4. Connectivity is established with our SQL Server VM.

    How To Integrate Database Server With AppService Through The Private Virtual Network
  5. To confirm the connectivity, use the below queries to create a database, table, and insert some records.
    1. create database EmpDB  
    2. GO  
    3. use EmpDB;  
    4. GO  
    5. create table Employee  
    6. (  
    7.    EmpId int identity (1,1) not null,  
    8.    [Name] nvarchar(50) null  
    9. )  
    10. GO  
    11. insert into Employee([Name]) values('Employee1');  
    12. insert into Employee([Name]) values('Employee2');  
    13. insert into Employee([Name]) values('Employee3');  
    14. GO  
    15. select * from Employee;  
    16. GO  
    How To Integrate Database Server With AppService Through The Private Virtual Network

    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.

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

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

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

  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.

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

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

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


  5. Verify the published application using the AppService URL, once deployed.

    Copy the URL from the overview Tab of AppService “apptosqldb”

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

  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.

    How To Integrate Database Server With AppService Through The Private Virtual Network
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.

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

  2. Click on “Dissociate”

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

  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.

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

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

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

    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.

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

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.

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

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.


Similar Articles