Access Azure SQL database with System Assigned Managed identity

Microsoft Azure provides a feature called managed identities that allows you to authenticate to Azure services without having to store any secrets or credentials in your code. Although you could securely store the secrets in Azure Key Vault, Azure services need a way to access Azure Key Vault. This is where the System Assigned Managed Identities are very helpful.

Managed identities in Azure

Managed Identities are used for passwordless authentication between Azure resources.

There are two type of managed identities in Azure:

• System-assigned: The system-assigned managed identity (SMI) is tied to a specific Azure resource and is automatically created and deleted along with the resource.
• User-assigned: The user-assigned managed identity (UMI) is an independent identity object (service principal) in Azure Active Directory. You can reuse it by assigning to one or multiple resources.
  Unlike the system-assigned managed identity, if you delete the resource to which the user-assigned managed identity is assigned, the identity will not be deleted automatically.

Accessing an Azure SQL Database with SMI

To access a Azure SQL Database with a system-assigned managed identity, you will need to execute the following steps:

1. Enable system-assigned managed identity on the Azure resource
2. Create a technical login user for the managed identity in the SQL server
3. Grant permissions to the technical user in the database

Enable managed identity on the Azure resource

There are many Azure services that support managed identities for Azure resources such as Virtual Machines, App Services, Azure Functions or many other supported resources.

You can enable the system-assigned managed identity on most of the Azure resources by selecting the Identity menu under the Settings (or Security) blade of the Azure resource.

The following screenshot demonstrates the Identity settings of a Virtual Machine in Azure Portal:

A typical use case for system assigned managed identities is when you want to connect an Azure Data Factory to an Azure SQL database, for example, to perform data transformations on the data stored in the SQL database. The screenshot bellow demonstrates the Managed Identity settings on a Data Factory resource:

By enabling the System assigned identity the resource will automatically generate a “technical user” (service principal) in the Azure Active Directory. You can use this service principal to authenticate on the target resource.

Please note the name of the Azure Data Factory resource DF-PUBVIEW-QAS. We will use this resource name as username in the SQL server later on.

Create a login user in the server

In order to allow an Azure resource to connect to the SQL database you need to create an SQL server login that refers to the “technical user” in the Azure Active Directory. In our case, we will create a server login for the Data Factory DF-PUBVIEW-QAS as follows:

1. Connect to the SQL database by using tools like SQL Server Management Studio with an Azure AD user. (see note bellow)
2. Run the following T-SQL:

CREATE USER [DF-PUBVIEW-QAS] FROM  EXTERNAL PROVIDER

Note:
You need to login to the server and execute the above T-SQL command with an Azure Active Directory user. Executing the command with SQL server authenticated user won’t work, because users that are not based on an Azure AD account cannot create Azure AD-based users. Why? Because SQL server authenticated users do not have permission to validate database users with the Azure AD.

Grant database permissions to the technical user

Once the login user is created you need to grant permissions to the technical user to access the database. You can grand permissions to the managed identity (technical user) as you normally do for other SQL users by running the following code:

ALTER ROLE [role name] ADD MEMBER [DF-PUBVIEW-QAS];

The previews versions of Microsoft SQL server allows you to change roles by executing the following stored procedure. Use this command if the the ALTER ROLE command does not work:

EXEC sp_addrolemember 'role name', 'DF-PUBVIEW-QAS'

To obtain more information about user roles and permissions, refer to the Microsoft documentation.

Now, you can use the system-assigned managed identity to authenticate to the SQL database and perform various tasks, such as querying data or modifying the schema. You can use the Azure Identity library in your code to manage the authentication process and obtain the necessary credentials.

I hope this helps!
Let me know if you have any questions.