Token-based login to SQL DB fails from App Service, works fine locally.

Question

Using an Azure access token to connect to SQL DB works fine when running locally under Visual Studio.

Deploying the web app to Azure App Service is fine.

The deployed web app is unable to connect to the SQL DB:
System.Data.SqlClient.SqlException (0x80131904): Login failed for user '<token-identified principal>'.

Remote debugging confirms that a token is issued.

When attempting to add a login for the app to the SQL DB,
CREATE USER [app-name] FROM EXTERNAL PROVIDER;

Error message results:
Principal 'app-name' has a duplicate display name. Make the display name unique in Azure Active Directory and execute this statement again.

Cannot find duplicated name/display name/etc anywhere in Azure, so unable to determine whether adding a login for the app will solve the problem, or if attention should be focused on another aspect(s) of the configuration.

There was a previous error related to being unable to get a token, however creating a Key Vault with GET SECRET for the service principal resolved the issue, progressing to current situation of being unable to log in to SQL Server via the web app / app service.

Any guidance will be greatly appreciated.

Answer 1

The "duplicate display name" was in fact the issue. Not sure if it was something I did, or if Azure automatically created both an Enterprise Application and App Service having the same name for me, but after renaming the Enterprise Application I was then able to add the App Service user to MSSQL, which solved the problem.

Answer 2

dude u know that we can see what you trying to hide !!?

Answer 3

@dunderhead-9106
You have mentioned multiple issues here. I will try and address them one by one.

• Token Authentication via Azure WebApp

For this to work, you will need to enable managed Identity on your AppService

• Duplicate Display names for Service Principals

To find out the SPN's with duplicate names, you can use Azure AD Powershell

 Connect-AzureAD
 Get-AzureADServicePrincipal -SearchString "YOURSPNHERE" 

In case of Managed Identity, SPN is the same as the name of your AppService

You should be able to find the duplicate SPN's from the list and rename yours accordingly

After renaming, you can try adding the SPN to your Azure SQL Database and it should work.

 CREATE USER [YOURSPNHERE] FROM EXTERNAL PROVIDER;