How to Secure Azure SQL Database with Managed Identity & Azure AD Authentication?

Post co-written by Yacine Smail and Amine Teffahi
How can you connect to an SQL database transparently and securely using Azure Active Directory (AD) authentication rather than the traditional method (user ID and password)? This is what we will look at today, step by step. In this post, we will demonstrate this method using a service app.
Explanatory diagram
Requirements
Before we get started, you need to deploy the following resources:
- SQL Server
- SQL Database
- App Service
Enabling the Managed Service Identity (MSI)
There are different ways to enable a managed identity for an app service (PowerShell, Azure Command-Line Interface [CLI], Azure Portal, etc.). To keep things simple, we will enable it through the Azure Portal.
There are two types of managed identities:
- System-assigned: Since this identity is linked to the App Service, deleting the App Service will also delete the identity. An application can only have one system-managed identity.
- User-assigned: A user-assigned identity is a standalone resource that can be assigned to multiple applications. An application can also have multiple user-assigned identities.
Follow the steps below to enable this from the portal:
This will create an Enterprise Application in the Azure Active Directory (AAD):
The Managed Identity web app will be used to connect to the SQL database as shown below:
Granting Access to the Database
Granting access to the database is straightforward. Simply:
- Go to the Azure Portal
- Select your SQL server
- Select the Active Admin directory
- Click “Set admin” and choose an Azure AD identity
- Click “Save”
Note: providing a group of admins is preferable.
Now that the AAD group has been configured as admin for the SQL server, it can be seen at the SQL server level. Now, log in using SQL Server Management Studio (SSMS) from a machine with network access to the SQL server. Remember that an SQL server typically contains sensitive data so it should never be made public. Private endpoints or service endpoints should be used.
If we add the Managed Identity web app to the “admin” group, it will be used to read and/or write to the database in our App Service.
Adding the MSI directly to the “admin” group is not good practice. For this reason, it is better to add the user to the database with more restricted permissions.
Log in to the database using an SSMS admin account and run this code to assign read permissions for our App Service.
After adding the user and assigning them the appropriate role, we can call our Application Programming Interface (API), which will retrieve the information from the database.
Accessing the Database
To access the database from an App Service, update the SQL server configuration and connection string:
1. Configuring the connection string:
To ensure that the App Service uses the system identity to access SQL, make the following changes to the config file:
2. Database access:
Once configured, modify the database connection string:
Here is our test result:
SQL Server Limits
It is not possible to disable or delete the SQL admin account. Anyone with the admin login credentials can connect to the SQL database with admin rights.
Microsoft suggests the following:
- Enable SQL audit logs (export to a storage account, Log Analytics, or an Event Hub)
- Enable Advanced Threat Detection with security alerts via the Security Center
- Use Azure Policy built-in definitions for SQL Database
For more information about SQL alerts, see the official Microsoft documentation.
Is it possible to put images in the last section (Accessing the Database) where they click to a full sized image? Or the code in plain text? The images are shrunk from normal view and my old eyes can’t read the content. I had to ask a kid to come over and help me read it.
Thank you for your comment: the code has been added so you can read it and copy it easily!