jeznorton

[JezNorton]

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

# 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:

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_

[yonzhan]

Thank you for opening this issue, we will look into it.

[microsoft-github-policy-service]

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @azureSQLGitHub.

[microsoft-github-policy-service]

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @azureSQLGitHub.