[Known Issue] Increased deployment time due to MSAL throttling

[udeeshagautam]

• SqlPackage or DacFx Version: All 160
• .NET Framework (Windows-only) or .NET Core: Both
• Environment (local platform and source/target platforms): AAD login

When: Intermittent. Using AAD username password with SqlPackage Symptoms: Increased deployment time. Following exception and retry log if in log file using /df parameter

Microsoft.Data.Tools.Diagnostics.Tracer Warning: 0 : 2022-04-12T17:11:58 : Retry occurred: session: 00000000-0000-0000-0000-000000000000; attempt - 8; delay - 00:01:00; exception - "System.AggregateException: One or more errors occurred. ---> Microsoft.Identity.Client.MsalUiRequiredException: AADSTS50196: The server terminated an operation because it encountered a client request loop. Please contact your app vendor. Trace ID: 4db7000d-e3cb-4d49-9b6d-8476ce410e00 Correlation ID: 1ea0fd86-6c90-4285-a9c8-a31a0f6b7939 Timestamp: 2022-04-13 00:11:59Z at Microsoft.Identity.Client.OAuth2.OAuth2Client.ThrowServerException(HttpResponse response, RequestContext requestContext) at Microsoft.Identity.Client.OAuth2.OAuth2Client.CreateResponse[T](HttpResponse response, RequestContext requestContext) at Microsoft.Identity.Client.OAuth2.OAuth2Client.<ExecuteRequestAsync>d__11`1.MoveNext()

Reason DacFx uses Microsoft.Data.SqlClient which further uses MSAL for AAD connections. The throttling logic for MSAL has changed in newer versions causing this issue. Ref: https://github.com/AzureAD/microsoft-authentication-library-for-dotnet/issues/2808

Did this occur in prior versions? If not - which version(s) did it work in? This is only for 160 versions (DacFx/SqlPackage/SSMS/Azure Data Studio)

[llali]

This is a known issue when using AAD SQL Password auth. The workaround is to use different authentication (https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal)

Here's the example how to use the service principal auth: sqlpackage.exe /a:"Publish" /tcs:"Data Source=server;Initial Catalog=testdb;User ID=...;Password=...;Pooling=True;Multiple Active Result Sets=False;Trust Server Certificate=True;Authentication=ActiveDirectoryServicePrincipal" /sf:"test.dacpac"

[llali]

A temporary fix has been added to sqlpackage.exe for this issue. Setting environment variable (CONNECTION_POOLING_ENABLED default false) to true will reduce the number of connections requesting token and will reduce the chance of getting throttled. https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver16

[dzsquared]

In the Import/Export service the environment variable CONNECTION_POOLING_ENABLED has been set to true, resulting in connection pooling and no errors from connection throttling by MSAL.

[siwekpawel]

Hello @dzsquared Has this change also been made in the Linux version of the sqlpackage? I noticed the same issue during bacpac file import:

*** Failed to authenticate the user [email protected] in Active Directory (Authentication=ActiveDirectoryPassword). Error code 0xinvalid_grant Your app has been throttled by AAD due to too many requests. To avoid this, cache your tokens see https://aka.ms/msal-net-throttling

SqlPackage version: 16.1.6374.0 Downloaded from: https://aka.ms/sqlpackage-linux

In the Windows application, this problem does not occur.

Best Regards, Pawel