System Assigned identity failed to connect to SQL Server database

Question

I have a .NET App Service running which is using Entity Framework to connect to an Azure SQL Server database.

The SQL Server is set to only allow connections from Microsoft Entra.

Steps taken:

1. Upload Web API to App Service in Azure
   • My app has the following connectionstring: Server=tcp:acme-dev-db.database.windows.net,1433;Initial Catalog=acme-db-test;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication="Active Directory Default";
2. Enabled System Assigned Identity on App Service
3. Ran the following query through SSMS on my database acme-db-test

   CREATE USER [acme-myapp-api-t] FROM EXTERNAL PROVIDER;
   ALTER ROLE db_datareader ADD MEMBER [acme-myapp-api-t];
   ALTER ROLE db_datawriter ADD MEMBER [acme-myapp-api-t];
   ALTER ROLE db_ddladmin ADD MEMBER [acme-myapp-api-t];

4. Ensured that "Allow Azure services and resources to access this server" is set to "Yes" from the Azure Portal under the Sql Server\Security\Firewalls and virtual networks

As far as I know this should be enough to allow my Web API to connect to the SQL Server database. But when I try to start my application in the App Service I get the following error back:

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ''.

I used the same approach for the dev App Service with the name acme-myapp-api-d, and it worked fine for that resource. The only difference is that the dev App Service connects to a different database (acme-db), but on the same SQL Server instance.

Anyone any idea why I am getting this Login failed error for the test App Service?

---

Update 1:

When I change my connectionstring to connect to the dev database acme-db then it all works fine. I also ran the query above to add my App Service as EXTERNAL PROVIDER.

But for some reason it doesn't connect the second database I created. Still no idea why.

Update 2:

I deleted the database and re-created it, ran the query to add the resource as EXTERNAL PROVIDER and it worked.

The only thing I changed was that the database was a development database, and now I switched to a serverless production database.

Not sure if you can't connect to a development database using Entra ID, but it works when now.