Home > How to Secure Azure SQL Database with Managed Identity & Azure AD Authentication?
Amine Teffahi
21 June 2022
Lire cet article en Français

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

Comment sécuriser Azure SQL Database avec « Managed Identity » & l’authentification Azure AD ?

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.

 

Authentification Azure AD explanation

Explanatory diagram

 

Requirements

 

Before we get started, you need to deploy the following resources:

  1. SQL Server
  2. SQL Database
  3. App Service

 

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:

 

 

Activation Managed Service Identity (MSI)

 

This will create an Enterprise Application in the Azure Active Directory (AAD):

 

Enterprise Application Azure Active Directory

 

The Managed Identity web app will be used to connect to the SQL database as shown below:

 

web app Managed Identity database SQL

 

 

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.

 

Access database Azure Active Directory

 

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.

 

Azure AD private endpoints

 

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.

 

web app Managed Identity

 

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.

 

 

SQL Server Management Studio

SQL Server Management Studio

 

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:

 

Identity system config SQL

 

 

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:

For more information about SQL alerts, see the official Microsoft documentation.

 

 

This posts should interest you
Comments

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.

Leave a Reply

Receive the best of Cloud, DevOps and IT news.
Receive the best of Cloud, DevOps and IT news.