Open cghgreg opened 1 year ago
@cghgreg We will look into this and will get back to you.
@cghgreg How are you able to get token with Azure.Identity.DefaultAzureCredential in context of Azure function App, The exception shows visual studio credential fetch. Are you trying to debug App remotely?
The order in which default credential work is in the link https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver16#using-active-directory-default-authentication. You have to make sure Azure function app environment is configured correctly to pick up the credentials correctly for connections. How are you able to connect manually successfully to the server?
No I get this error when trying to debug locally through Visual Studio. I never got it working locally so I never tried it remotely.
Code along these lines seems to work just fine with both EF6 and EF Core (this is from my EF6 implementation).
For EF6, I was following instructions from a blog about using the AppAuthentication package's SQL authentication provider and Active Directory Interactive authentication, which worked sometimes but would sometimes get stuck in some kind of deadlock. The code below seems to be more reliable.
When I have this run locally, it will use VisualStudioCredential, and when on my VM/App Service/Function App with a Managed Identity it will autheticate as that identity, just as I expect.
private static Azure.Core.AccessToken accessToken = default;
private static readonly object lockObj = new object();
public DataContext(string nameOrConnectionString)
: base(nameOrConnectionString)
{
((SqlConnection)this.Database.Connection).AccessToken = GetToken();
}
private static string GetToken()
{
// Ensure waiting threads don't get another token if one was just fetched
lock (lockObj)
{
if (accessToken.ExpiresOn < DateTimeOffset.Now.AddSeconds(5))
{
var credential = new Azure.Identity.DefaultAzureCredential();
accessToken = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" }));
}
}
return accessToken.Token;
}
I'd like to add another stack trace to this bug, it's slightly different but also very similar. I've been dealing with it for months actually. Oddly, only happens on my company laptop, whether I'm using it remotely via ZScaler, or if I'm in the office. It does not ever happen on my Azure VM with the same code. It's also somewhat inconsistent, sometimes the Api code just works. I've yet to find a pattern. There's a theory among my infrastructure colleagues that some switching / routing info could be flapping between correct and incorrect. First, the stack trace.
fail: Microsoft.EntityFrameworkCore.Database.Connection[20004]
An error occurred using the connection to database '<redacted>' on server '<redacted>.database.windows.net'.
fail: Microsoft.EntityFrameworkCore.Query[10100]
An exception occurred while iterating over the results of a query for context type 'AAWorkbench.Api.Data.AAWorkbenchDbContext'.
Microsoft.Data.SqlClient.SqlException (0x80131904): The operation was canceled.
---> System.OperationCanceledException: The operation was canceled.
at System.Threading.CancellationToken.ThrowOperationCanceledException()
at Azure.Identity.VisualStudioCredential.GetProcessStartInfos(VisualStudioTokenProvider[] visualStudioTokenProviders, String resource, TokenRequestContext requestContext, CancellationToken cancellationToken)
at Azure.Identity.VisualStudioCredential.GetTokenImplAsync(TokenRequestContext requestContext, Boolean async, CancellationToken cancellationToken)
at Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex, String additionalMessage)
at Azure.Identity.VisualStudioCredential.GetTokenImplAsync(TokenRequestContext requestContext, Boolean async, CancellationToken cancellationToken)
at Azure.Identity.VisualStudioCredential.GetTokenAsync(TokenRequestContext requestContext, CancellationToken cancellationToken)
at Azure.Identity.DefaultAzureCredential.GetTokenFromSourcesAsync(TokenCredential[] sources, TokenRequestContext requestContext, Boolean async, CancellationToken cancellationToken)
at Azure.Identity.DefaultAzureCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
at Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex, String additionalMessage)
at Azure.Identity.DefaultAzureCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
at Azure.Identity.DefaultAzureCredential.GetTokenAsync(TokenRequestContext requestContext, CancellationToken cancellationToken)
at Microsoft.Data.SqlClient.ActiveDirectoryAuthenticationProvider.AcquireTokenAsync(SqlAuthenticationParameters parameters)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.<>c__DisplayClass147_1.<<GetFedAuthToken>b__1>d.MoveNext()
--- End of stack trace from previous location ---
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo)
at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e)
at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
ClientConnectionId:224ae027-7a86-4cfc-a6d8-727912098f69
Now, interestingly, App Insights logging shows that the code is trying to get a token from the identity IMDS endpoint (MI auth method), and it seems to be connecting, but getting no response, then it backs off, tries again, and eventually throws.
Any help greatly appreciated. Obviously this is used via EF Core. I haven't tried a simpler test case of direct SqlClient.
This looks like an issue with Visual Studio rather than SqlClient - see https://developercommunity.visualstudio.com/t/Version-1770-Preview-20---WebApp-unab/10409641?q=%22a+task+was+cancelled%22&sort=newest and https://github.com/Azure/azure-sdk-for-net/issues/38014 - this looks to be a Visual Studio related issue with Microsoft.Asal.TokenService.exe
Deleting the %LOCALAPPDATA%/.IdentityService
directory worked for me.
Hopefully they will implement a real solution...
Deleting the
%LOCALAPPDATA%/.IdentityService
directory worked for me.
This might have helped, a bit. What I've found since doing that, is the first time after starting the app in VS, I get the same failing behavior as above, if I try it again, it works and then continues to work until the next app restart. Something about the SqlClient / EF Core warmup? Very odd. Also odd that it works perfectly, every time in my Azure VM.
Deleting the
%LOCALAPPDATA%/.IdentityService
directory worked for me.
Also worked for me. Delete folder, restart Visual Studio - voilá 👍
I tried closing VS, deleting %LOCALAPPDATA%/.IdentityService, restarting the computer, logging back into VS, and I still had issues. I ended up opening the VS installer, repairing, and that fixed my problem. So that's a nuclear option in case deleting the .IdentityService folder doesn't work for you.
Update: my problem recurred after repairing VS. And repairing again did NOT fix my issue.
I also tried raphi007fly's suggestion of moving Microsoft.Data.SqlClient to 4.1.1, and that didn't help either.
I have two PCs with Visual Studio installed. 17.7.3 experiences this problem. 17.5.3 running the same code on the same network does not experience this problem.
We are also using Conditional Access with MFA for our accounts. I don't know if that is relevant or not.
Visual Studio is now unusable for about half of our dev team who have upgraded to 17.7.3. We REALLY need a solution.
I also encountered the same problem. Downgrading the Microsoft.Data.SqlClient nuget package from 5.1.1 to 4.1.1 solved the problem for me. Note that I also tried the previous steps of clearing %LOCALAPPDATA%/.IdentityService and repairing VS 2022.
Please see Thomas's comment in a linked thread: https://developercommunity.visualstudio.com/t/Version-1770-Preview-20---WebApp-unab/10409641#T-N10463906
This solution worked for me while on v17.7.3 without having to change any NuGet packages. Basically, add a 30 second connection timeout to your SQL Server connection string.
I've been experiencing the same issue. Updated VS to 17.8.4 and still have the same issue.
Tried deleting the .IdentityService folder and that did not help.
Increasing the Connection Timeout limit to 60s works, but obviously is not really a solution.
Here are some of the references in my .NET 6 Azure Function project:
<PackageReference Include="Azure.Identity" Version="1.10.4" />
<PackageReference Include="Microsoft.ApplicationInsights.WorkerService" Version="2.22.0" />
<PackageReference Include="Microsoft.Azure.Functions.Extensions" Version="1.1.0" />
<PackageReference Include="Microsoft.Data.SqlClient" Version="5.1.4" />
<PackageReference Include="Microsoft.Extensions.Configuration.AzureAppConfiguration" Version="7.0.0" />
<PackageReference Include="Microsoft.IdentityModel.JsonWebTokens" Version="7.2.0" />
<PackageReference Include="Microsoft.NET.Sdk.Functions" Version="4.2.0" />
Let me know if there's any additional info I can provide.
I've started facing this issue only on my dev machine, on dev servers and on my colleagues machine it's not happening.. I've tried to remove and re-add permissions on Azure... I'm on 17.8.7. In those minutes I'm trying to upgrade to 17.9.1
Hello the issue is still happening with 5.2
Microsoft.Data.SqlClient.SqlException (0x80131904): A task was canceled.
---> System.Threading.Tasks.TaskCanceledException: A task was canceled.
at Azure.Identity.AzureCliCredential.RequestCliAccessTokenAsync(Boolean async, TokenRequestContext context, CancellationToken cancellationToken)
at Azure.Identity.AzureCliCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
at Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex, String additionalMessage, Boolean isCredentialUnavailable)
at Azure.Identity.AzureCliCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
at Azure.Identity.AzureCliCredential.GetTokenAsync(TokenRequestContext requestContext, CancellationToken cancellationToken)
at Azure.Identity.DefaultAzureCredential.GetTokenFromSourcesAsync(TokenCredential[] sources, TokenRequestContext requestContext, Boolean async, CancellationToken cancellationToken)
at Azure.Identity.DefaultAzureCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
at Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex, String additionalMessage, Boolean isCredentialUnavailable)
at Azure.Identity.DefaultAzureCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
at Azure.Identity.DefaultAzureCredential.GetTokenAsync(TokenRequestContext requestContext, CancellationToken cancellationToken)
at Microsoft.Data.SqlClient.ActiveDirectoryAuthenticationProvider.AcquireTokenAsync(SqlAuthenticationParameters parameters)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.<>c__DisplayClass148_1.<<GetFedAuthToken>b__1>d.MoveNext()
--- End of stack trace from previous location ---
I found that this issue is happening when I also enable Application Insights in the project. I have a project with net core 8, Microsoft.Data.SqlClient 5.1.5 and Application Insights version 2.22.0 When removing the line AddApplicationInsights from my startup, the issue magically disappears. It looks like threads are blocking each other while obtaining a an access token.
Describe the bug
I have an .NET 6 Azure Function app with EF Core 6 and Microsoft.Data.SqlClient 4.1.0 and I'm trying to connect to an Azure SQL database with Active Directory Default authentication via the connection string. However, I get a 'task canceled' exception when it attempts to fetch the access token. This occurs when debugging the function app from Visual Studio.
I've also tried this in a .net 7 MVC app with EF Core 7 and Microsoft.Data.SqlClient 5.1.1 and ran into the same problem.
Related Stack Overflow question: https://stackoverflow.com/q/72984555/2200576
Stack trace for function app:
Stack trace for MVC app:
To reproduce
Expected behavior
SqlClient is able to get an access token opaquely and connect to the Azure SQL database without additional boilerplate by simply injecting MyDbContext and enumerating a DbSet.
Further technical details
Microsoft.Data.SqlClient version: 4.1.0, 5.1.1 .NET target: net60, net70 SQL Server version: Azure SQL
Additional context When I attempt to fetch a token manually using Azure.Identity.DefaultAzureCredential and attach it to the DbConnection, that works fine; it's only when trying to use it purely with service collection extension methods and "Authentication=Active Directory Default" in the connection string do I run into this issue.