Unleashing the Power of System Managed Identity for Azure SQL Database: A Step-by-Step Guide and Troubleshooting Tips

Login failed for user ‘<token-identified principal>’

Azure SQL Database provides a powerful feature called Managed Identity, which allows applications and services to authenticate and access resources securely without the need for explicit credentials. In this blog post, we will focus on System Managed Identity for Azure SQL Database. We will cover its benefits, common permission-related exceptions, and provide a practical example of connecting Azure Data Factory and Azure App Service using System Managed Identity. Additionally, we’ll demonstrate how to query the roles assigned to the managed identity.

Introduction to System and User Assigned Managed Identity

Azure offers two types of Managed Identity: System Managed Identity and User Assigned Managed Identity. System Managed Identity is automatically provisioned and managed by Azure for a specific Azure resource, such as an Azure SQL Database. On the other hand, User Assigned Managed Identity is created explicitly by the user and can be assigned to one or more Azure resources. Learn more about Managed Identity

System Assigned Managed Identity Provides Several Benefits

• Simplified authentication: Eliminates the need to store and manage explicit credentials for Azure resources.
• Secure access: Establishes a secure channel between the Azure resource and the target resource without exposing sensitive information.
• Seamless integration: Allows applications and services to access other Azure resources, such as storage accounts or Key Vaults, with minimal configuration.

Enable the System Assigned Managed Identity

To enable the managed identity, you can use the Azure portal, Azure CLI, or Powershell.

Azure CLI

az sql server update --name <sql-server-name> --resource-group <resource-group-name> --assign-identity

Azure Portal

Enable System Assigned Managed Identity For App Service

To enable the managed identity, you can use the Azure portal, Azure CLI, or Powershell.

Azure CLI

az webapp identity assign --name <app-service-name> --resource-group <resource-group-name>

Azure Portal

Common Exceptions and Permission Requirements

The more common exception when working with managed identity
Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ‘<token-identified principal>’. Stackoverflow

When working with System Managed Identity, it’s crucial to understand the common exceptions that can occur when the appropriate permissions are not assigned to the managed identity. The most common permission-related exceptions include:

• Login Permissions: Ensure that the managed identity has the necessary login permissions at the SQL Server or database level. Without these permissions, the managed identity won’t be able to establish a connection to the Azure SQL Database.
• DDL (Data Definition Language) Permissions: If the managed identity lacks the necessary DDL permissions, it won’t be able to perform operations such as creating or modifying database schema objects.
• Reader and Writer Permissions: Without proper reader or writer permissions, the managed identity may encounter issues when attempting to access or modify data in the Azure SQL Database.
• Stored Procedure Execution Permissions: If the managed identity does not have execute permissions on stored procedures, it won’t be able to invoke them.

To avoid these exceptions, it’s essential to ensure that the managed identity is granted the appropriate permissions at the SQL Server or database level, as well as for specific operations within the Azure SQL Database.

Querying Roles Assigned to the Managed Identity

To check the roles assigned to the System Managed Identity in Azure SQL Database, you can use the following query

SELECT p.name AS PrincipalName, r.name AS RoleName
FROM sys.database_role_members m
JOIN sys.database_principals p ON m.member_principal_id = p.principal_id
JOIN sys.database_principals r ON m.role_principal_id = r.principal_id
WHERE p.name = '<app server name or adf name>'

Assign Appropriate Permission

Run the following command using SSMS or Azure Data Studio. You must connect to the database you want the system identity to access and have admin permissions to execute the following commands. A system identity is always Azure resource name, e.g., App Service Name or ADF name—additional reading for Create User command.

USE DatabaseName;
CREATE USER <App_Service_Name or Managed Identity Name> FROM EXTERNAL PROVIDER;

-- DB Reader
ALTER ROLE db_datareader ADD MEMBER <App_Service_Name or Managed Identity Name>;
--DB Writer
ALTER ROLE db_datawriter ADD MEMBER <App_Service_Name or Managed Identity Name>;

-- db_ddladmin [Not required]
ALTER ROLE db_ddladminADD MEMBER <App_Service_Name or Managed Identity Name>;

Limitations

System Assigned Managed identities in Azure have certain limitations that you should be aware of:

1. One-to-one relationship: Each Azure resource can have only one system-managed identity associated with it. If you require multiple identities for different purposes, you may need to consider using user-assigned managed identities.
2. Limited to Azure AD authentication: System-managed identities are designed for Azure AD authentication within the Azure ecosystem. They cannot be used for external authentication protocols like OAuth or OpenID Connect.
3. Limited to Azure resources: System-managed identities are tied to specific Azure resources such as virtual machines, Azure Functions, or App Services. They cannot be used outside the scope of these resources.
4. Limited to specific Azure services: While system-managed identities are available for a wide range of Azure services, not all services support them. It’s essential to check the documentation for specific services to confirm if they support system-managed identities.
5. Limited to specific resource types: System-managed identities are available for certain resource types like virtual machines, Azure Functions, and App Services. They may not be available for other Azure resources or services.
6. Limited control over lifecycle: With system-managed identities, the lifecycle management is handled automatically by Azure. This means you have limited control over aspects like renewal, revocation, or deletion of the identity.
7. Limited visibility and management: System-managed identities are not visible or manageable through Azure Active Directory. They are managed within the context of the specific Azure resource to which they are assigned

Key Takeaways

1. System Managed Identity for Azure SQL Database provides a secure and seamless authentication method for accessing resources within Azure.
2. Understanding the benefits of System Managed Identity, such as simplified authentication and secure access, can help you leverage its full potential.
3. Be aware of common permission-related exceptions that may arise when working with System Managed Identity, including missing login permissions at the SQL Server or database level, as well as DDL, reader, writer, or stored procedure execution permissions.
4. By properly configuring and granting the necessary permissions to the managed identity, you can avoid these exceptions and ensure smooth operations.
5. Practical examples, like connecting Azure Data Factory and Azure App Service using System Managed Identity, demonstrate the real-world application and value of this feature.
6. Querying the roles assigned to the managed identity can help you verify the access levels and permissions associated with it within the Azure SQL Database.
7. While system-managed identities offer convenient and secure authentication within Azure, it’s important to understand their limitations and consider whether they align with your specific requirements. For more advanced scenarios or greater control, user-assigned managed identities or service principals may be more suitable.

By keeping these key takeaways in mind, you can harness the power of System Managed Identity in Azure SQL Database and enhance the security and efficiency of your Azure resources.