Azure / Azure/azure-cli

az sql server update --assign_identity fails with unrelated "Invalid value given for parameter RetentionDays" error

Open
#32,726 12 comments 2 reactions 2 assignees Assigned to @a0x1ab View on GitHub
act-codegen-extensibility-squad Auto-Assign bug customer-reported Error Messages Service Attention SQL
Dominant language
Python
Stars
4.6k
Forks
3.5k
Avg merge
3d 2h
Merged PRs (30d)
60

Description

### Describe the bug

## Title
`az sql server update --assign_identity` fails with unrelated "Invalid value given for parameter RetentionDays" error

## Azure CLI Version
```
azure-cli 2.82.0
```

## Describe the bug
When running `az sql server update --assign_identity` to enable system-assigned managed identity on an Azure SQL Server, the command fails with an error about `RetentionDays`, which is an auditing parameter unrelated to identity assignment.

## Command name
`az sql server update`

## Steps to reproduce
```bash
# Create a new SQL Server (or use existing)
az sql server create \
--name my-sql-server \
--resource-group my-rg \
--location eastus \
--admin-user sqladmin \
--admin-password "SecurePassword123!"

# Attempt to enable managed identity
az sql server update \
--name my-sql-server \
--resource-group my-rg \
--assign_identity
```

## Expected behavior
The command should enable system-assigned managed identity on the SQL Server and return the updated server details including the `identity.principalId`.

## Actual behavior
The command fails with:
```
ERROR: (InvalidParameterValue) Invalid value given for parameter RetentionDays.
```

The `RetentionDays` parameter is for auditing configuration, not identity assignment. This appears to be internal validation leaking an unrelated error.

## Workaround
Using `--identity-type SystemAssigned` instead sometimes works:
```bash
az sql server update \
--name my-sql-server \
--resource-group my-rg \
--identity-type SystemAssigned
```

## Environment
- OS: Ubuntu 22.04 (Azure DevOps hosted agent) / Windows 11
- Shell: Bash / PowerShell
- Azure CLI: 2.82.0
- Region: UK South

## Additional context
This issue occurs both when creating a new SQL Server and when updating an existing one. The workaround with `--identity-type` is inconsistent - it works in some cases but not others.

The managed identity is required for enabling Azure AD authentication on SQL Server, specifically for `CREATE USER ... FROM EXTERNAL PROVIDER` to resolve App Service managed identities.

---

### Related command

az sql server update

### Errors

The command fails with:
```
ERROR: (InvalidParameterValue) Invalid value given for parameter RetentionDays.

Using `--identity-type SystemAssigned` instead sometimes works:
```bash
az sql server update \
--name my-sql-server \
--resource-group my-rg \
--identity-type SystemAssigned

### Issue script & Debug output

Write-Host "[OK] SQL Server created with Azure AD authentication" -ForegroundColor Green

# Enable Managed Identity on new SQL Server (REQUIRED for CREATE USER FROM EXTERNAL PROVIDER)
Write-Host "[INFO] Enabling SQL Server Managed Identity..." -ForegroundColor Cyan

# Try multiple approaches to enable MI (Azure CLI can be inconsistent)
$miEnabled = $false

# Approach 1: Use --assign_identity flag
az sql server update --name $SqlServer --resource-group $ResourceGroup --assign_identity 2>&1 | Out-Null
Start-Sleep -Seconds 5 # Give Azure time to propagate

$sqlServerIdentity = az sql server show --name $SqlServer --resource-group $ResourceGroup --query "identity.principalId" -o tsv 2>$null
if ($sqlServerIdentity) {
$miEnabled = $true
Write-Host "[OK] SQL Server MI enabled: $sqlServerIdentity" -ForegroundColor Green
} else {
Write-Host "[WARNING] First MI enable attempt returned no identity, retrying..." -ForegroundColor Yellow

# Approach 2: Retry with explicit identity type
az sql server update --name $SqlServer --resource-group $ResourceGroup --identity-type SystemAssigned 2>&1 | Out-Null
Start-Sleep -Seconds 5

$sqlServerIdentity = az sql server show --name $SqlServer --resource-group $ResourceGroup --query "identity.principalId" -o tsv 2>$null
if ($sqlServerIdentity) {
$miEnabled = $true
Write-Host "[OK] SQL Server MI enabled (retry): $sqlServerIdentity" -ForegroundColor Green
}
}

if (-not $miEnabled) {
Write-Host "[ERROR] Failed to enable SQL Server Managed Identity after retries" -ForegroundColor Red
Write-Host "[ERROR] CREATE USER FROM EXTERNAL PROVIDER will fail without MI" -ForegroundColor Red
Write-Host "[INFO] Manual fix: az sql server update --name $SqlServer --resource-group $ResourceGroup --assign_identity" -ForegroundColor Yellow
# Don't exit - let the script continue so other resources are created
}

### Expected behavior

The command should enable system-assigned managed identity on the SQL Server and return the updated server details including the `identity.principalId`.

### Environment Summary

## Environment
- OS: Ubuntu 22.04 (Azure DevOps hosted agent) / Windows 11
- Shell: Bash / PowerShell
- Azure CLI: 2.82.0
- Region: UK South

### Additional context

_No response_

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.