Suggestions not working with AzureSQL and Azure SQL Managed Instance with AAD and MFA

ChrisDurante

Hello,

Using SSMS 18.7 with Red Gate SQL Prompt 10.4.7.16599
Connecting to either Azure SQL Databases or Azure SQL Managed Instance
Authentication Method is Azure Active Directory Universal with MFA

We are not able to get suggestions for any objects, databases, schemas, etc.
The verbose log doesn't really have much information, but there's always a warning "failed to load server:"

2020-11-11 09:15:12.773 -08:00|Information||| [48424] Started task 'Code analysis task <SQLQuery4.sql>'.
2020-11-11 09:15:12.831 -08:00|Information||| [48424] Run 'Code analysis task <SQLQuery4.sql>' completion handler!
2020-11-11 09:15:12.831 -08:00|Information||| [48424] Successfully finishing current task 'Code analysis task <SQLQuery4.sql>'.
<b>2020-11-11 09:15:13.239 -08:00|Warning||| Failed to load server xxxx.database.windows.net</b>
2020-11-11 09:15:13.485 -08:00|Debug||| Closing messages for xxxx.database.windows.net
2020-11-11 09:15:14.385 -08:00|Information||| [48652] Started task 'Matching object names'.
2020-11-11 09:15:14.787 -08:00|Information||| [48652] Run 'Matching object names' completion handler!
2020-11-11 09:15:14.787 -08:00|Information||| [48652] Successfully finishing current task 'Matching object names'.
2020-11-11 09:15:15.748 -08:00|Debug||| Sending 2 events:
[
  {
    "eventType": "connection.authenticationtype",
    "headers": {
      "productId": "sql-prompt",
      "productVersion": "10.4.7.16599",
      "eventUuid": "0a161d38-1927-4375-8193-5e6cc437bb47",
      "usageUserId": "21b51ceb-16b8-4f46-9e6c-2a10dcff094d",
      "sessionUuid": "55b9e907-7a86-4583-8777-49103998bffe",
      "createdTimeUtc": "2020-11-11T17:15:12Z"
    },
    "payload": {
      "authtype": "ActiveDirectoryUniversal",
      "hostEnvironment": "SqlServerManagementStudio"
    }
  },
  {
    "eventType": "server.connected",
    "headers": {
      "productId": "sql-prompt",
      "productVersion": "10.4.7.16599",
      "eventUuid": "ac61abf3-94af-4f10-9a35-782eb0095325",
      "usageUserId": "21b51ceb-16b8-4f46-9e6c-2a10dcff094d",
      "sessionUuid": "55b9e907-7a86-4583-8777-49103998bffe",
      "createdTimeUtc": "2020-11-11T17:15:13Z"
    },
    "payload": {
      "server_version": "Sql2019",
      "is_linkedserver": false,
      "hostEnvironment": "SqlServerManagementStudio"
    }
  }
]<p></p>

Is MFA support only for on-prem SQL server?

If so - when where is this on the roadmap? We no longer want to use any less secure authentication methods for SQL connectivity.

ostandage

Hi, 

There is a bug with SQL Prompt and Azure MFA connections, which appears to be affecting users connecting to SQL Server instances on a secondary Azure tenant (not where their credentials are managed).

We will be working on this in the coming few weeks, however we are unlikely to ship a fix for this before January 2021.

Apologies for the inconvenience caused.

Owen

MarkRos

This was supposed to have been fixed months ago but hasn't been.

The latest I received on my support ticket was from over a month ago:
"I can confirm the problem is a bug and the development team are going to investigate.  Sadly I cannot provide an estimate as to when a bug fix version will become at this time."

Very frustrating as (for me) Suggestions are the most useful and important feature of SQL Toolbelt!  I find it hardly worth the license cost without it.

RiteshG

I feel like cheated now.
I have upgraded to latest version of SQL prompt (with 3 yrs support) by paying nearly 450£ today, so that I can use it in SQL management studio 18.6 with MFA auth, but it looks like it does not work.
It does not show any database objects in suggestions. 
I am totally annoyed and need a refund now. 

ChrisDurante

Hi @ostandage,

I can confirm that the issue occurs when the Azure SQL Managed Instance or Azure SQL database are in a different tenant than the AAD account. I have a native account (in the same tenant as the SQL instances) to test with and suggestions do work for that account.

The primary AAD account my team and I sign in with for most services is in a different tenant so we're looking forward to the fix.

Thank you,
Chris