How to switch which Azure SQL database my app service connects to

Question

Perhaps a native question...

I have an Azure app service that happily connects to an Azure SQL database.
I now want to change it to connect to a different database instance, on the same Azure SQL server, to use the free database offer.
I tried to copy the configuration settings manually, but when I then change the connection string used by the app service, I seem to get authentication errors. The kudu debug console shows:
"Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user '<token-identified principal>'. at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)". It is (I believe) using my Entra ID and again both databases are in the same Azure SQL server, and I don't recall what I had to do to get the first database working.

I found one help document online that has the following SQL query:

SELECT DISTINCT pr.principal_id, pr.name, pr.type_desc, pe.state_desc, pe.permission_name

FROM sys.database_principals AS pr

JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id;

When I run that against both databases, from the respective Query editor, I do get different results. The working database has 4 entries: public (DATABASE_ROLE, GRANT, SELECT), dbo (SQL_USER, GRANT, CONNECT), and two with names of my app services (EXTERNAL_USER, GRANT, CONNECT).
The non-working database has only the first two entries. Assuming that is the problem, how do I go about fixing this? I'm having a hard time finding the right documentation to answer this question and I've tried a bunch of things that don't work so hoping someone can point me in the right direction.

Answer 1

Hi,

Please connect to the database that your app service is unable to connect to and execute the following:

CREATE USER [yourappservicename] FROM EXTERNAL PROVIDER;

After completing the above please restart the app service and test to see if you are able to connect.

https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?view=azuresql&tabs=azure-powershell#create-contained-users-mapped-to-microsoft-entra-identities

Please click Accept Answer and upvote if the above was helpful.

Thanks.

-TP