Skip to main content

Let's use the Azure SQL Database for an app

Overview​

Running application that needs to connect to an Azure SQL Database? Regardless of the language or framework, using Entra ID authentication with a managed identity is secure and convenient.

How? Simply put... Create an ID in Entra -> In Entra, assign the ID a SQL specific role -> In the Azure SQL Database, grant the ID access.

Create a Managed Identity for Azure SQL Database (step 1 of 3)​

  1. Go to Azure Portal.
  2. Open your Azure SQL server
  3. under "Settings", click on "Identity"
  4. Under "System assigned", click "On" and then "Save"

Let the Managed Identity Access Azure SQL Database (step 2 of 3)​

  1. After the identity is created, go to "Access Control (IAM)" and click "Add role assignment"
  2. In the "Add role assignment" pane, select the "SQL DB Contributor" role
  3. Under "Assign access to", select "Managed identity"
  4. Click "Select members" and add the ID from step 1.

alt text

Let's grant the Managed Identity access to the database (step 3 of 3)​

  1. Connect to the database using SSMS.
  2. Run the following SQL commands to create and grant access.
CREATE USER [MyManagedIdentity@domain.com] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [MyManagedIdentity@domain.com];

Questions?​

  • Why use a system-assigned managed identity (SAMI) instead of a user-assigned managed identity (UAMI)? It's automatically created and managed by Azure. User assigned identity requires more manual setup and management.

  • Can I use the same SAMI for multiple Azure SQL databases in a single Azure SQL server? Yes, you can use the same SAMI for multiple databases within the same server but not across different servers.

  • What is then benefit of using UAMI? It can be shared across multiple resources and services, even across different Azure SQL servers.

Details not covered in the quick steps above​

  • Although Contributor seems excessive when read-only access is needed, SQL DB Contributor role is the minimum role required for the managed identity to access the Azure SQL Database. To restrict permissions, dial-in permission at the database level.