SQL Azure connection error with User Assigned Managed Identity 'Login failed for user'

Question

I have a function app that is assigned a user assigned managed identity, and it uses that to connect to the SQL database. This was working fine for a few days, but then suddenly stopped working, without any changes to db or the function app.

Error: Login failed for user '<ClientId>@<TenantId>'.

• ClientId: ClientId of the user assigned managed identity.
• Tenant Id: The tenant this identity exists in.

I searched online, and found ways to look into a more detailed error in sys.event_log. As per this, I see that error is 18456, and state is 68. Unfortunately state 68 for error 18456 is not documented anywhere. (Official doc).

This is how I create a SqlConnection (and note that this was working before, and the same code is working elsewhere in the same exact setup):

SqlConnection connection = new SqlConnection("Server=tcp:myserver.database.windows.net,1433;Database=MyDb;");
connection.AccessToken = await new AzureServiceTokenProvider("RunAs=App;AppId=<ClientId>").GetAccessTokenAsync("https://database.windows.net/");

The user was created in db using:

CREATE USER [<Name of user assigned identity>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<Name of user assigned identity>];
ALTER ROLE db_datawriter ADD MEMBER [<Name of user assigned identity>];

Any pointers to where I can look into next?

Note:

1. Using Azure Function Runtime 2.0 (dotnet core)
2. Using Microsoft.Azure.Services.AppAuthentication 1.4.0 (latest stable).

Answer 1

I couldn't find the root cause, but I am posting what helped me get unblocked so that hopefully it will help others in future.

For some reason all form of authentication (except by the AAD admin of the server) were failing on this server. So not only the user assigned identity authentication failed (which is described in the question above), but also contained user auth failed. Removing the user assigned identity from db and re-adding didn't work:

DROP USER [<Name of user assigned identity>];
CREATE USER [<Name of user assigned identity>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<Name of user assigned identity>];
ALTER ROLE db_datawriter ADD MEMBER [<Name of user assigned identity>];

Similarly dropping the contained user and recreating didn't work either.

DROP USER [ContainedUser];
CREATE USER [ContainedUser] WITH PASSWORD='******';
ALTER ROLE db_owner ADD MEMBER [ContainedUser];

I also noticed that the secondary (i.e. replica) was working fine and auth was working against it. So basically I concluded something is wrong with my primary, not sure what exactly.

So I decided to recreate the database:

• I did a failover to the secondary.
• Remove the replication link, and then deleted the bad server. (just deleting the db and doing rest of the steps below wasn't enough, I did try)
• Recreated the server.
• Reconfigured replication so that the db got created on the new server as secondary.
• Did another failover so that the newly created db becomes the primary.
• All auth worked fine.