Troubleshooting Azure Managed Identity Connection to SQL Azure Database

Managed identities allow you to connect to several Azure resources, including Azure App Services to Azure SQL databases. This eliminates the need for app secrets, custom SQL users, and passwords, as the managed identity acts as a bridge between the resources. If you are new to managed identities, Microsoft provides a comprehensive article on setting it up:

https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cef%2Cdotnet

However, after you have completed the setup process, you may encounter an error when visiting the site. The error message might read: “HTTP Error 500.30 – ASP.NET Core app failed to start.”

• The app failed to start
• The app started but then stopped
• The app started but threw an exception during startup

Troubleshooting steps:

• Check the system event log for error messages
• Enable logging the application process’ stdout messages
• Attach a debugger to the application process and inspect

For more information visit: https://go.microsoft.com/fwlink/?LinkID=2028265

This error can have several causes, such as the app failing to start, stopping after starting, or throwing an exception during startup. To troubleshoot the issue, Microsoft recommends checking the system event log for error messages, enabling logging of the application process’ stdout messages, and attaching a debugger to the application process.

In our case, we discovered the issue by starting the .NET Core application from within the Azure App Service console. By navigating to Development Tools and Console, and then navigating to the root directory of the application, we ran the application using the command “dotnet run (yourApplicationName).dll”.

Upon starting the application, we encountered the following error: “Login failed for user“. Further investigation led us to realize that the Azure App Service System Identity was given the same name as the Azure App Service itself. In our scenario, this name already existed in Azure Active Directory’s Enterprise Applications.

Simply renaming the identity created by the Azure App Service was not enough. We took the following steps to fix the issue:

• Disable the System Identity in the Azure App Service

• Check the active SQL identities by running this query:

select name as username,
create_date,
modify_date,
type_desc as type,
authentication_type_desc as authentication_type
from sys.database_principals
where type not in (‘A’, ‘G’, ‘R’, ‘X’)
and sid is not null
order by username;

• Delete the SQL identity, by running this query:

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N’username’)
DROP USER [username]

• Recreate the correct identity once again, by running this query:

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

After completing these steps, we were able to connect our Azure App Service to the Azure SQL database without encountering any further issues.