Allow connecting to Azure SQL database using AAD auth

[sethusrinivasan]

If Azure AAD is only way to connect to SQL database, it may not be possible to use mssql-cli. Please add support for Azure AAD auth & add sample command line in documentation

Related: Azure AAD auth: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication

[pensivebrian]

Unfortunately, mssql-cli does not currently support AAD auth. This is due to a limitation of SqlClient on .NET Core, which is used by the Microsoft/sqltoolsservice and mssql-cli to connect to SQL Server. There are plans to support this feature in SqlClient, however there aren't any timelines that I'm aware of that I can share.

The issue tracking AAD auth support in SqlClient on .NET Core is below. Please upvote. https://github.com/dotnet/corefx/issues/8807

[andreesteve]

@pensivebrian - do you know if SQLClient has rolled out AAD support for .net core since? I am trying to find a link to any upstream issue/worktitem associated to that, do you know which repo it would be in?

EDIT: maybe this one? https://github.com/dotnet/SqlClient/issues/10 - doesn't look very promising :(

[Parasrah]

It looks as if this has since been shipped in SqlClient 1.0.19239.1. Looking around, it seems lacking AAD support is a deal breaker for a lot of corporate environments

[danbrad]

Are there any plans to deliver this? It feels like a fairly fundamental feature that is missing.

[TarekSalha]

any updates on this? I wanted to update my net6.0 function app from the legacy package Microsoft.Azure.Services.AppAuthentication that is currently doing:

// TODO: Migrate to Azure.Identity if possible, because Microsoft.Azure.Services.AppAuthentication is deprecated
                var tokenProvider = new AzureServiceTokenProvider();
                _log.LogInformation("retrieving sql access token...");
                string accessToken = await tokenProvider.GetAccessTokenAsync("https://database.windows.net/", Environment.GetEnvironmentVariable("Sql:TenantId"));
                var connection = new SqlConnection(_connectionString)
                {
                    AccessToken = accessToken
                };
                connection.Open();

When trying to use System.Data.SqlClient native auth like this:

string ConnectionString = @"Server=demo.database.windows.net; Authentication=Active Directory Default; Database=testdb;";

using (SqlConnection conn = new SqlConnection(ConnectionString)) {
    conn.Open();
}

it fails with error message, that auth keyword is not supported

If I work around this using Azure.Identity methods, it is getting incredibly slow:

var tokenProvider = new DefaultAzureCredential();
                _log.LogInformation("retrieving sql access token...");
                var tokenRequestContext = new Azure.Core.TokenRequestContext(scopes: new string[] { "https://database.windows.net//.default" }, tenantId: Environment.GetEnvironmentVariable("Sql:TenantId"));
                var accessToken = await tokenProvider.GetTokenAsync(tokenRequestContext);
                var connection = new SqlConnection(_connectionString)
                {
                    AccessToken = accessToken.Token
                };
                connection.Open();

Especially when using startup class and injecting the sql client, I would expect a reuse of the token. Instead, it seems, the code is getting a new token with every new call (it takes seconds to finish every single one)

@ellbosch: What is the suggested method for accessing Azure SQL in net6.0?