Azure SQL firewall rules - Mini Project: Build & Apply

Azure SQL Firewall Rules Setup

📖 Scenario: You are setting up an Azure SQL Database for a small company. To keep the database secure, you need to configure firewall rules that allow only specific IP addresses to access the database server.

🎯 Goal: Build an Azure Resource Manager (ARM) template snippet that defines an Azure SQL Server and configures firewall rules to allow access from two specific IP addresses.

📋 What You'll Learn

Create an Azure SQL Server resource with a specified name and location.

Add two firewall rules to the SQL Server to allow access from two exact IP addresses.

Use exact resource names and IP addresses as specified.

Ensure the ARM template JSON is valid and deployable.

💡 Why This Matters

🌍 Real World

Configuring firewall rules is essential to secure Azure SQL Databases by allowing only trusted IP addresses to connect.

💼 Career

Cloud engineers and infrastructure specialists often write ARM templates to automate secure deployment of Azure SQL resources.

1

Create Azure SQL Server resource

Create an Azure SQL Server resource in the ARM template with the name mySqlServer and location eastus. Use the resource type Microsoft.Sql/servers and API version 2021-02-01-preview. Set the administrator login to sqladmin and administrator login password to StrongP@ssw0rd!.

Azure

{
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "mySqlServer",
      "location": "eastus",
      "properties": {
        "administratorLogin": "sqladmin",
        "administratorLoginPassword": "StrongP@ssw0rd!"
      }
    }
  ]
}
# Your code here

Hint

Define a resource with type Microsoft.Sql/servers and set the required properties exactly as specified.

2

Add first firewall rule configuration

Add a firewall rule resource to the ARM template to allow access from IP address 192.168.1.10. The resource type should be Microsoft.Sql/servers/firewallRules with API version 2021-02-01-preview. Name the firewall rule AllowIP1 and set both startIpAddress and endIpAddress to 192.168.1.10. The firewall rule resource must be a child of the SQL Server resource mySqlServer.

Azure

{
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "mySqlServer",
      "location": "eastus",
      "properties": {
        "administratorLogin": "sqladmin",
        "administratorLoginPassword": "StrongP@ssw0rd!"
      }
    },
    {
      "type": "Microsoft.Sql/servers/firewallRules",
      "apiVersion": "2021-02-01-preview",
      "name": "mySqlServer/AllowIP1",
      "properties": {
        "startIpAddress": "192.168.1.10",
        "endIpAddress": "192.168.1.10"
      }
    }
  ]
}
# Your code here

Hint

Firewall rule resource name must be in the format mySqlServer/AllowIP1 to link it as a child resource.

3

Add second firewall rule configuration

Add another firewall rule resource to the ARM template to allow access from IP address 203.0.113.5. Use the same resource type and API version as before. Name this firewall rule AllowIP2 and set both startIpAddress and endIpAddress to 203.0.113.5. This resource must also be a child of the SQL Server resource mySqlServer.

Azure

{
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "mySqlServer",
      "location": "eastus",
      "properties": {
        "administratorLogin": "sqladmin",
        "administratorLoginPassword": "StrongP@ssw0rd!"
      }
    },
    {
      "type": "Microsoft.Sql/servers/firewallRules",
      "apiVersion": "2021-02-01-preview",
      "name": "mySqlServer/AllowIP1",
      "properties": {
        "startIpAddress": "192.168.1.10",
        "endIpAddress": "192.168.1.10"
      }
    },
    {
      "type": "Microsoft.Sql/servers/firewallRules",
      "apiVersion": "2021-02-01-preview",
      "name": "mySqlServer/AllowIP2",
      "properties": {
        "startIpAddress": "203.0.113.5",
        "endIpAddress": "203.0.113.5"
      }
    }
  ]
}
# Your code here

Hint

Remember to use the full resource name format mySqlServer/AllowIP2 for the firewall rule.

4

Complete ARM template with all resources

Ensure the ARM template JSON includes the Azure SQL Server resource mySqlServer and both firewall rules named AllowIP1 and AllowIP2 with their respective IP addresses. The template must be valid JSON and ready for deployment.

Azure

{
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "mySqlServer",
      "location": "eastus",
      "properties": {
        "administratorLogin": "sqladmin",
        "administratorLoginPassword": "StrongP@ssw0rd!"
      }
    },
    {
      "type": "Microsoft.Sql/servers/firewallRules",
      "apiVersion": "2021-02-01-preview",
      "name": "mySqlServer/AllowIP1",
      "properties": {
        "startIpAddress": "192.168.1.10",
        "endIpAddress": "192.168.1.10"
      }
    },
    {
      "type": "Microsoft.Sql/servers/firewallRules",
      "apiVersion": "2021-02-01-preview",
      "name": "mySqlServer/AllowIP2",
      "properties": {
        "startIpAddress": "203.0.113.5",
        "endIpAddress": "203.0.113.5"
      }
    }
  ]
}
# Verify the full template is complete and valid

Hint

Make sure the ARM template includes all three resources with correct names and IP addresses.

Practice

1. What is the main purpose of Azure SQL firewall rules?

easy

A. To control which IP addresses can access the database

B. To encrypt data stored in the database

C. To backup the database automatically

D. To monitor database performance

Solution

1. Step 1: Understand firewall rules function

   Azure SQL firewall rules specify which IP addresses are allowed to connect to the database.

2. Step 2: Compare with other options

   Encryption, backup, and monitoring are different features not controlled by firewall rules.

3. Final Answer:

   To control which IP addresses can access the database -> Option A

4. Quick Check:

   Firewall rules = control IP access [OK]

Hint: Firewall rules control IP access, not encryption or backup [OK]

Common Mistakes:

• Confusing firewall rules with encryption settings
• Thinking firewall rules manage backups
• Assuming firewall rules monitor performance

2. Which of the following is the correct way to define a firewall rule in Azure SQL?

easy

A. Specify a username and password

B. Specify only a single IP address without a range

C. Specify a start IP and an end IP address range

D. Specify a database name and region

Solution

1. Step 1: Recall firewall rule structure

   Azure SQL firewall rules require a start IP and an end IP to define the allowed range.

2. Step 2: Eliminate incorrect options

   Single IP alone is not enough; username/password and database name/region are unrelated to firewall rules.

3. Final Answer:

   Specify a start IP and an end IP address range -> Option C

4. Quick Check:

   Firewall rule = start IP + end IP [OK]

Hint: Firewall rules need IP ranges, not usernames or database names [OK]

Common Mistakes:

• Trying to use usernames instead of IP addresses
• Defining only one IP without a range
• Confusing firewall rules with database settings

3. Given a firewall rule with start IP 192.168.1.10 and end IP 192.168.1.20, which IP address can connect?

medium

A. 192.168.1.9

B. 192.168.2.10

C. 192.168.1.21

D. 192.168.1.15

Solution

1. Step 1: Understand IP range inclusion

   The firewall rule allows IPs from 192.168.1.10 up to 192.168.1.20 inclusive.

2. Step 2: Check each IP against the range

   192.168.1.9 is below start IP, 192.168.1.15 is inside range, 192.168.1.21 and 192.168.2.10 are outside the range.

3. Final Answer:

   192.168.1.15 -> Option D

4. Quick Check:

   IP inside range = allowed [OK]

Hint: Check if IP is between start and end IP inclusive [OK]

Common Mistakes:

• Choosing IP just outside the range
• Ignoring inclusive range boundaries
• Confusing similar IP addresses

4. You created a firewall rule with start IP 10.0.0.5 and end IP 10.0.0.3. What is the problem?

medium

A. The rule allows all IPs by mistake

B. Start IP is greater than end IP, so the rule is invalid

C. The IP addresses are in different subnets

D. There is no problem; the rule is valid

Solution

1. Step 1: Check IP range order

   The start IP must be less than or equal to the end IP for a valid range.

2. Step 2: Identify invalid range

   Here, 10.0.0.5 is greater than 10.0.0.3, making the range invalid.

3. Final Answer:

   Start IP is greater than end IP, so the rule is invalid -> Option B

4. Quick Check:

   Start IP ≤ End IP for valid rule [OK]

Hint: Start IP must be less or equal to end IP [OK]

Common Mistakes:

• Ignoring IP order in the range
• Assuming different subnets cause errors
• Thinking invalid range allows all IPs

5. You want to allow access only from your office IP 203.0.113.25 and your home IP 198.51.100.10. How should you configure Azure SQL firewall rules?

hard

A. Create two separate firewall rules, each with start and end IP set to one of the IPs

B. Create one firewall rule with start IP 198.51.100.10 and end IP 203.0.113.25

C. Create one firewall rule with start IP 0.0.0.0 and end IP 255.255.255.255

D. Create a firewall rule with start IP 198.51.100.10 and end IP 198.51.100.10 only

Solution

1. Step 1: Understand IP range rules

   Firewall rules allow ranges of IPs; to allow two separate IPs, create two rules each with start and end IP the same.

2. Step 2: Evaluate options

   Create two separate firewall rules, each with start and end IP set to one of the IPs correctly creates two rules for each IP. Create one firewall rule with start IP 198.51.100.10 and end IP 203.0.113.25 creates a large range including unwanted IPs. Create one firewall rule with start IP 0.0.0.0 and end IP 255.255.255.255 allows all IPs, which is insecure. Create a firewall rule with start IP 198.51.100.10 and end IP 198.51.100.10 only allows only one IP, missing the other.

3. Final Answer:

   Create two separate firewall rules, each with start and end IP set to one of the IPs -> Option A

4. Quick Check:

   Separate IPs need separate rules [OK]

Hint: Use separate rules for separate IPs, same start and end IP [OK]

Common Mistakes:

• Trying to combine non-contiguous IPs in one rule
• Allowing all IPs by mistake
• Creating only one rule for multiple IPs