Resolved - Login Failed For User Token-Identified Principal In Azure SQL DB

Sometimes applications can fail while connecting to Azure SQL DB using Managed Service Identity with the error message: " Login failed for user ‘<token-identified principal>” and Unable to connect to SQL.

This blog demonstrates detailed steps to resolve this login failed issue. In this example, the function app is unable to access Azure SQL DB using MSI. We will be granting permission in step by step.

Step 1. Enable MSI on your Function App

Make sure System assigned identity is on for your app. Once you enable MSI for an Azure Service (e.g. Azure Functions), then dedicated Service Principal i.e. Object ID created in the Azure AD tenant.

Step 2. Set Azure AD admin in SQL Server 

 Set yourself as Admin in Azure SQL Server. This is required to perform the rest of the steps successfully.

NOTE: "Allow Azure services and resources to access this server” should be set as Yes in Firewalls and virtual networks settings in your Azure SQL DB, in case it was not set earlier during Azure SQL setup.

Step 3. Sign in to SQL Database by using the SQLCMD command

 In Azure Cloud Shell, sign in to SQL Database by using the SQLCMD command. Replace with your server name, with the database name that your app uses, and with your Azure AD user's credentials.

sqlcmd -S <server-name>.database.windows.net -d <db-name> -U <aad-user-name> -P "<aad-password>" -G -l 30

Alternatively, you can login to the SQL Server using SSMS with your Azure AD credentials.

Step 4. Grant permission to identity

Run the following commands to grant the permissions your app needs. Your app could be Function App, Web app, etc. For example,

CREATE USER [<identity -name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];

GO

Here, < identity-name > is the name of the managed identity in Azure AD. If the identity is system-assigned, the name is always the same as the name of your App Service app.

In my case, I am granting permission for a function app called func-MyToDoJob 

Happy Reading!