[BUG] SMO ServerConnection to AzureSqlDatabase using Active Directory Default auth

[sidesw1pe]

To Reproduce

After starting a Cloud Shell session, I connect my account and try to establish a connection to an Azure SQL Database.
I use (Get-AzAccessToken).UserId to check my account is connected.
I am using the SqlServer PowerShell module (updated to the latest version 22.1.1, since the version installed on Cloud Shell is 2 months old and also I want to use the same version I am using locally. In fact I get the same problem regardless of whether I update the SqlServer module or not.
My connection is using the ServerConnection class (SMO) as explained here, and using Active Directory Default authentication.
The SMO library used by the v22.1.1 SqlServer PowerShell module is v17.13.0.

Connect-AzAccount -DeviceCode # connect my account
Update-Module SqlServer # Update the SqlServer module to the latest version, currently 22.1.1
Import-Module SqlServer -MinimumVersion "22.1.1" # Import the SqlServer module, latest version
$newcon = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$newcon.ConnectionString = "Data Source=myserver.database.windows.net;Initial Catalog=mydb;Encrypt=False;Trust Server Certificate=False;Authentication=ActiveDirectoryDefault"
$newcon.Connect()

Observed Behavior

After a short period I get an error message and the connection fails to connect.

MethodInvocationException: Exception calling "Connect" with "0" argument(s): "Failed to connect to server myserver.database.windows.net."

InnerException : Microsoft.Data.SqlClient.SqlException (0x80131904): The operation was canceled.
                  ---> System.Threading.Tasks.TaskCanceledException: The operation was canceled.
                    at Azure.Core.CancellationHelper.ThrowOperationCanceledException(Exception innerException, CancellationToken cancellationToken)
                    at Azure.Core.CancellationHelper.ThrowIfCancellationRequested(CancellationToken cancellationToken)
                    at Azure.Core.Pipeline.ResponseBodyPolicy.ThrowIfCancellationRequestedOrTimeout(CancellationToken originalToken, CancellationToken timeoutToken, Exception inner,
                 TimeSpan timeout)
                    at Azure.Core.Pipeline.ResponseBodyPolicy.ProcessAsync(HttpMessage message, ReadOnlyMemory`1 pipeline, Boolean async)
                    at Azure.Core.Pipeline.LoggingPolicy.ProcessAsync(HttpMessage message, ReadOnlyMemory`1 pipeline, Boolean async)
                    at Azure.Core.Pipeline.RedirectPolicy.ProcessAsync(HttpMessage message, ReadOnlyMemory`1 pipeline, Boolean async)
                    at Azure.Core.Pipeline.RetryPolicy.ProcessAsync(HttpMessage message, ReadOnlyMemory`1 pipeline, Boolean async)
                    at Azure.Core.Pipeline.RetryPolicy.ProcessAsync(HttpMessage message, ReadOnlyMemory`1 pipeline, Boolean async)
                    at Azure.Identity.ManagedIdentitySource.AuthenticateAsync(Boolean async, TokenRequestContext context, CancellationToken cancellationToken)
                    at Azure.Identity.ManagedIdentityClient.AuthenticateAsync(Boolean async, TokenRequestContext context, CancellationToken cancellationToken)
                    at Azure.Identity.ManagedIdentityCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
                    at Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex, String additionalMessage)
                    at Azure.Identity.ManagedIdentityCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
                    at Azure.Identity.ManagedIdentityCredential.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.ProviderBase.DbConnectionClosed.TryOpenConnection(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.Data.SqlClient.SqlConnection.Open()
                    at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnectImpl()
                    at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect()
                    at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
                 ClientConnectionId:e9e29fbe-158c-43c8-8f60-9441402b92fc
                 ClientConnectionId before routing:0789793d-0e0e-4581-a163-8298de0f2665
                 Routing Destination:e5fd1a19bb6e.tr7243.australiaeast1-a.worker.database.windows.net,11024
HelpLink       :
Source         : Microsoft.SqlServer.ConnectionInfo
HResult        : -2146233087
StackTrace     :    at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
                    at CallSite.Target(Closure, CallSite, Object)

Expected behavior

I expect that no error will be returned, and that my connection will be successful.

Is this specific to Cloud Shell?

I'm using PowerShell v7.3.4 (same as Cloud Shell) on my Windows 10 desktop. I have the same version of the SqlServer PowerShell module installed, v22.1.1. When I run the same commands, the expected behaviour occurs. I use (Get-AzAccessToken).UserId to confirm my account is connected (same account with Cloud Shell).

Interface information

Via Windows Terminal on Windows 10.

Additional context

If I change the authentication method from "Active Directory Default" to "Active Directory Password" and modify the connection string to this, "Data Source=myserver.database.windows.net;Initial Catalog=mydb;User ID=user@mydomain.com;Password=mYp@sSw0Rd!;Connect Timeout=30;Encrypt=False;Trust Server Certificate=False;Authentication=ActiveDirectoryPassword", the connection is successful.
However naturally I do want to use passwordless authentication (Active Directory Default).

EDIT: I decided to try the Cloud Shell in the Azure Portal just now, since I had been subbornly sticking with the Terminal version all along. And I get the expected behaviour from there. Furthermore it works regardless of whether I connect my account or not, I did not realise this. So the issue seems to be isolated specifically to the Windows Terminal Cloud Shell?

[JacktnielFD20]

Check network connectivity: Make sure that Azure Cloud Shell has access to the Internet and that there are no network restrictions that might be blocking the connection to your Azure SQL database.Update the SqlServer module: Although you mentioned that you have already updated the SqlServer module to the latest version, there might be some incompatibility or specific issue that has not been addressed yet. Try using an older version of the SqlServer module to see if that fixes the problem.Try different authentication methods: If the connection works with "Active Directory Password" but not with "Active Directory Default", there might be a problem with the way authentication is done. Try using different supported authentication methods to see if that resolves the issue.Check your account settings: Make sure your Azure account has the proper permissions to access the SQL database and perform theauthentication using the selected method.Contact Azure support: If you've tried the above solutions and still can't resolve the issue, I would recommend contacting Azure support. They will have access to the most up-to-date information and tools to help you resolve your specific Azure Cloud Shell issue.

[sidesw1pe]

Thanks for the suggestions. There seems to be a difference between the terminal-based Cloud Shell vs the Cloud Shell in the Azure portal, based on the edit I added to the Additional context in my original post. I don't see that is my specific issue.

[theJasonHelmick]

Hi @sidesw1pe - Thank you for reporting this issue. Can you confirm, this works when you are logged into the portal, but does not work in Terminal? Can you also confirm that the account you are using with terminal is the same account you use for portal? Could you supply the output for both portal and terminal of the following command: az account show

[sidesw1pe]

Hi @theJasonHelmick ,

Azure Cloud Shell (Terminal)

I am logged on to my desktop (laptop) with username@domain.com (Azure AD account). I start a new Azure Cloud Shell session via Windows Terminal. It prompts me to visit https://microsoft.com/devicelogin and enter the provided code. I do this, select my account, and close the tab. Back at the Cloud Shell, and it prompts me to select my Tenant, which I do (in my case there are 2 tenants to choose from, so I naturally select the one associated with the Azure SQL Database I am interested in). After the authentication process completes I am left at the command prompt. I run the command (Get-AzAccessToken).UserId and it returns MSI@***** (where ***** is a 5 digit number).

Azure Cloud Shell (Azure Portal)

I log in to the portal using my Azure AD account username@domain.com. This is the same user as I used above. I open the Azure Cloud Shell by clicking on "Cloud Shell" at the top. After it authenticates I am left at the command prompt. I run the command (Get-AzAccessToken).UserId and it returns MSI@***** (where ***** is a 5 digit number). This is the same number as above.

In both shells above, I run the following commands:

Import-Module SqlServer
$newcon = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$newcon.ConnectionString = "Data Source=mysqlserver.database.windows.net;Initial Catalog=mydb;Encrypt=False;Trust Server Certificate=False;Authentication=ActiveDirectoryDefault"
$newcon.Connect()

From the Terminal Cloud Shell, after a brief period, I get this error:

MethodInvocationException: Exception calling "Connect" with "0" argument(s): "Failed to connect to server mysqlserver.database.windows.net."

After getting the error, I ran Get-Error and saved the output, in case it is of use.
connect_error.txt

From the Portal Cloud Shell it connects right away (no error is given). I can then, for example, use the connection to create a new server object and get details back from the server.

While documenting this for my response, I noticed that after I get a successful connection from the Portal Cloud Shell, the connection then succeeds from the Terminal Cloud Shell. It's not just a timing issue. I had tried repeatedly from my Terminal, even with a 10 minute wait in between, and consistently got the error message mentioned. I then tried the Portal Cloud Shell, and after connecting successfully, I immediately retried my Terminal Cloud Shell, and it connected right away. I exited from both Cloud Shells, and reauthenticated with my Terminal Cloud Shell, and I could still successfully connect to my SQL db. However after waiting for some time, say 30 minutes or more, the original problem would return.

Here is the output from az account show. It is identical in both Cloud Shells, Terminal and Portal.

{
  "environmentName": "AzureCloud",
  "homeTenantId": "94fd9d0a-7823-48d8-be50-0c6559650cc9",
  "id": "16afc5ef-0971-4cf2-bcdc-25a5e8f4e189",
  "isDefault": true,
  "managedByTenants": [],
  "name": "mysub",
  "state": "Enabled",
  "tenantId": "94fd9d0a-7823-48d8-be50-0c6559650cc9",
  "user": {
    "cloudShellID": true,
    "name": "username@domain.com",
    "type": "user"
  }
}