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:

1. SQL Server
2. SQL Database
3. 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.