I have set up a bunch of services in Azure, namely an SQL Server instance, an SQL database, and an App Service running a Web API, which talks to the SQL DB in Azure.
The database is running fine; I can connect from SSMS no problem. The Web API is also running fine and talks to the DB without issue when I run in VS, however, once published, the Web API is unable to connect to the database.
The App Service has the connection string in the configuration, and that connection string uses Authentication=Active Directory Default. Again, this will work locally (when the default resolves to my account), but not when published (when the default resolves to some unknown entity). My question is this- what is that entity? Or how can I find out and check its access?
Curiously, this used to work without any such considerations, but seemed to stop after the App Service was restarted and has been unable to connect ever since.
When your code is running in an app service, it should use the managed identity of the app service to access the database, see the following link:
This also means that the managed identity of the service must be granted access to the database. see: https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver16#using-active-directory-managed-identity-authentication