Azure-sdk-for-net: [BUG] Microsoft.Azure.Services.AppAuthentication gives unusable access token

[IdeaHunter]

Describe the bug

Unable to use MSI on net core 2.2 azure windows app service when using Microsoft.Azure.Services.AppAuthentication , whenever with the same configuration if i use bare MSI_ENDPOIND and MSI_SECRET in my code to get access token , the code starts working. Also if i omit setting access token completely the error message is changes(so library is doing something)
The code giving me following problem

Exception or Stack Trace
Exception message:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Stack trace:

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at GraphQL.WebApi.Startup.<>c.<ConfigureServices>b__4_7(IServiceProvider serviceProvider) 

To Reproduce

i have tried to follow tutorial on setting up MSI to access data in Azure SQL Server Database
First, i created app service and assigned system managed identity

Second i have created Azure AD Group and added system managed identity to a group

Then i connected to azure database right way(tm) and added Azure AD Group and granted it admin rights db_datareader db_datawriter db_ddladmin

Then i took a connection string from azure admin panel and modified it to be usable for MSI(see code below)

Then i tried to run code and it seems like it produces valid jwt token(checked under debugger)

And when i execute following command

use master;
SELECT *
FROM sys.event_log
where event_type = 'connection_failed'

on sql server im getting following result

testing 2019-07-12 18:30:00.0000000 2019-07-12 18:35:00.0000000 connectivity    connection_failed   4   login_failed_for_user   2   2   Login failed for user.

Code Snippet

c# var provider = new AzureServiceTokenProvider(); var token = provider.GetAccessTokenAsync("https://database.windows.net/", "xxxxxxxxx.onmicrosoft.com").Result; var sqlConnection = new SqlConnection("Data Source=xxxxx.database.windows.net;Initial Catalog=testing"); sqlConnection.AccessToken = token; sqlConnection.Open()

Expected behavior
The library should produce valid access token that can be understanded by Azure

Setup (please complete the following information):

• OS: Windows(Azure App Service)
• IDE : VS2019 (remote debugging)
• Version of the Library used: (1.2.0 for net core)

Additional context
Add any other context about the problem here.

Information Checklist
Kindly make sure that you have added all the following information above and checkoff the required fields otherwise we will treat the issuer as an incomplete report

• [x] Bug Description Added
• [x] Repro Steps Added
• [x] Setup information Added

this issue if copied from https://github.com/dotnet/SqlClient/issues/138 because initially SqlClient library were under suspicion but it turned out it works just fine under net core and only token produced by AppAuthentication is not understood by Azure

[loarabia]

Hi @IdeaHunter thanks for sending us the details about this issue. I've tagged this to try and get it routed to the right team.

[Skylertodd]

This sounds similar to an issue I am experiencing although my symptoms are slightly different.

When I get the failure to login it looks slightly different

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa@aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa'. (obscured from original value)
  at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()

But the symptoms on the SQL side are the same.

I was able to work around it by granting the MSI a user and role on the database directly.

CREATE USER [ServiceIdentityName] FROM EXTERNAL PROVIDER;
ALTER ROLE DB_OWNER ADD MEMBER [ServiceIdentityName];

Hope the workaround works for you as well, but it would be nice to be able to configure without having to set explicit permissions and use groups instead.

[IdeaHunter]

@Skylertodd yeah, and if you look at msi vs non msi jwt you can see that that the group claims not included in token for a non msi

[TechWatching]

It seems that I have the same problem. My Azure Function with MSI activated queries my database without any issue when running in Azure but when I try to run it from Visual Studio, I also have the Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. message. Even if my user logged in Visual Studio belongs to the same Azure AD Group than my Azure Function. What is strange is I have no problem querying the database from Azure Data Studio when connected with this user.

The workaround of setting the permissions directly to my AD User did not work for me unfortunately.

[rickvdbosch]

I'm running into the same issue when running this code local (in a Console App or in an Azure Function):

var tokenProvider = new AzureServiceTokenProvider();
using (var connection = new SqlConnection(CONNECTIONSTRING))
using (var command = new SqlCommand(QUERY, connection))
{
    connection.AccessToken = await (tokenProvider.GetAccessTokenAsync("https://database.windows.net/"));
    await connection.OpenAsync();
    var result = (await command.ExecuteScalarAsync()).ToString();

    return new OkObjectResult(result);
}

I've ran az login and I _am_ able to connect to for instance Key Vault or Storage using the same Managed Identity, both in the cloud and locally. I've set an AD Admin for the SQL Server and both my user account and the managed identity are in the same AD group for SQL Admins.

I've deployed the same code to Azure; it runs fine there.

[rickvdbosch]

UPDATE
I discussed this issue with some people, in the end a solution came up that works!

The main issue in my case was that my subscription (and my user) is a Microsoft account (Outlook). Because of this, you need to specify the tenantId in the GetAccessTokenAsync() call.

Apparently, for managed identities you do not have to specify the tenantId. With a user, it's a good idea to explicitly specify it. In case of a personal MS account, specifying it is mandatory.

The steps I took to fix this issue:

• I’ve created an Active Directory Group (let’s call it _AdminGroup_)
• Both my user account and the Azure Function App’s Managed Identity are in _AdminGroup_
• _AdminGroup_ is set as the Active Directory Admin for the Azure SQL Server
• I’ve created a database user _AdminGroup_ in the database I want to connect to
• I created a user for _AdminGroup_ and added it to a role in the database, using:

CREATE USER [AdminGroup] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [AdminGroup];
ALTER ROLE db_datawriter ADD MEMBER [AdminGroup];
GO

My current code (sort of):

var tokenProvider = new AzureServiceTokenProvider();

using (var connection = new SqlConnection(CONNECTIONSTRING))
using (var command = new SqlCommand(QUERY, connection))
{
    connection.AccessToken = await tokenProvider.GetAccessTokenAsync("https://database.windows.net/", "<YOUR_TENANT_ID>");

    await connection.OpenAsync();
    var result = (await command.ExecuteScalarAsync()).ToString();

    return new OkObjectResult(result);
}

This solution has been tested and works both when specifying the tenantId (or Directory ID, the tenant's GUID) and the 'onmicrosoft'-name (xxx.onmicrosoft.com). It works in Azure and locally.

[TechWatching]

This solution works fine for me :) Thanks @rickvdbosch

Apparently not only the Microsoft account are concerned, I was using an account from another directory which is a guest on the directory of my database and I had the same issue. I wanted to test with other accounts but for a reason I don't understand Visual Studio keeps using the same account as the managed identity whatever account I specify in Visual Studio

[rickvdbosch]

Hi @TechWatching,

I wanted to test with other accounts but for a reason I don't understand Visual Studio keeps using the same account as the managed identity whatever account I specify in Visual Studio

That's the az login answer I posted on stack overflow first. I'm pretty sure that'll fix your issue. When I run an Azure Function locally, it uses the user that is logged in with Azure CLI, not the one that is selected in Visual Studio.

[TechWatching]

It is strange, it seems making it work (on my computer at least) is a combination of az login and account set in "Azure Service Authentication" Visual Studio options (just logout and login with another azure account was not enough). Anyway, that's another issue. Thanks for your help. I think this issue can be closed. @IdeaHunter did you manage to make it work as well ?

[IdeaHunter]

@TechWatching
nope, it cant be closed, as per microsoft documentation it should work without pointing to specific tenant id - what you have described is essentially workaround

Also my issue is different from @rickvdbosch one - i have problems with making it work on azure but rick didnt:

I've deployed the same code to Azure; it runs fine there.

Also, isnt point of MSI to successfully authenticate users within azure directory in an automatic way?

[mkowalskigps]

I'm also having the same issue that @TechWatching was having e.g. when running from an App Service, authentication via a token issued for the managed identity works, but anything that I'm doing locally fails with Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. I've verified the JWT issued when running locally looks good e.g. proper tenant and groups and that there are no firewall issues.

One thing I've noticed is that I don't see any of my local connection attempts via the application code in the SQL security audit logs.

[IdeaHunter]

Microsoft.Azure.Services.AppAuthentication 1.5.0 is now producing valid tokens that works as expected.