Build your Azure SQL Server & Database and access from anywhere

Table of Contents

  1. Purpose
  2. Pre-requisites
  3. Create SQL Server
    3.1 Resource Group
    3.2 Create SQL Database
    3.3 Objects created
    3.4 Test connection to SQL server
    3.5 Firewall and virtual networks
    3.5.1 Client IP address
    3.5.2 Virtual network
    3.5.3 Allow Azure services and resources to access this server
  4. Create 2nd SQL Database
  5. End of Document

1. Purpose

Purpose of this document is to explain how to build Azure SQL Server and SQL Database and access from anywhere.

2. Pre-requisites

Azure subscription

3. Create SQL Server

3.1 Resource Group

Resource group is a logical grouping of the objects. (Like VM, Storage account, NIC, Disk, public IP, SQL Server, SQL Database etc)

Login to Azure portal https://portal.azure.com => Create a resource => Resource Group

Create Resource Group: NSIT-RG01

Region: West US

Always Tag the resource. Tag is beneficial for identifying the related objects and also gives you better Azure cost calculation.

3.2 Create SQL Database

Creation of SQL Database also includes SQL Server creation. If you are building first SQL Database you need to build SQL server as well. If you are creating second SQL Database you can create new SQL Server or you can create SQL Database on existing server.

SQL Database: NSIT-DB01

SQL Server: nsit-sql-srv01

SQL Server: Size (As per your requirement)

Resource group: Select correct resource group

Location: West US

Select the SQL Server Size as per your requirement. (Basic/ Standard/ Premium)

Select Public endpoint to access Database from anywhere. Leave the Firewall Rules as it is. This will be explained later in this post.

Always Tag the resources. Tagging resources help in identifying related objects / costing on resources.

3.3 Objects created

Below objects get created. “Master” DB will be visible only if you select “Show hidden types”.

3.4 Test connection to SQL server

Once your SQL Server and SQL DB is ready, you can test connection on port 1433 from anywhere over internet.

C:\> telnet nsit-sqlsrv01.database.windows.net 1443

Telnet is working over internet since while creation of SQL Server & DB Selected network connectivity method as “Public endpoint”. This does not mean now you able to connect SQL Server/ DB. You need to configure “Firewall and virtual networks” settings.

3.5 Firewall and virtual networks

There are three opts available in Firewall and Virtual networks

3.5.1 Client IP address

Scenario A:  Connect SQL Server over Internet.

In this case you need to add client IP address from where you want to access the SQL Server/ DB. Client IP can be any public or Private IP.

In this example shown how to access SQL server from anywhere over internet (Public IP).

Issue:

Without client IP address entry. If you try to access the SQL Server using “SQL management studio”, you will get below message.

Find your Laptop/ Desktop public IP.

Add entry of your Laptop/ Desktop public IP in “Client IP address” rule in “Firewall and virtual networks” setting of SQL Server.

Here you go, now you able to connect to SQL Server/ DB over Internet.

3.5.2 Virtual network

Scenario B:  Need to connect SQL Server from Specific Azure Virtual Network (from any VM in specific VNET).

Use case is if your VM’s are in DMZ zone, which are isolated from internet and need to connect Azure SQL Server.

Require below objects:

Resource Group: NSIT-RG01

Virtual Network Name: NSIT-VNET-01 (10.1.0.0/16)

Subnet Name: NSIT-SN01 (10.1.1.0/24)

Virtual Machine Name: NSIT-VM01 (10.1.1.4/24)

SQL Server: nsit-sqlsrv01

SQL DB: NSIT-SQL01

Prerequisite – First you need to build your Azure Virtual network (VNET) and VM ready before starting this exercise. To build your Virtual network and Virtual Machine see post https://nsitautomation.in/build-your-first-azure-vm/

Once VNET (10.1.0.0/16), Subnet (10.1.1.0/24) and VM’s are ready you need to create network rule for existing Virtual network (10.1.0.0/16).

Connect SQL from Azure VM

Login to Azure VM, install SQL Mgmt studio and connect to SQL Server.

3.5.3 Allow Azure services and resources to access this server

Scenario C:  Need to connect SQL Server from any Azure services or resources (VM, WebApp etc)

You have option to ON/OFF “Allow Azure services and resources to access this server”. Once your enable this any Azure services (VM, WebApp etc) can connect to this DB server.

Note: Allow Azure services and resources to access this server (ON), means you are allowing to any Azure services to connect (including connections from the subscriptions of other customers as well).Only protection is Credentials.

4. Create 2nd SQL Database

Hear shown how to create 2nd SQL DB on existing SQL Server.

Always Tag the resource. Tag is beneficial for identifying the related objects and also gives you better Azure cost calculation.

5. End of Document

Facebook Comments

Leave a Reply

Your email address will not be published. Required fields are marked *