mssql-cli
mssql-cli copied to clipboard
Allow connecting to Azure SQL database using AAD auth
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
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
@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 :(
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
Are there any plans to deliver this? It feels like a fairly fundamental feature that is missing.
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?