Azure CLI SQL bits

Status: Reviewed Stack: PowerShell → SQL / stored procs → Blazor Depends on: SQL-CLI-01/03/04 for query/deploy into Azure SQL after infra is ready Goal: Use az sql (and a few adjacent Azure CLI commands) from Windows/PowerShell to provision, firewall, Entra admin, and inspect Azure SQL in ways that feed Rick’s pipelines — without turning this into a full Azure encyclopedia.

1. When to use az sql vs Portal vs ARM/Bicep vs Az.Sql

Rick’s loop: AD/Windows/PowerShell → SQL stored procs → Blazor. az sql is the control-plane CLI for Azure SQL infra (logical server, DB, firewall, Entra admin). It does not replace sqlcmd, Invoke-Sqlcmd, or sqlpackage.

Rule of thumb

• Infra ready for pipelines → az sql (or Bicep if the env is checked into Git).
• Talk to the engine → sqlcmd / go-sqlcmd / Invoke-Sqlcmd (SQL-CLI-01, SQL-CLI-03).
• Deploy schema → sqlpackage (SQL-CLI-04).
• Az.Sql vs az → same ARM surface; pick one for a given script. Mixing both in one pipeline is noise unless you need a cmdlet that CLI lacks.

2. Install / login (Windows → jump box)

Install

az version
az upgrade          # keep current; sql surface changes with CLI releases

az is a Python CLI that works from PowerShell and cmd. Prefer calling it from PowerShell so you can pipe -o json | ConvertFrom-Json.

Login patterns Rick might use

az account show -o table
az account set -s "<subscriptionIdOrName>"
az configure --defaults group=rg-sql-lab location=eastus sql-server=sql-lab-01   # optional shortcuts

RBAC reminder: creating servers/DBs needs Contributor (or SQL-specific roles) on the RG. Setting Entra admin / Entra-only auth needs rights that can manage SQL + read directory (often SQL Security Manager + Directory Reader patterns — confirm with your tenant’s role model).

3. Scope map (what lives where)

Prioritize logical server + single database — the common Blazor app shape. MI is mentioned only so you do not confuse command groups.

Mental model

Subscription → Resource group → Logical server (firewall + Entra admin)
                                    └─ Database(s)  ← Blazor connection string points here

Hostname for tools: <server>.database.windows.net — but az sql flags take the short server name, not the FQDN.

4. Practical command cheat sheet

Variables used below (PowerShell):

$rg = "rg-sql-lab"
$loc = "eastus"
$server = "sql-lab-01"          # globally unique, lowercase
$db = "AppDb"
$admin = "sqladmin"             # SQL auth admin (skip if Entra-only from day one)

Server

# Create logical server (SQL auth admin — store password in secret store, not transcript)
az sql server create -g $rg -n $server -l $loc `
  --admin-user $admin --admin-password $dnv:SQL_ADMIN_PASSWORD `
  --enable-public-network true --minimal-tls-version 1.2

az sql server list -g $rg -o table
az sql server show -g $rg -n $server -o jsonc
az sql server delete -g $rg -n $server --yes   # destroys server + DBs

Useful create flags: --enable-ad-only-auth, --assign-identity, --enable-public-network false (forces private-path designs).

Database

# DTU-style
az sql db create -g $rg -s $server -n $db --service-objective S0

# vCore General Purpose Gen5, 2 vCores
az sql db create -g $rg -s $server -n $db `
  --edition GeneralPurpose --family Gen5 --capacity 2

# Serverless (common for labs / low Blazor traffic)
az sql db create -g $rg -s $server -n $db `
  -e GeneralPurpose -f Gen5 -c 2 --compute-model Serverless --auto-pause-delay 60

# Named objective shorthand
az sql db create -g $rg -s $server -n $db --service-objective GP_S_Gen5_2

az sql db list -g $rg -s $server -o table
az sql db show -g $rg -s $server -n $db -o jsonc
az sql db list-editions -l $loc -a -o table   # see what your sub can buy

# Scale / retier
az sql db update -g $rg -s $server -n $db --edition GeneralPurpose --family Gen5 --capacity 4
az sql db update -g $rg -s $server -n $db --service-objective S2 --max-size 250GB

Firewall

az sql server firewall-rule create -g $rg -s $server -n AllowClient `
  --start-ip-address 203.0.113.10 --end-ip-address 203.0.113.10

az sql server firewall-rule list -g $rg -s $server -o table
az sql server firewall-rule show -g $rg -s $server -n AllowClient
az sql server firewall-rule delete -g $rg -s $server -n AllowClient --yes

# Allow Azure services (portal toggle) — start=end=0.0.0.0
az sql server firewall-rule create -g $rg -s $server -n AllowAllWindowsAzureIps `
  --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

Microsoft Entra admin

# Display name + object id of user OR group (group preferred for ops team)
az sql server ad-admin create -g $rg -s $server `
  --display-name "SQL-Admins" --object-id "<entra-object-guid>"

az sql server ad-admin list -g $rg -s $server -o table
az sql server ad-admin update -g $rg -s $server `
  --display-name "SQL-Admins" --object-id "<new-guid>"
az sql server ad-admin delete -g $rg -s $server

# Entra-only (kills SQL auth) — set admin first
az sql server ad-only-auth enable -g $rg -n $server
az sql server ad-only-auth get -g $rg -n $server
az sql server ad-only-auth disable -g $rg -n $server

Connection strings (templates — secrets are placeholders)

# Prints a template with <username>/<password> tokens — not live secrets from Azure
az sql db show-connection-string -s $server -n $db -c ado.net
az sql db show-connection-string -s $server -n $db -c ado.net --auth-type ADIntegrated
az sql db show-connection-string -s $server -n $db -c sqlcmd

Clients: ado.net, jdbc, odbc, php, php_pdo, sqlcmd. Auth types: SqlPassword (default), ADPassword, ADIntegrated.

Do not commit the output. Prefer Key Vault references / User Secrets for Blazor ConnectionStrings.

Auditing (lean)

az sql db audit-policy show -g $rg -s $server -n $db
# update needs storage / workspace targets — wire when compliance requires; skip for lab bootstrap

5. PowerShell orchestration

Capture JSON, check exit codes

$ErrorActionPreference = "Stop"

$serverObj = az sql server show -g $rg -n $server -o json | ConvertFrom-Json
if ($LASTEXITCODE -ne 0) { throw "az sql server show failed ($LASTEXITCODE)" }

$fqdn = $serverObj.fullyQualifiedDomainName   # sql-lab-01.database.windows.net
$state = $serverObj.state

$dbs = az sql db list -g $rg -s $server -o json | ConvertFrom-Json
$dbs | Where-Object name -ne "master" | Select-Object name, status, earliestRestoreDate, currentSku

JMESPath keeps payloads small:

az sql db list -g $rg -s $server --query "[?name!='master'].{name:name,sku:currentSku.name,status:status}" -o table

Idempotency patterns

az sql * create fails if the resource exists. Pattern o:

function Ensure-SqlFirewallRule {
  param($Rg, $Server, $Name, $Ip)
  $existing = az sql server firewall-rule show -g $Rg -s $Server -n $Name -o json 2>$null
  if ($LASTEXITCODE -eq 0 -and $existing) {
    az sql server firewall-rule update -g $Rg -s $Server -n $Name `
      --start-ip-address $Ip --end-ip-address $Ip | Out-Null
  } else {
    az sql server firewall-rule create -g $Rg -s $Server -n $Name `
      --start-ip-address $Ip --end-ip-address $Ip | Out-Null
  }
  if ($LASTEXITCODE -ne 0) { throw "firewall ensure failed" }
}

function Test-AzSqlDbExists {
  param($Rg, $Server, $Name)
  az sql db show -g $Rg -s $Server -n $Name -o none 2>$null
  return ($LASTEXITCODE -eq 0)
}

if (-not (Test-AzSqlDbExists $rg $server $db)) {
  az sql db create -g $rg -s $server -n $db --service-objective GP_S_Gen5_2
  if ($LASTEXITCODE -ne 0) { throw "db create failed" }
}

Hand off to sqlcmd / sqlpackage

Once firewall + Entra admin (or SQL admin) are in place:

az sql stops at the door; the other three walk inside.

6. Auth & networking gotchas

7. Copy-paste recipes

Assume $rg, $server, $db are set. Run from elevated-enough PowerShell where az is on PATH.

R1 — Login + subscription

az login
# or: az login --use-device-code
# or: az login --identity
az account set -s "<subscriptionIdOrName>"
az account show -o table

R2 — Firewall for current public IP

$ip = (Invoke-RestMethod -Uri "https://api.ipify.org")
Ensure-SqlFirewallRule -Rg $rg -Server $server -Name "Client-$env:COMPUTERNAME" -Ip $ip
# or inline:
az sql server firewall-rule create -g $rg -s $server -n "Client-$env:COMPUTERNAME" `
  --start-ip-address $ip --end-ip-address $ip
az sql server firewall-rule list -g $rg -s $server -o table

R3 — Create server + single DB (lab)

az group create -n $rg -l $loc
az sql server create -g $rg -n $server -l $loc `
  --admin-user $admin --admin-password $env:SQL_ADMIN_PASSWORD `
  --minimal-tls-version 1.2
az sql db create -g $rg -s $server -n $db `
  --edition GeneralPurpose --family Gen5 --capacity 2 --compute-model Serverless --auto-pause-delay 60

R4 — Set Microsoft Entra admin (group preferred)

$groupName = "SQL-Admins"
$oid = az ad group show --group $groupName --query id -o tsv
if ($LASTEXITCODE -ne 0 -or [string]::IsNullOrWhiteSpace($oid)) { throw "group not found / Graph rights?" }

az sql server ad-admin create -g $rg -s $server --display-name $groupName --object-id $oid
az sql server ad-admin list -g $rg -s $server -o table

R5 — Show connection info (no live secrets)

$info = az sql server show -g $rg -n $server -o json | ConvertFrom-Json
$dbInfo = az sql db show -g $rg -s $server -n $db -o json | ConvertFrom-Json
[pscustomobject]@{
  Fqdn     = $info.fullyQualifiedDomainName
  Db       = $dbInfo.name
  Status   = $dbInfo.status
  Sku      = $dbInfo.currentSku.name
  Collation= $dbInfo.collation
  EarliestRestore = $dbInfo.earliestRestoreDate
}
az sql db show-connection-string -s $server -n $db -c ado.net --auth-type ADIntegrated

R6 — PowerShell loop: list DBs across servers in an RG

$servers = az sql server list -g $rg -o json | ConvertFrom-Json
foreach ($s in $servers) {
  Write-Host "`n=== $($s.name) ($($s.fullyQualifiedDomainName)) ===" -ForegroundColor Cyan
  az sql db list -g $rg -s $s.name --query "[].{name:name,status:status,sku:currentSku.name}" -o table
  if ($LASTEXITCODE -ne 0) { Write-Warning "list failed for $($s.name)" }
}

R7 — Open path for go-sqlcmd -G (Entra)

# 1) Firewall (R2)  2) Entra admin (R4)  3) then:
$fqdn = az sql server show -g $rg -n $server --query fullyQualifiedDomainName -o tsv

# Interactive Entra (go-sqlcmd) — see SQL-CLI-01 for auth-method details
sqlcmd -S $fqdn -d $db -G -Q "SELECT SUSER_SNAME() AS me, DB_NAME() AS db;"

After first Entra admin login, create the Blazor app identity inside the DB (still T-SQL via sqlcmd — not az sql):

CREATE USER [app-registration-or-mi-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [app-registration-or-mi-name];
-- add db_datawriter / exec on procs as needed

R8 — Scale then hand off to sqlpackage

az sql db update -g $rg -s $server -n $db --service-objective GP_Gen5_2
if ($LASTEXITCODE -ne 0) { throw "scale failed" }
$fqdn = az sql server show -g $rg -n $server --query fullyQualifiedDomainName -o tsv
# Schema deploy — SQL-CLI-04
# sqlpackage /Action:Publish /SourceFile:.\AppDb.dacpac /TargetServerName:$fqdn /TargetDatabaseName:$db ...

8. What NOT to do with az sql

Pipeline shape that matches this stack

az sql          → server, db, firewall, Entra admin
sqlpackage      → schema (DACPAC)                    [SQL-CLI-04]
sqlcmd / -G     → CREATE USER FROM EXTERNAL PROVIDER, smoke procs  [SQL-CLI-01]
Invoke-Sqlcmd   → token-based PS automation / tests  [SQL-CLI-03]
Blazor          → connection string / MI to AppDb

References (Learn, azure-cli-latest / azuresql)

• az sql server
• az sql db
• az sql server firewall-rule
• az sql server ad-admin
• Create single DB + firewall (CLI sample)
• IP firewall rules
• Configure Microsoft Entra authentication
• Microsoft Entra-only authentication
• Install Azure CLI on Windows
• az login — service principal / managed identity

Cross-links: SQL-CLI-01 · SQL-CLI-03 · SQL-CLI-04