Token-based database authentication fails with "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

Question

I'm having trouble getting token-based database authentication working. Connecting with Active Directory Password works, but when connecting with a token I receive the following error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

This problem has been discussed here on Stack Overflow and here on MSDN, but no solution is available. This blog post suggests there is something wrong with user permissions, but it's rather vague.

Configuration

Acquiring a token succeeds, with the following method:

static async Task<string> GetAccessToken()
{
    var certificateSDN = "redacted";
    var tenantId = "redacted";
    var clientId = "redacted";
    var resource = "https://database.windows.net";

    X509Certificate2 certificate;
    using (var certificateStore = new X509Store(StoreName.My, StoreLocation.CurrentUser))
    {
        certificateStore.Open(OpenFlags.ReadOnly);
        certificate = certificateStore.Certificates
            .Find(X509FindType.FindBySubjectDistinguishedName, certificateSDN, false)
            [0];
    }

    var authenticationContext = new AuthenticationContext($"https://login.microsoftonline.com/{tenantId}");
    var clientAssertionCertificate = new ClientAssertionCertificate(clientId, certificate);
    var authenticationResult = await authenticationContext.AcquireTokenAsync(resource, clientAssertionCertificate);

    return authenticationResult.AccessToken;
}

Connecting to the database is done like this:

var connectionStringBuilder = new SqlConnectionStringBuilder()
{
    DataSource = "redacted.database.windows.net",
    InitialCatalog = "redacted",
};
using (var sqlConnection = new SqlConnection(connectionStringBuilder.ConnectionString))
{
    sqlConnection.AccessToken = accessToken;
    sqlConnection.Open();
}

The error occurs when calling sqlConnection.Open().

The clientId matches the application ID of an app registration. I've created a user in the database, matching the name of the app registration, with the following query:

create user [redacted] from external provider
go
exec sp_addrolemember N'db_datareader', N'redacted'
exec sp_addrolemember N'db_datawriter', N'redacted'

Diagnostics

With the following queries, I check that the user has the roles and has permission to connect to the database:

declare @user nvarchar(max);
set @user = 'redacted'

select
    u.name
    ,r.name
from
    sys.database_role_members as m
    inner join sys.database_principals as r on m.role_principal_id = r.principal_id
    inner join sys.database_principals as u on u.principal_id = m.member_principal_id
where
    u.name = @user;

select
    class_desc
    ,major_id
    ,permission_name
    ,state_desc
from
    sys.database_permissions
where
    grantee_principal_id = user_id(@user);

This is the output:

redacted    db_datareader
redacted    db_datawriter

DATABASE    0   CONNECT GRANT

Trying to connect with a bogus token gives the same error. So I don't know if there's something wrong with my token, or if there's something wrong with the database configuration. What other diagnostics can I perform, to find the cause of this problem?

Answer 1

It turned out to be a very minor mistake: resource needs a final slash at the end.

So instead of

var resource = "https://database.windows.net";

it needs to be

var resource = "https://database.windows.net/";