✨ Managed Identity - Migrate Azure services from connection string secrets to Managed Identity

[ssw-yakshaver]

Requested by: @jernejk via YakShaver.ai 🦬
cc: @jernejk, @Freego1783

Hi Team!

  🟥  Watch the video (1 min 13 sec)

  📏 SSW Rule: Do you use Azure Managed Identities?

Pain

SSW.Rewards has System Assigned Managed Identity already enabled on the App Service, and Key Vault RBAC is properly configured. However, the actual service connections (SQL, Blob Storage, Notification Hub) still use connection string secrets stored in Key Vault rather than authenticating via Managed Identity directly.

This means:

• Secrets still need to be rotated and managed
• Connection strings with embedded credentials are stored in Key Vault
• The Managed Identity setup gives false confidence — it's only used for Key Vault access, not for the services themselves

Current State (what MSI is/isn't used for)

Service	Current Auth	MSI Ready?
Key Vault	✅ MSI (RBAC role assignment)	✅ Done
Blob Storage	❌ Connection string via Key Vault (despite preferMsi: true in code)	🟡 Partially
Azure SQL (App DB)	❌ SQL credentials via Key Vault	❌ No
Azure SQL (Hangfire)	❌ SQL credentials via Key Vault	❌ No
Notification Hub	❌ Connection string via Key Vault	❌ No
Azure Maps	❌ API key via Key Vault	⚠️ May not support MSI
Graph API (email)	❌ Client secret via Key Vault	⚠️ Separate concern
Firebase	❌ Credentials JSON via Key Vault	⚠️ External service

Acceptance Criteria

SQL Server → Managed Identity Auth

• Update sql.bicep to enable Azure AD-only authentication (currently has both SQL admin + AD admin)
• Create contained database users for the App Service MSI in both databases (app + Hangfire)
• Update Infrastructure/ConfigureServices.cs to use DefaultAzureCredential with SqlConnection (via Azure.Identity + Microsoft.Data.SqlClient)
• Update Hangfire SQL connection to use MSI token-based auth
• Remove SqlConnectionString and HangfireSqlConnectionString secrets from Key Vault
• Update webapp.bicep to remove Key Vault connection string references, replace with server/database name app settings

Blob Storage → Managed Identity Auth

• Code already has preferMsi: true — verify it works with service URI instead of connection string
• Update webapp.bicep to pass Blob Storage service URI instead of connection string
• Add "Storage Blob Data Contributor" RBAC role assignment for App Service MSI on the storage account
• Remove ContentStorageConnectionString secret from Key Vault

Infrastructure (Bicep)

• Add RBAC role assignments in Bicep for SQL and Blob Storage (similar pattern to existing add-kv-role-assignment.bicep)
• Update webapp.bicep app settings to use service endpoints instead of Key Vault secret references where possible
• Keep Key Vault for secrets that genuinely can't use MSI (Firebase credentials, Graph client secret, Azure Maps key)

Testing & Verification

• Verify local development still works with connection strings (dev uses Docker SQL)
• Verify deployed app connects to SQL via MSI
• Verify Blob Storage operations work via MSI
• Verify Hangfire job processing works via MSI
• Health checks pass

Documentation

• Update README or docs with MSI configuration notes
• Document which services still require Key Vault secrets and why

⚠️ Gotchas

• SysAdmin dependency: Creating contained database users and assigning SQL AD roles likely requires elevated permissions — a SysAdmin may need to be involved
• Not all services support MSI: Firebase, Azure Maps, and Graph API client secrets will likely remain in Key Vault — that's OK
• Local dev unchanged: DefaultAzureCredential falls back to connection strings/local auth, so Docker-based local dev should still work
• Different MSI patterns per service: SQL needs Azure AD auth + contained users, Blob needs RBAC roles, each has different SDK integration

Screenshot

Figure: SSW Rewards lacks managed identity integration despite SSW rules

[jernejk]

🔍 Investigation Notes

Estimated effort: 4 story points (~1 day)

Most of the heavy lifting is in SQL MSI migration. Blob Storage is nearly there already.

---

1. SQL Server → MSI (biggest piece)

Current: sql.bicep generates a random SQL password via guid(), stores it as a Key Vault secret, and the app reads it via @Microsoft.KeyVault(SecretUri=...).

Target: Use DefaultAzureCredential to get an Azure AD token for SQL.

Code change in Infrastructure/ConfigureServices.cs:

// Before
options.UseSqlServer(configuration.GetConnectionString("DefaultConnection"), ...);

// After
var connectionString = configuration.GetConnectionString("DefaultConnection");
var connection = new SqlConnection(connectionString);

// Only add token in non-development (MSI not available locally with Docker SQL)
if (!builder.Environment.IsDevelopment())
{
    var credential = new DefaultAzureCredential();
    var token = await credential.GetTokenAsync(
        new TokenRequestContext(["https://database.windows.net/.default"]));
    connection.AccessToken = token.Token;
}

options.UseSqlServer(connection, ...);

The connection string in production would simplify to just:

Server=tcp:{server}.database.windows.net,1433;Database={db};Encrypt=True;

(No User ID or Password — the access token handles auth)

Bicep change — add SQL RBAC for MSI:

The App Service's principalId needs to be added as a contained database user. This is a manual SQL step (not Bicep-able):

-- Run against each database by a SQL AD admin
CREATE USER [app-sswrewards-api-prod] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [app-sswrewards-api-prod];
ALTER ROLE db_datawriter ADD MEMBER [app-sswrewards-api-prod];
-- For Hangfire DB, also needs:
ALTER ROLE db_owner ADD MEMBER [app-sswrewards-api-prod];

⚠️ This requires SysAdmin access — someone with Azure AD admin on the SQL Server needs to run this.

---

2. Blob Storage → MSI (quick win)

Current state is interesting: The code in ConfigureServices.cs already has preferMsi: true:

clientBuilder.AddBlobServiceClient(
    configuration["CloudBlobProviderOptions:ContentStorageConnectionString"], 
    preferMsi: true);

However, webapp.bicep passes a connection string from Key Vault, so MSI never kicks in. The fix:

1. Change the app setting to pass the service URI (e.g. https://stsswrewards.blob.core.windows.net)
2. Add "Storage Blob Data Contributor" role to App Service MSI
3. Remove the connection string secret

---

3. Services that STAY in Key Vault

These can't use MSI and that's fine:

• Firebase credentials — external Google service, needs JSON credentials
• Graph API client secret — Azure AD app registration secret (could migrate to federated credentials later, but separate concern)
• Azure Maps API key — key-based auth only
• Notification Hub — connection string based (Azure Notification Hubs MSI support is limited)

---

4. Suggested implementation order

1. ✅ Blob Storage MSI (quick win, preferMsi: true is already in code)
2. ✅ SQL Server MSI for main app DB
3. ✅ SQL Server MSI for Hangfire DB
4. ✅ Clean up unused Key Vault secrets from Bicep
5. ✅ Test everything end-to-end

NuGet packages needed

• Azure.Identity (may already be referenced)
• Microsoft.Data.SqlClient ≥ 4.0 (has built-in Azure AD token support)