> ## Documentation Index > Fetch the complete documentation index at: https://docs.superblocks.com/llms.txt > Use this file to discover all available pages before exploring further. # Azure managed identity authentication to SQL Server > Connect the Superblocks On-Premise Agent on Azure Container Apps to SQL Server on an Azure VM using a user-assigned managed identity. export const Alert = ({type, title, children}) => { const getIcon = () => { switch (type) { case 'info': return "data:image/svg+xml,%3Csvg xmlns='http://www.w3.org/2000/svg' width='20' height='20' viewBox='0 0 20 20' fill='none'%3E%3Cpath d='M10 0C4.477 0 0 4.477 0 10s4.477 10 10 10 10-4.477 10-10S15.523 0 10 0zm0 15c-.552 0-1-.448-1-1s.448-1 1-1 1 .448 1 1-.448 1-1 1zm1-3H9V6h2v6z' fill='%230099FF'/%3E%3C/svg%3E"; case 'success': return "data:image/svg+xml,%3Csvg width='20' height='20' viewBox='0 0 20 20' fill='none' xmlns='http://www.w3.org/2000/svg'%3E%3Cpath fill-rule='evenodd' clip-rule='evenodd' d='M10 0C4.477 0 0 4.477 0 10s4.477 10 10 10 10-4.477 10-10S15.523 0 10 0zm4.293 6.293L9 11.586 5.707 8.293c-.391-.391-1.024-.391-1.414 0s-.391 1.024 0 1.414l4 4c.391.391 1.024.391 1.414 0l6-6c.391-.391.391-1.024 0-1.414s-1.024-.391-1.414 0z' fill='%230CC26D'/%3E%3C/svg%3E"; case 'warning': return "data:image/svg+xml;charset=utf-8;base64,PHN2ZyB4bWxucz0naHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmcnIHhtbDpzcGFjZT0ncHJlc2VydmUnIHdpZHRoPScxMDgwJyBoZWlnaHQ9JzEwODAnPjxyZWN0IHdpZHRoPScxMDAlJyBoZWlnaHQ9JzEwMCUnIGZpbGw9J3RyYW5zcGFyZW50Jy8+PHBhdGggZD0nTTEzLjc5NCAxMC43NSA4LjMgMS4yNWExLjUgMS41IDAgMCAwLTIuNiAwbC01LjQ5NCA5LjVBMS40OTQgMS40OTQgMCAwIDAgMS41IDEzaDExYTEuNDkzIDEuNDkzIDAgMCAwIDEuMjk0LTIuMjVNNi41IDUuNWEuNS41IDAgMCAxIDEgMFY4YS41LjUgMCAwIDEtMSAwek03IDExYS43NS43NSAwIDEgMSAwLTEuNS43NS43NSAwIDAgMSAwIDEuNScgc3R5bGU9J3N0cm9rZTpub25lO3N0cm9rZS13aWR0aDoxO3N0cm9rZS1kYXNoYXJyYXk6bm9uZTtzdHJva2UtbGluZWNhcDpidXR0O3N0cm9rZS1kYXNob2Zmc2V0OjA7c3Ryb2tlLWxpbmVqb2luOm1pdGVyO3N0cm9rZS1taXRlcmxpbWl0OjQ7ZmlsbDojZmY5ZjM1O2ZpbGwtcnVsZTpub256ZXJvO29wYWNpdHk6MScgdHJhbnNmb3JtPSd0cmFuc2xhdGUoLjAyIDE5LjMwNSlzY2FsZSg3Ny4xNCknLz48L3N2Zz4="; case 'danger': return "data:image/svg+xml,%3Csvg width='20' height='20' viewBox='0 0 20 20' fill='none' xmlns='http://www.w3.org/2000/svg'%3E%3Cpath d='M10 0C4.477 0 0 4.477 0 10s4.477 10 10 10 10-4.477 10-10S15.523 0 10 0zm5.707 4.293L10 9.586 4.293 4.293c-.391-.391-1.024-.391-1.414 0s-.391 1.024 0 1.414L8.586 11l-5.707 5.293c-.391.391-.391 1.024 0 1.414s1.024.391 1.414 0L10 12.414l5.707 5.293c.391.391 1.024.391 1.414 0s.391-1.024 0-1.414L11.414 11l5.707-5.293c.391-.391.391-1.024 0-1.414s-1.024-.391-1.414 0z' fill='%23F45252'/%3E%3C/svg%3E"; case 'note': return "data:image/svg+xml,%3Csvg xmlns='http://www.w3.org/2000/svg' width='20' height='20' viewBox='0 0 20 20' fill='none'%3E%3Cpath d='M10 0C4.477 0 0 4.477 0 10s4.477 10 10 10 10-4.477 10-10S15.523 0 10 0zm0 15c-.552 0-1-.448-1-1s.448-1 1-1 1 .448 1 1-.448 1-1 1zm1-3H9V6h2v6z' fill='%230099FF'/%3E%3C/svg%3E"; default: return ""; } }; return
{title &&
{title}
}
{children}
; }; This guide applies to the Superblocks Hybrid or Cloud-Prem (On-Premise Agent) deployment on Azure Container Apps connecting to SQL Server on an Azure VM. It does not apply to Superblocks Cloud executing queries from Superblocks-hosted infrastructure. ## 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](https://learn.microsoft.com/en-us/cli/azure/install-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](#step-2-grant-microsoft-graph-permissions)). ### 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 | Tool | Notes | | ------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------- | | [Azure CLI](https://learn.microsoft.com/en-us/cli/azure/install-azure-cli) (`az`) | Required for Container Apps and identity commands | | [`sqlcmd`](https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility) | Install per OS; see [Step 4](#step-4-provision-the-managed-identity-as-a-database-user) | | PowerShell with [Microsoft Graph PowerShell SDK](https://learn.microsoft.com/en-us/powershell/microsoftgraph/get-started) | Required 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](https://portal.azure.com), 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 --resource-group --query principalId -o tsv` ```powershell theme={null} Connect-MgGraph -Scopes "AppRoleAssignment.ReadWrite.All","Application.Read.All" $principalId = "" $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 } ``` If the Global Admin cannot find the managed identity under **Enterprise applications** by name, search by **principal ID**. Set the **Application type** filter to **All applications**. This workflow is the supported alternative when **Directory Readers** cannot be assigned directly; see [Troubleshooting](#directory-readers-role-cannot-be-assigned). ### 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**. If the portal warns that the identity lacks permissions, Graph assignments from Step 2 may not have replicated yet. Wait a few minutes, refresh, and try **Apply** again. ### Step 4: Provision the managed identity as a database user Install `sqlcmd` on the machine you will use to run the script: * **macOS**: `brew install sqlcmd` * **Windows**: Download and install from Microsoft’s [sqlcmd utility](https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility) documentation. * **Linux**: Follow [Install the SQL Server command-line tools on Linux](https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools). Then connect as a SQL administrator and create the Entra principal as a user, then grant roles (adjust roles to your least-privilege needs): ```bash theme={null} sqlcmd -S -U -P '' \ -Q "USE ; CREATE USER [] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER []; ALTER ROLE db_datawriter ADD MEMBER [];" ``` `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** ```bash theme={null} az containerapp update --name \ --resource-group \ --user-assigned /subscriptions//resourcegroups//providers/Microsoft.ManagedIdentity/userAssignedIdentities/ ``` ### 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`). **`az rest` syntax depends on your shell.** Bash on macOS and Linux supports line continuation with a trailing backslash (`\`). **PowerShell** and **Command Prompt** on Windows do not use that pattern for Azure CLI—run the command on **one line**, still with the JSON body wrapped in **single quotes** as shown below. **macOS / Linux (bash)** — line continuation with `\`, single-quoted `--body`: ```bash theme={null} az rest --method PATCH \ --url "https://management.azure.com/subscriptions//resourceGroups//providers/Microsoft.App/containerApps/?api-version=2026-01-01" \ --body '{"properties":{"configuration":{"identitySettings":[{"identity":"","lifecycle":"All"}]}}}' ``` **Windows (PowerShell or Command Prompt)** — single line, single-quoted `--body`: ```text theme={null} az rest --method PATCH --url "https://management.azure.com/subscriptions//resourceGroups//providers/Microsoft.App/containerApps/?api-version=2026-01-01" --body '{"properties":{"configuration":{"identitySettings":[{"identity":"","lifecycle":"All"}]}}}' ``` `` is the full resource ID, for example: `/subscriptions//resourcegroups//providers/Microsoft.ManagedIdentity/userAssignedIdentities/` The PATCH **replaces** the entire `identitySettings` array. If the container app has **multiple** user-assigned identities, include **every** identity in the array each time you run this command. Verify: ```bash theme={null} az containerapp show --name --resource-group --query "properties.configuration.identitySettings" ``` You should see an entry similar to: ```json theme={null} [ { "identity": "/subscriptions/.../userAssignedIdentities/", "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: ```bash theme={null} az containerapp update --name \ --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" ``` Some deployments also honor `SB_EXECUTION_ENV_INCLUSION_LIST` as an alias when wiring workers. Use the variable names your Terraform or platform team standardizes on; the OPA Dockerfile default is `SUPERBLOCKS_EXECUTION_ENV_INCLUSION_LIST`. 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](/integrations/integrations-library/mssql) integration using a **connection string** with this shape: ``` Server=,1433;Database=;Authentication=Active Directory Integrated;Client Id= ``` * **`Client Id`** is the managed identity’s **client (application) ID**, not the object/principal ID: ```bash theme={null} az identity show --name --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 ''` 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): ```sql theme={null} USE ; CREATE USER [] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER []; ALTER ROLE db_datawriter ADD MEMBER []; ``` ### `Connection failure: (code undefined) (code undefined)` Usually a **malformed** connection string or an **unsupported** parameter before authentication runs. Confirm the string matches [Step 8](#step-8-create-the-datasource-in-superblocks). Check Container App logs: ```bash theme={null} az containerapp logs show --name --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](#step-6-register-the-identity-with-identitysettings-cli) and verify with `az containerapp show ... --query "properties.configuration.identitySettings"`. * **`SUPERBLOCKS_EXECUTION_ENV_INCLUSION_LIST`** omits **`IDENTITY_ENDPOINT`** and **`IDENTITY_HEADER`** — fix [Step 7](#step-7-update-superblocksexecutionenvinclusionlist), then restart. ### `Unknown properties identitySettings in ContainerAppContainer` The REST body targeted the wrong path. Use **`properties.configuration.identitySettings`**, not `properties.template.containers[].identitySettings`: ```json theme={null} {"properties":{"configuration":{"identitySettings":[{"identity":"","lifecycle":"All"}]}}} ``` ### `identitySettings` PATCH removed other identities Each PATCH **replaces** the full array. Always send **every** managed identity: ```json theme={null} {"properties":{"configuration":{"identitySettings":[ {"identity":"","lifecycle":"All"}, {"identity":"","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](#step-2-grant-microsoft-graph-permissions) for **both** the VM system-assigned identity and the OPA user-assigned identity. ## Related documentation * [Database connections](/integrations/auth/database-connections/index) — networking and connection strings * [Microsoft SQL Server integration](/integrations/integrations-library/mssql) — Superblocks UI setup * [AWS IAM with the agent](/integrations/auth/aws-iam) — execution environment allowlist pattern for cloud workloads