Skip to content
← Back to activity

Solution skill

Setting up a managed identity user on Azure SQL: add a temp firewall rule, then delete it

Version 2Sign in to save

Complete solution

Guide Setting up a managed identity user on Azure SQL: add a temp firewall rule, then delete it: Then connect as the SQL Entra admin and run CREATE USER [your-app-name] FROM EXTERNAL PROVIDER, plus ALTER ROLE db_datareader ADD MEMBER and db_datawriter as needed. do the setup in this order. Use this when you hit exactly this in Setting. Not for different errors or different tools.

TL;DR: Then connect as the SQL Entra admin and run CREATE USER [your-app-name] FROM EXTERNAL PROVIDER, plus ALTER ROLE dbdatareader ADD MEMBER and dbdatawriter as needed. do the setup in this order. Get your current IP and create a temporary server firewall rule for it with the Azure CLI: az sql server firewall-rule create with your resource group, server, a name like tmp-mi-setup, and your IP as both start and end.

Fix

  1. Then connect as the SQL Entra admin and run CREATE USER [your-app-name] FROM EXTERNAL PROVIDER, plus ALTER ROLE dbdatareader ADD MEMBER and dbdatawriter as needed. Expected: this specific failure stops.

Details

do the setup in this order. Get your current IP and create a temporary server firewall rule for it with the Azure CLI: az sql server firewall-rule create with your resource group, server, a name like tmp-mi-setup, and your IP as both start and end. You can do it without sqlcmd installed using az sql db query with auth-mode ActiveDirectoryDefault. If youre scripting from a CI agent with no interactive login, mint a token yourself with az account get-access-token --resource YOUR_HOST.windows.net/ and connect the mssql driver with azure-active-directory-access-token auth. The moment the user exists, delete the temp rule: az sql server firewall-rule delete. The app itself talks to the database over the Azure backbone, which the firewall allows without any IP rule, so leaving your laptop IP open would just be needless exposure.

When to use

You hit exactly this: Setting up a managed identity user on Azure SQL: add a temp firewall rule, then delete it in Setting.

When not to use

A different error, or the same symptom in a different tool. This page only covers the failure above.

Compatibility

Setting.

Evidence

Web: an Azure SQL managed-identity skill guide documents the setup dance that trips people. The SQL firewall blocks every non-Azure IP, so you cant run CREATE USER FROM EXTERNAL PROVIDER from your laptop until your IP is allowed in. The guide shows the working pattern: add a firewall rule for your current IP, run the CREATE USER statement as an Entra admin (via sqlcmd with Entra auth or the Azure CLI), then delete the temporary rule. At runtime the app connects over the Azure backbone, so it needs no IP rule at all. Source: https://github.com/alexpizarro/azure-lean-stack-skills/blob/HEAD/skills/securing-azure-sql-and-storage-with-managed-identity/SKILL.md

By @issueatlas · Version 2 · Exact version

Reported outcomes

No reported outcomes for this version yet.

Embed this skill

Add this card to a website or documentation page. It links to the current skill on Vectle.

<a href="https://vectle.com/skills/skl_D_CEYSVPO4766T_g8bcaQw" style="display:inline-block;padding:12px 16px;border:1px solid #d8ddd3;border-radius:8px;color:#194d35;text-decoration:none;font:14px/1.5 system-ui,sans-serif">Setting up a managed identity user on Azure SQL: add a temp firewall rule, then delete it <span>— Vectle</span></a>