Azure SQL DB using AAD and MFA

Andeavour

SQL Data Compare and SQL Compare don't seem to work with Azure if your databases require Multi Factor Authentication. We are using this and can log into the databases with SSMS using Azure AAD & MFA, but when that user then attempts to run a Compare (Schema or Data), the project fails to log in with error AADSTS50076, saying you must use multi-factor authentication to access.
I have used MFA to access the database, but that's not an option in the Redgate connections. How can I get this working, because currently we are having to use a SQL Server user to perform our compares?

DanC

Hi @Andeavour so we currently support Azure Active Directory in the latest versions of SQL Compare & Data Compare, however MFA is currently still under development. Please keep an eye on the release notes for further updates!

https://documentation.red-gate.com/sc/release-notes-and-other-versions

BonnieF

Is there an update on this issue?

DanC

Hi @BonnieF

So if you update to the latest version of SQL Compare 14, you can use Active Directory Interactive Authentication.

The following document should help: https://documentation.red-gate.com/sc/setting-up-the-comparison/using-azure-interactive-authentication

BonnieF

Is there some reason you aren't bringing up the standard AAD interactive MFA dialog but instead requiring an application ID?

AndrewStevens

BonnieF said:

Is there some reason you aren't bringing up the standard AAD interactive MFA dialog but instead requiring an application ID?

Echoing Bonnie's comments. It doesn't seem appropriate to need to create an application within Azure to use SQL Compare (and likewise, SQL Multiscript). Are there any plans to implement a more "standard" approach to authentication?

kdevine

Azure Interactive Authentication is no longer an option since the last update and the MFA is throwing an error.

DanC

AndrewStevens said:

BonnieF said:

Is there some reason you aren't bringing up the standard AAD interactive MFA dialog but instead requiring an application ID?

Echoing Bonnie's comments. It doesn't seem appropriate to need to create an application within Azure to use SQL Compare (and likewise, SQL Multiscript). Are there any plans to implement a more "standard" approach to authentication?

@BonnieF

 The reason for this is because SQL Compare and other tools need their own application ID, whereas other tools like SSMS have a built-in app id that is preauthorized.

DanC

kdevine said:

Azure Interactive Authentication is no longer an option since the last update and the MFA is throwing an error.

Hi Kevin, there's currently an issue with one of the Microsoft libraries, could you please reach out and submit a support ticket for us to investigate this?

https://productsupport.red-gate.com/hc/en-us

DanC

Hi,

For anyone who also experienced issues with MFA throwing an error please see the following post to address this: https://forum.red-gate.com/discussion/88435/active-directory-interactive-now-gone-and-mfa-doesnt-work