基于SQL AAD令牌的身份验证-用户'NT AUTHORITY \ ANONYMOUS LOGON登录失败

问题描述

要求-我正在尝试从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.

a.创建了具有基于证书的身份验证的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();
            }
        }
    }

这篇关于基于SQL AAD令牌的身份验证-用户'NT AUTHORITY \ ANONYMOUS LOGON登录失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！