0

we have a nightly process (all running on Azure VMs) which is largely a collection of powershell scripts to update DBs in our TEST environment. Quite frequently we hit issues like below. We have around 25 DBs to update and at some random point in the process we'll have sporadic errors trying to use Azure Managed Identity Authentication. Below shows how it all works perfectly for one DB, but then fails on the next DB even though run from the same process on the same Azure VM and connecting to the same SQL Managed Instance where the source VM's Managed Identity is configured to have full sysAdmin rights.

I've previously been advised in effect, "these services just aren't that reliable so you just need to wrap all such logic in retry logic and hope you eventually get lucky". The frequency with which we see these issues is making me wonder if there is more going on than that and if anyone has seen this or has other ideas? If the nature of these services are such that it just isn't that reliable to work every time, you'd think they would add the retry logic in the core libraries such as Microsft.Data.SqlClient or the underlying Azure.Identity libraries vs expecting developers to detect these issues higher in the logic chain and respond?

Anyway, I'm not sure if anyone else has seen this or I'm just unlucky :)

[06:51:56][Step 5/6] ========================================================================
[06:51:56][Step 5/6] Updating DB zzzzz_Regression (zzzzzRegression) on server  xxxxx-tcc-sql.2222.database.windows.net (xxxxx-TCC-SQL)
[06:51:56][Step 5/6] ========================================================================
[06:51:57][Step 5/6] 6:51:56 AM:Using env file from command line: .\Package\BuildScripts\Configs\mmmmmTestAzure.fmenv.xml
[06:51:57][Step 5/6] 6:51:57 AM:Loaded environment: mmmmmTestAzure
[06:51:57][Step 5/6] 6:51:57 AM:Applying change scripts.
[06:52:09][Step 5/6] 6:52:09 AM:Creating functions.
[06:52:27][Step 5/6] 6:52:27 AM:Creating triggers.
[06:52:34][Step 5/6] 6:52:34 AM:Creating stored procedures.
[06:54:08][Step 5/6] 6:54:07 AM:Database FM3OMS_Regression upgraded successfully.
[06:54:08][Step 5/6] Inializing Report Urls on Database Id zzzzzRegression
[06:54:14][Step 5/6] ========================================================================
[06:54:14][Step 5/6] Updating DB yyyyy (yyyyy) on server  xxxxx-tcc-sql.2222.database.windows.net (xxxxx-TCC-SQL)
[06:54:14][Step 5/6] ========================================================================
[06:54:14][Step 5/6] 6:54:14 AM:Using env file from command line: .\Package\BuildScripts\Configs\mmmmmTestAzure.fmenv.xml
[06:54:15][Step 5/6] 6:54:14 AM:Loaded environment: mmmmm
[06:54:15][Step 5/6] 6:54:14 AM:Applying change scripts.
[06:54:28][Step 5/6] 6:54:28 AM:Creating functions.
[06:54:52][Step 5/6] 6:54:52 AM:Creating triggers.
[06:55:10][Step 5/6] 6:55:03 AM:Creating stored procedures.
[06:57:39][Step 5/6] 6:57:39 AM:Database yyyyy upgraded successfully.
[06:57:42][Step 5/6] Inializing Report Urls on Database Id yyyyy
[06:58:18][Step 5/6] Initialize-FMReportUrls : 6:58:07 AM:Using env file from command line: 
[06:58:18][Step 5/6] .\Package\BuildScripts\Configs\mmmmmTestAzure.fmenv.xml 6:58:13 AM:Loaded environment: mmmmmTestAzure 
[06:58:18][Step 5/6] System.AggregateException: One or more errors occurred. ---> Azure.Identity.CredentialUnavailableException: 
[06:58:18][Step 5/6] ManagedIdentityCredential authentication unavailable. No Managed Identity endpoint found.    at 
[06:58:18][Step 5/6] Azure.Identity.ManagedIdentityClient.<AuthenticateAsync>d__12.MoveNext() --- End of stack trace from previous location 
[06:58:18][Step 5/6] where exception was thrown ---    at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)    at 
[06:58:18][Step 5/6] System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)    at 
[06:58:18][Step 5/6] Azure.Identity.ManagedIdentityCredential.<GetTokenImplAsync>d__9.MoveNext() --- End of stack trace from previous 
[06:58:18][Step 5/6] location where exception was thrown ---    at Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex)  
[06:58:18][Step 5/6]   at Azure.Identity.ManagedIdentityCredential.<GetTokenImplAsync>d__9.MoveNext() --- End of stack trace from previous 
[06:58:18][Step 5/6] location where exception was thrown ---    at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task 
[06:58:18][Step 5/6] task)    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)    at 
[06:58:18][Step 5/6] Azure.Identity.ManagedIdentityCredential.<GetTokenAsync>d__7.MoveNext() --- End of stack trace from previous location 
[06:58:18][Step 5/6] where exception was thrown ---    at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)    at 
[06:58:18][Step 5/6] System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)    at 
[06:58:18][Step 5/6] System.Threading.Tasks.ValueTask`1.get_Result()    at 
[06:58:18][Step 5/6] Microsoft.Data.SqlClient.ActiveDirectoryAuthenticationProvider.<AcquireTokenAsync>d__19.MoveNext()    --- End of inner 
[06:58:18][Step 5/6] exception stack trace ---    at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)  
[06:58:18][Step 5/6]   at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)    at 
[06:58:18][Step 5/6] System.Threading.Tasks.Task`1.get_Result()    at 
[06:58:18][Step 5/6] Microsoft.Data.SqlClient.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo)    at 
[06:58:18][Step 5/6] Microsoft.Data.SqlClient.SqlInternalConnectionTds.OnFedAuthInfo(SqlFedAuthInfo fedAuthInfo)    at 
[06:58:18][Step 5/6] Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, 
[06:58:18][Step 5/6] BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)    at 
[06:58:18][Step 5/6] Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, 
[06:58:18][Step 5/6] BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)    at 
[06:58:18][Step 5/6] Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)    at 
[06:58:18][Step 5/6] Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, 
[06:58:18][Step 5/6] SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean 
[06:58:18][Step 5/6] isFirstTransparentAttempt, Boolean disableTnir)    at 
[06:58:18][Step 5/6] Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, 
[06:58:18][Step 5/6] SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential 
[06:58:18][Step 5/6] credential, TimeoutTimer timeout)    at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer 
[06:58:18][Step 5/6] timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString 
[06:58:18][Step 5/6] newSecurePassword, Boolean redirectedUserInstance)    at
Tony Campney
  • 205
  • 1
  • 3
  • 7
  • Looks like it's returning some kind of error, rather than just timing out. Can you use Fiddler or Wireshark to see the exact response? – Charlieface Jun 09 '22 at 14:01
  • 2
    You can add extra parameter in your connectionstring to retry: `Connection Timeout=30;ConnectRetryCount=3;ConnectRetryInterval=10;` – Thomas Jun 09 '22 at 20:03
  • 1
    Thanks we'll try the options to the connection string. As the issue is only randomly reproducible, not sure if/when I'll be able to confirm it fixed the issue, but definitely worth trying! Thx! – Tony Campney Jun 10 '22 at 12:33

1 Answers1

0

This is a problem with the Azure VM, or an issue with Microsoft.Data.SqlClient.

ManagedIdentityCredential authentication unavailable. No Managed Identity endpoint found.

This message claims that the local http endpoint that Azure provides when you enable Managed Identity on a VM is not available to hand out access tokens.

You should

  1. make sure you're current on Microsoft.Data.SqlClient,
  2. open an Azure Support incident and/or create an issue in the SqlClient repo, or perhaps the Azure.Identity repo and
  3. switch to SQL Auth, or non-MSI AAD auth (eg ActiveDirectoryServicePrincipal where you use the ClientId as the user name and a Client Secret as the password or ActiveDirectoryDefault which supports supplying the ClientID and ClientSecret in environment variables) as a workaround.
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67