SQL AAD Token Based Authentication - Login failed

问题:

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.

回答1:

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

回答2:

The Application Registered in the AAD should be added to the users list of the DB and respective roles should be given to DB USER.

For suppose the name of the App registered is "App_AAD_Register_Name". add this user to the corresponding DB like executing the below query. With this the user will be added to Principal Users list of the DB server.

CREATE USER [App_AAD_Register_Name] FROM EXTERNAL PROVIDER.

Create some generic Role like below

CREATE ROLE [RoleUser] GO GRANT SELECT ON SCHEMA :: dbo TO [RoleUser] GO GRANT INSERT ON SCHEMA :: dbo TO [RoleUser] GO

Once Role is created and respective permissions are given, assign the role to the user created in the first step.

EXEC sp_addrolemember N'RoleUser', N'App_AAD_Register_Name'.

Once all these steps are done you will be able to connect to DB with the token.

These steps worked for me.

回答3:

Have you checked whether you have signed into Visual Studio? If you are not, then you could get this error. There should be an account configured for Azure Service Authentication. Once you sign into an account in Visual Studio this will be automatically taken care. This had solved my issue.