SQL AAD Token Based Authentication - Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON

Question

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. Created an AAD application( ex : MTSLocal ) with certificate based authentication.

b. Added permission to the above AAD in SQL.

CREATE USER [MTSLocal] FROM external provider;

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. 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.

without seeing some code and other info about how you're doing this it's not going to be easy for anyone to answer you meaningfully. See stackoverflow.com/help/how-to-ask for guidance about what to put in your question. — ADyson
sorry for that.. I used the code sample from - stackoverflow.com/questions/38862095/… But in the connection.open() line of code I am getting above said exception. connection.AccessToken = accessToken;// I am able to get the access token successfully connection.Open(); — Pritish Mohapatra
Please describe your architecture more specifically. Client, Azure DC, SQL server and any web server or other type of application server which may be in front of the SQL server. — T-Heron
please put your code, inside your question. And also information about your setup, as mentioned by the comment above. Ok you took the code from another question, but that question could get deleted or amended in future. For this question to make sense, all the necessary information must be within it. Just to warn you, questions which don't have all the required info within them can legitimately be closed off by the moderators - see stackoverflow.com/help/on-topic for the reasons. — ADyson
Can you please edit your question with the information found. It is hard to read inside a comment — T-Heron

Murray Foxcroft Murray Foxcroft · Accepted Answer · 2018-05-14T15:36:13

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 Token Based Authentication - Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON

2 Answers