基于 SQL AAD 令牌的身份验证 - 用户“NT AUTHORITYANONYMOUS LOGON"登录失败

2022-01-08 00:00:00 azure-sql-database authentication azure-active-directory sql-server asp.net-mvc

要求 - 我正在尝试从 asp.net MVC 应用程序连接到 azure SQL DB，并且到 azure SQL DB 的连接类型是基于令牌"，下面是我完成的设置.

Requirement - I am trying to connect to azure SQL DB from a asp.net MVC application and the connection type to azure SQL DB is "token based" and below are the set up done from my end.

一个.使用基于证书的身份验证创建了一个 AAD 应用程序(例如:MTSLocal).

a. Created an AAD application( ex : MTSLocal ) with certificate based authentication.

b.在 SQL 中添加了对上述 AAD 的权限.

b. Added permission to the above AAD in SQL.

从外部提供商创建用户 [MTSLocal]；

CREATE USER [MTSLocal] FROM external provider;

c.在代码级别，我试图通过使用客户端 ID(从步骤 a.)和证书获取访问令牌，我连接的资源是https://database.windows.net".请参考示例代码 -

c.In code level I am trying to get a access token by using Client ID( obtained from step a.) and certificate and the resource I am connecting to is "https://database.windows.net". Please refer the sample code - 

string authority = string.Format(System.Globalization.CultureInfo.InvariantCulture, "https://login.windows.net/{0}",
                "xxxx.onmicrosoft.com");
            var authContext = new AuthenticationContext(authority);        
            AuthenticationResult result = null;
            result = await authContext.AcquireTokenAsync("https://database.windows.net", AssertionCert);
            token = result.AccessToken;

d.我能够检索访问令牌，但是当我尝试打开 SQL 连接时.我收到上述错误.

d. I am able to retrieve the access token but when I am trying to open the SQL connection.I am getting the above said error.

        sqlBuilder["Data Source"] = serverName;
        sqlBuilder["Initial Catalog"] = databaseName;
        sqlBuilder["Connect Timeout"] = 30;

        string accesstoken = GetAccessToken();

        using (SqlConnection connection = new SqlConnection(sqlBuilder.ConnectionString))
        {
            try
            {
                connection.AccessToken = accesstoken;
                connection.Open();
            }
            catch (Exception ex)
            {

            }
        }

对此的任何帮助都会非常有帮助.

Any help on this would be really helpful.

推荐答案

这里有一些关于我如何解决这个问题的粗略和现成的代码.我必须提供主机租户(参见下面的代码.

Here is some rough and ready code on how I solved this. I had to supply the host tenant (see in the code below.

    private async Task<string> SqlServerVersion()
    {
        var provider = new AzureServiceTokenProvider();
        var token = await provider.GetAccessTokenAsync("https://database.windows.net/", "<host tenant>.onmicrosoft.com").ConfigureAwait(false);

        SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder
        {
            csb.DataSource = "<your server>.database.windows.net";
            csb.InitialCatalog = "<your database>";
        };

        using (var conn = new SqlConnection(csb.ConnectionString))
        {
            conn.AccessToken = token;
            await conn.OpenAsync().ConfigureAwait(false);

            using (var sqlCommand = new SqlCommand("SELECT @@VERSION", conn))
            {
                var result = await sqlCommand.ExecuteScalarAsync().ConfigureAwait(false);
                return result.ToString();
            }
        }
    }

相关文章