基于令牌的数据库身份验证失败，并显示“用户'NT AUTHORITY \ ANONYMOUS LOGON'的登录失败". [英] Token-based database authentication fails with "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

问题描述

我无法使基于令牌的数据库身份验证正常工作.使用Active Directory密码连接有效，但是使用令牌连接时，出现以下错误:

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:

用户"NT AUTHORITY \ ANONYMOUS LOGON"的登录失败.

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.

使用以下方法成功获取令牌:

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();
}

调用sqlConnection.Open()时发生错误.

clientId与应用程序注册的应用程序ID匹配.我已经在数据库中创建了一个用户，将应用程序注册的名称与以下查询匹配:

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);

这是输出:

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?

推荐答案

事实证明这是一个非常小的错误:resource最后需要最后一个斜杠.

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

所以不是

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

它必须是

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

这篇关于基于令牌的数据库身份验证失败，并显示“用户'NT AUTHORITY \ ANONYMOUS LOGON'的登录失败".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！