Innofactor Blog

How to Connect Azure SQL to laaS

Written by Sarmad Jari | 29.9.2017

Azure SQL is a fantastic service, it offers many features and benefits, but many of our customers do not like the idea that Azure SQL expose its connection to the wide internet when connected to other services even if it is protected by SSL, username/password authentication and a SQL Server Firewall, and to be honest their concerns are legit.

Microsoft just released a new feature to cover the concerns with Azure SQL Database VNET Service Endpoints and currently, it’s a public preview in the following regions: West Central US, West US, West US2, and East US.

This enables customers to lock the network connection and make the Azure SQL exposed within the VNET only.

Before Microsoft released the feature, we used to approach it in a different method, so I have written about both in hope to give a better understanding.

Old traditional method:

Let’s start our scenario by defining our main components:

  • A Virtual Machin VM1
    • Virtual Network Vnet1
    • Subnet Subnet1 (10.0.0.0/24)
    • Public IP Address VM1-ip (Static IP example 123.456.789.111)
    • Network Security Group VM1-nsg
  • Azure SQL database LGSQLBD1
    • Server lgsqlsrv1

To make our connection between the VM (VM1) and the Azure SQL (LGSQLBD1) secured we must have a static public IP for our VM.

Secure Azure SQL server (lgsqlsrv1) by settings “Allow access to Azure services” to OFF so that none has access to the database server (other servers and service in Azure)

Add our VM’s Public IP address (example 123.456.789.111) to the “Client IP Address” to have only the VM (VM1) accessing the Azure SQL database server (lgsqlsrv1)

 

 

To tighten the security around the VM1, the Network Security Group VM1-nsg need to be examined (both the Inbound and the Outbound security rules).

Here are the rules for the inbound and the outbound when VM1 was created (remember to check the standard Default rules as well to have a wider understand)

 

 

The rules in red are the Default rules that comes with every new Vnet, the Default-allow-rdp and Default-allowsql came in as the default rules when we chose an (SQL Server 2016 SP1 Developer on Windows Server 2016) type Virtual Machine and chose to have the database open for internet access.

New Method:

Using the new Feature (Azure SQL Database VNET Service Endpoints) which is available in West Central US, West US2, and East US1 regions as I write this blog.

We will use the same scenario in the “Old traditional method”:

  • A Virtual Machin VM1
    • Virtual Network Vnet1
    • Subnet Subnet1 (10.0.0.0/24)
    • Public IP Address VM1-ip (for remote access, will not be used for database connectivity)
    • Network Security Group VM1-nsg
  • Azure SQL database LGSQLBD1
    • Server lgsqlsrv1

Before you add a database to a VNET you need to enable Microsoft SQL Endpoint in the VNET under “Service endpoints” otherwise you will get an error message. You can do that in an existent or while creating new VNET.

 

 

After creating the Azure SQL, under “Firewall/virtual Network” we can add the Azure SQL to one or many VNETs/Subnets under the section “Virtual networks” chose “+ Add existing”

 

 

Remember we still need to tick “OFF” for “Allow access to Azure services” to harden the security exactly like the “First traditional method”

We now can connect to the database from any VM or Service within the defined Subnet, in our case the Vnet1/Subnet1 without the need to configure each VM/Service and no need to use Public IPs as the “old traditional method”

With Azure SQL Database VNET Service Endpoints, Microsoft made Azure SQL more attractive to costumers, where you can lock the Database and make it exposed to the IaaS only.

The following diagram by Microsoft gives more information on the architecture: