Deploying Azure SQL Database with ARM Templates

My Second ARM Template Challenge

Hello again! In my last article, I showed you how to deploy a Web App with ARM templates. Today I want to share something more challenging – deploying Azure SQL Database with secure parameters.

This was harder than the Web App because I needed to handle passwords securely. Let me show you what I learned!

Why Azure SQL Database?

Most web applications need a database. In my home lab, I’m building a simple product catalog app. It needs to store data somewhere. Azure SQL Database is a managed database service – Microsoft handles backups, updates, and security patches for me.

My Azure SQL ARM Template

Here is my actual code from GitHub:

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "SQLLogin": {
            "type":"string",
            "metadata":{
                "description":"The administrator user name"
            }
        },
        "SQLPassword": {
            "type":"secureString",
            "metadata":{
                "description":"The administrator password"
            }
        }
  },
  "functions": [],
  "variables": {},
  "resources": [
    {
            "type": "Microsoft.Sql/servers",
            "apiVersion": "2022-02-01-preview",
            "name": "rezasqlserverr9473",
            "location": "[resourceGroup().location]",
            "properties":{
                "administratorLogin": "[parameters('SQLLogin')]",
                "administratorLoginPassword": "[parameters('SQLPassword')]"
            }
        },
        {
               "type": "Microsoft.Sql/servers/databases",
               "apiVersion": "2022-02-01-preview",
               "name": "[format('{0}/{1}','rezasqlserverr9473','appdb')]",
               "location": "[resourceGroup().location]",
               "sku":{
                "name":"Basic",
                "tier":"Basic"
               },
               "properties":{},
                "dependsOn":[
                    "[resourceId('Microsoft.Sql/servers','rezasqlserverr9473')]"
                ]
        }
  ],
  "outputs": {}
}

Understanding Parameters – The New Part!

This template is different from my first one. Look at the parameters section:

"parameters": {
    "SQLLogin": {
        "type":"string",
        "metadata":{
            "description":"The administrator user name"
        }
    },
    "SQLPassword": {
        "type":"secureString",
        "metadata":{
            "description":"The administrator password"
        }
    }
}

What Are Parameters?

Parameters make templates flexible. Instead of hard-coding username and password in the template, I pass them during deployment.

Important difference:

“type”:”string” – normal parameter, visible in logs
“type”:”secureString” – password parameter, hidden in logs!

When I first learned this, I used “type”:”string” for passwords. That was a security mistake! Always use secureString for passwords and secrets.

Metadata Section

"metadata":{
    "description":"The administrator user name"
}

This is documentation inside the template. When someone deploys this template, they see this description. It helps others understand what this parameter is for.

Creating the SQL Server

{
    "type": "Microsoft.Sql/servers",
    "apiVersion": "2022-02-01-preview",
    "name": "rezasqlserverr9473",
    "location": "[resourceGroup().location]",
    "properties":{
        "administratorLogin": "[parameters('SQLLogin')]",
        "administratorLoginPassword": "[parameters('SQLPassword')]"
    }
}

Technical details:

type: Microsoft.Sql/servers – this creates the SQL Server (not the database yet!)
name: rezasqlserverr9473 – must be globally unique across all Azure
administratorLogin: [parameters(‘SQLLogin’)] – using parameter here!
administratorLoginPassword: [parameters(‘SQLPassword’)] – secure parameter

Why the Name Has Random Numbers?

Azure SQL Server names must be unique across ALL of Azure, not just your subscription. When I tried rezasqlserver, Azure said “name already taken”. So I added 9473 at the end.

In my next articles, I will show you how to use uniqueString() function to generate unique names automatically.

Creating the Database

{
    "type": "Microsoft.Sql/servers/databases",
    "apiVersion": "2022-02-01-preview",
    "name": "[format('{0}/{1}','rezasqlserverr9473','appdb')]",
    "location": "[resourceGroup().location]",
    "sku":{
        "name":"Basic",
        "tier":"Basic"
    },
    "properties":{},
    "dependsOn":[
        "[resourceId('Microsoft.Sql/servers','rezasqlserverr9473')]"
    ]
}

The Nested Resource Name

Look at this line carefully:

"name": "[format('{0}/{1}','rezasqlserverr9473','appdb')]"

This uses the format() function. It creates the name: rezasqlserverr9473/appdb

Why this format? The database is a child resource of the SQL Server. In ARM templates, child resources use the format: parentName/childName

{0} = first parameter = ‘rezasqlserverr9473’ (server name)
{1} = second parameter = ‘appdb’ (database name)

When I first wrote this, I tried:

"name": "rezasqlserverr9473/appdb"  // This works but format() is better

Both work, but format() is cleaner when you use variables or parameters.

SKU – Database Pricing Tier

"sku":{
    "name":"Basic",
    "tier":"Basic"
}

This is important for cost! Azure SQL has different tiers:

Basic: $5/month – good for learning and small apps
Standard: $15-300/month – for production apps
Premium: $465+/month – for high performance

I use Basic tier because I’m learning and testing. It’s cheap but has limits:

Max size: 2 GB
Limited performance
Good enough for my home lab!

The Dependency Chain

"dependsOn":[
    "[resourceId('Microsoft.Sql/servers','rezasqlserverr9473')]"
]

This is critical! The database cannot exist without the server. The dependsOn tells Azure:

Create SQL Server first
Wait until it’s ready
Then create the database

When I forgot this line, deployment failed with error: “Server does not exist”

How I Deployed This Template

I use Azure CLI with inline parameters:

# Create resource group
az group create --name sql-test-grp --location "North Europe"

# Deploy with parameters
az deployment group create \
  --resource-group sql-test-grp \
  --template-file 02-AzureSQL.json \
  --parameters SQLLogin=sqlusr SQLPassword='Azure@123'

Security note: This is only for my home lab testing! In real production, never put passwords in command line. Use parameter files or Azure Key Vault instead.

Using Parameter Files (Better Way)

For production, I create a separate parameter file:

parameters.json:

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentParameters.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "SQLLogin": {
      "value": "sqlusr"
    },
    "SQLPassword": {
      "value": "Azure@123"
    }
  }
}

Then deploy:

az deployment group create \
  --resource-group sql-test-grp \
  --template-file 02-AzureSQL.json \
  --parameters @parameters.json

The @ symbol tells Azure CLI to read from file.

Connecting to My SQL Database

After deployment, I can connect using:

Server name: rezasqlserverr9473.database.windows.net
Database name: appdb
Username: sqlusr
Password: Azure@123

I use Azure Data Studio or SQL Server Management Studio to connect and create tables.

What I Learned

Parameters make templates reusable – Same template, different values
secureString is mandatory for passwords – Security first!
Child resources use slash notation – parentName/childName
format() function is useful – Better than hardcoding strings
dependsOn prevents errors – Always set dependencies correctly
Basic SKU is cheap – Perfect for learning ($5/month)
Parameter files are more secure – Don’t put passwords in command line

My Mistakes and Fixes

Mistake 1: Used “type”:”string” for password

"SQLPassword": {
    "type":"string"  // ❌ Wrong! Password visible in logs
}

Fix: Changed to “type”:”secureString”

Mistake 2: Forgot dependsOn

// No dependsOn array

Error: “The specified server does not exist” Fix: Added dependsOn to database resource

Mistake 3: Name not unique globally

"name": "sqlserver"  // ❌ Already taken by someone else

Fix: Added random numbers: rezasqlserverr9473

Mistake 4: Wrong database name format

"name": "appdb"  // ❌ Wrong for child resource

Fix: Used format function: [format(‘{0}/{1}’,’rezasqlserverr9473′,’appdb’)]

Testing My Database

After deployment, I connected and created a simple table:

CREATE TABLE Products (
    ProductID int,
    ProductName varchar(1000),
    Quantity int
);

INSERT INTO Products VALUES (1, 'Mobile', 100);
INSERT INTO Products VALUES (2, 'Laptop', 200);

It worked! Now my Web App can connect to this database.