Skip to main content

​
Overview

This document describes how to connect a Superblocks On-Premise Agent (OPA) running on Azure Container Apps (ACA) to SQL Server on an Azure VM using a user-assigned managed identity, without storing database credentials in Superblocks.

​
Authentication flow

  1. The OPA container has a user-assigned managed identity attached.
  2. When a query runs, the OPA obtains an access token from Azure’s managed identity endpoint, scoped for SQL Server.
  3. The token is supplied to the MSSQL connector via the connection string (no SQL username or password).
  4. SQL Server validates the token using its own system-assigned identity to call Microsoft Graph.
  5. Access succeeds because the managed identity is provisioned as a database user in SQL Server.

​
What you will configure

  • Steps 1–4: One-time setup per SQL Server VM (identity, Graph permissions, Entra auth on the SQL VM resource, database user).
  • Steps 5–7: Azure Container App running the OPA (attach identity, identitySettings, execution environment allowlist).
  • Step 8: MSSQL datasource in Superblocks using the connection string.

​
Prerequisites

​
Access and permissions

  • Azure CLI installed and signed in (az login).
  • Owner or Contributor on the subscription or resource group that contains the container app.
  • A Global Administrator in Microsoft Entra ID available to grant Microsoft Graph permissions (Step 2).

​
Azure resources

  • A running SQL Server VM with TCP 1433 allowed in the network security group (and any internal firewall rules).
  • A Superblocks OPA deployed as an Azure Container App.
  • A user-assigned managed identity in Azure (use one identity per SQL Server if you want separate identities per server).

​
Tools

ToolNotes
Azure CLI (az)Required for Container Apps and identity commands
sqlcmdInstall per OS; see Step 4
PowerShell with Microsoft Graph PowerShell SDKRequired for Step 2 (Global Admin)

​
Part 1: Setup steps

​
Step 1: Enable system-assigned managed identity on the SQL VM

SQL Server needs its own identity to call Microsoft Graph when validating incoming Microsoft Entra tokens. This is separate from the user-assigned identity used by the OPA.
  1. In the Azure portal, open the virtual machine resource that runs SQL Server (not the SQL virtual machine blade only—use the VM resource).
  2. Go to Security → Identity.
  3. Open the System assigned tab.
  4. Set Status to On and save.

​
Step 2: Grant Microsoft Graph permissions

Both the VM’s system-assigned identity and the OPA’s user-assigned identity need permission to query Microsoft Graph so SQL Server can resolve and validate Entra principals. A Global Administrator should run the following PowerShell twice: once with the VM system-assigned identity’s object (principal) ID, and once with the OPA user-assigned identity’s principal ID. Find principal IDs
  • VM (system assigned): VM resource → Security → Identity → System assigned → copy Object (principal) ID.
  • User-assigned identity:
    az identity show --name <YOUR_MANAGED_IDENTITY_NAME> --resource-group <YOUR_RESOURCE_GROUP> --query principalId -o tsv
Connect-MgGraph -Scopes "AppRoleAssignment.ReadWrite.All","Application.Read.All"

$principalId = "<PRINCIPAL_ID_OF_IDENTITY_YOU_ARE_GRANTING>"
$graph = Get-MgServicePrincipal -Filter "appId eq '00000003-0000-0000-c000-000000000000'"

$roles = @("User.Read.All", "GroupMember.Read.All", "Application.Read.All")
foreach ($role in $roles) {
    $roleId = ($graph.AppRoles | Where-Object {$_.Value -eq $role}).Id
    New-MgServicePrincipalAppRoleAssignment `
        -ServicePrincipalId $principalId `
        -PrincipalId $principalId `
        -ResourceId $graph.Id `
        -AppRoleId $roleId
}
This workflow is the supported alternative when Directory Readers cannot be assigned directly; see Troubleshooting.

​
Step 3: Configure Microsoft Entra authentication on the SQL VM

This enables SQL Server to accept Microsoft Entra tokens and selects which managed identity SQL uses when validating them.
  1. In the Azure portal, open the SQL virtual machine resource (the SQL VM resource, not only the generic VM blade).
  2. Go to Security → Security configuration.
  3. Under Microsoft Entra authentication, select Enable.
  4. Set Managed identity type to User-assigned managed identity.
  5. Select the OPA’s user-assigned identity from the dropdown.
  6. Click Apply.

​
Step 4: Provision the managed identity as a database user

Install sqlcmd on the machine you will use to run the script: Then connect as a SQL administrator and create the Entra principal as a user, then grant roles (adjust roles to your least-privilege needs): 
sqlcmd -S <YOUR_SQL_SERVER_IP> -U <YOUR_SQL_ADMIN_USERNAME> -P '<YOUR_PASSWORD>' \
  -Q "USE <YOUR_DATABASE_NAME>;
       CREATE USER [<YOUR_MANAGED_IDENTITY_NAME>] FROM EXTERNAL PROVIDER;
       ALTER ROLE db_datareader ADD MEMBER [<YOUR_MANAGED_IDENTITY_NAME>];
       ALTER ROLE db_datawriter ADD MEMBER [<YOUR_MANAGED_IDENTITY_NAME>];"
CREATE USER ... FROM EXTERNAL PROVIDER maps the Entra identity to a database user so token-based logins are allowed.

​
Step 5: Attach the managed identity to the OPA container app

Portal
  1. Open the Container App → Settings → Identity → User assigned.
  2. Select + Add, choose the managed identity, and confirm.
Azure CLI 
az containerapp update --name <YOUR_CONTAINER_APP_NAME> \
  --resource-group <YOUR_RESOURCE_GROUP> \
  --user-assigned /subscriptions/<YOUR_SUBSCRIPTION_ID>/resourcegroups/<YOUR_RESOURCE_GROUP>/providers/Microsoft.ManagedIdentity/userAssignedIdentities/<YOUR_MANAGED_IDENTITY_NAME>

​
Step 6: Register the identity with identitySettings (CLI)

On Azure Container Apps Consumption plan, attaching a user-assigned identity to the app is not enough: until you apply this configuration, the managed identity sidecar will not inject IDENTITY_ENDPOINT and IDENTITY_HEADER into the workload container, so the OPA cannot acquire tokens. Use API version 2026-01-01 (earlier API versions may not support identitySettings). macOS / Linux (bash) — line continuation with \, single-quoted --body: 
az rest --method PATCH \
  --url "https://management.azure.com/subscriptions/<YOUR_SUBSCRIPTION_ID>/resourceGroups/<YOUR_RESOURCE_GROUP>/providers/Microsoft.App/containerApps/<YOUR_CONTAINER_APP_NAME>?api-version=2026-01-01" \
  --body '{"properties":{"configuration":{"identitySettings":[{"identity":"<YOUR_MANAGED_IDENTITY_RESOURCE_ID>","lifecycle":"All"}]}}}'
Windows (PowerShell or Command Prompt) — single line, single-quoted --body: 
az rest --method PATCH --url "https://management.azure.com/subscriptions/<YOUR_SUBSCRIPTION_ID>/resourceGroups/<YOUR_RESOURCE_GROUP>/providers/Microsoft.App/containerApps/<YOUR_CONTAINER_APP_NAME>?api-version=2026-01-01" --body '{"properties":{"configuration":{"identitySettings":[{"identity":"<YOUR_MANAGED_IDENTITY_RESOURCE_ID>","lifecycle":"All"}]}}}'
<YOUR_MANAGED_IDENTITY_RESOURCE_ID> is the full resource ID, for example: /subscriptions/<SUBSCRIPTION_ID>/resourcegroups/<RG>/providers/Microsoft.ManagedIdentity/userAssignedIdentities/<NAME> Verify: 
az containerapp show --name <YOUR_CONTAINER_APP_NAME> --resource-group <YOUR_RESOURCE_GROUP> --query "properties.configuration.identitySettings"
You should see an entry similar to: 
[
  {
    "identity": "/subscriptions/.../userAssignedIdentities/<YOUR_MANAGED_IDENTITY_NAME>",
    "lifecycle": "All"
  }
]

​
Step 7: Update SUPERBLOCKS_EXECUTION_ENV_INCLUSION_LIST

By default the OPA image allows a fixed set of environment variables into worker execution contexts (see the agent image defaults). Azure MSI variables IDENTITY_ENDPOINT and IDENTITY_HEADER must be explicitly allowlisted so the OPA process can read them. Append them to the existing comma-separated list without removing AWS variables if you still use AWS integrations from the same agent: 
az containerapp update --name <YOUR_CONTAINER_APP_NAME> \
  --resource-group <YOUR_RESOURCE_GROUP> \
  --set-env-vars "SUPERBLOCKS_EXECUTION_ENV_INCLUSION_LIST=AWS_DEFAULT_REGION,AWS_ROLE_ARN,AWS_WEB_IDENTITY_TOKEN_FILE,AWS_REGION,IDENTITY_ENDPOINT,IDENTITY_HEADER"
Restart the container app so replicas pick up changes:
  1. Azure portal → Container App → Application → Revisions and replicas.
  2. Stop the active revision, then Start it (or use your standard revision rollout process).

​
Step 8: Create the datasource in Superblocks

Create an Microsoft SQL Server integration using a connection string with this shape: 
Server=<YOUR_SQL_SERVER_IP>,1433;Database=<YOUR_DATABASE_NAME>;Authentication=Active Directory Integrated;Client Id=<YOUR_MANAGED_IDENTITY_CLIENT_ID>
  • Client Id is the managed identity’s client (application) ID, not the object/principal ID:
az identity show --name <YOUR_MANAGED_IDENTITY_NAME> --resource-group <YOUR_RESOURCE_GROUP> --query clientId -o tsv

​
Part 2: Adding more SQL servers

For each additional SQL Server (and optional separate user-assigned identity per server):
  1. Repeat Steps 1–4 for that server and identity.
  2. Attach the new identity to the same Container App (User assigned → + Add).
  3. Re-run the Step 6 identitySettings PATCH with all identities in the array (not only the new one).
  4. Restart the container app.
  5. Create a new Superblocks MSSQL datasource with a connection string that uses that identity’s Client Id.

​
Part 3: Common errors and troubleshooting

​
Login failed for user '<client-id>'

The connection reached SQL Server but authentication failed. Most often the managed identity is not created as a user in the target database. Fix (run as admin): 
USE <YOUR_DATABASE_NAME>;
CREATE USER [<YOUR_MANAGED_IDENTITY_NAME>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<YOUR_MANAGED_IDENTITY_NAME>];
ALTER ROLE db_datawriter ADD MEMBER [<YOUR_MANAGED_IDENTITY_NAME>];

​
Connection failure: (code undefined) (code undefined)

Usually a malformed connection string or an unsupported parameter before authentication runs. Confirm the string matches Step 8. Check Container App logs: 
az containerapp logs show --name <YOUR_CONTAINER_APP_NAME> --resource-group <YOUR_RESOURCE_GROUP> --follow

​
ManagedIdentityCredential authentication unavailable

The OPA cannot reach the MSI endpoint. Typical causes:
  • identitySettings was never applied or was cleared — re-run Step 6 and verify with az containerapp show ... --query "properties.configuration.identitySettings".
  • SUPERBLOCKS_EXECUTION_ENV_INCLUSION_LIST omits IDENTITY_ENDPOINT and IDENTITY_HEADER — fix Step 7, then restart.

​
Unknown properties identitySettings in ContainerAppContainer

The REST body targeted the wrong path. Use properties.configuration.identitySettings, not properties.template.containers[].identitySettings: 
{"properties":{"configuration":{"identitySettings":[{"identity":"<RESOURCE_ID>","lifecycle":"All"}]}}}

​
identitySettings PATCH removed other identities

Each PATCH replaces the full array. Always send every managed identity: 
{"properties":{"configuration":{"identitySettings":[
  {"identity":"<IDENTITY_1_RESOURCE_ID>","lifecycle":"All"},
  {"identity":"<IDENTITY_2_RESOURCE_ID>","lifecycle":"All"}
]}}}

​
Security configuration page is grayed out / SQL VM shows Offline

The SQL IaaS extension may still be initializing. After the VM is Running, wait 10–20 minutes, refresh the SQL virtual machine resource until status is healthy.

​
Directory Readers role cannot be assigned

Assigning Directory Readers in Entra ID requires Privileged Role Administrator or Global Administrator. If that is not available, use the Microsoft Graph application roles approach in Step 2 for both the VM system-assigned identity and the OPA user-assigned identity.