Cannot authenticate to Azure SQL Server with MFA

SuperJMN

Recently, my org has switched to this new (more secure) authentication mode. I've tried to use it with LINQPad 6.

This is my setup:

When I click test, I get this:

The error is immediate. I expected to be prompted with a Multi Factor Auth request, but I'm not 🤔

More info: I can connect using MS SQL Management Studio with this configuration:

What am I doing wrong?
Thanks in advance!

BTW, I'm a licensed user

JoeAlbahari

More details of this error should be recorded in %localappdata%\linqpad\logs.linqpad6\log.txt.

Could you post the relevant entry, please?

Also could you try setting up this connection in LINQPad 5 and letting me know whether you get the same error.

Thanks

SuperJMN

Thanks for your quick response!

I've attached the entire log file, in case I miss something, because it's not really clear what's the relevant error
I've also confirmed that the same happens in LINQPad 5.

SuperJMN

Is this difficult to fix?
Thank you, Joe.

JoeAlbahari

There's nothing relevant in the log file. Are you able to try this in LINQPad 5 and let me know whether it works? LINQPad 6's implementation is much more complex because the .NET Core libraries do not support MFA.

SuperJMN

I'm afraid it doesn't work in version 5 either. I'm getting the very same error message. Moreover, it doesn't log anything.

How could I help?

JoeAlbahari

Does this happen on more than one machine?

SuperJMN

I've tried it in a fresh virtual machine with LINQPad 5 and the same error is shown. If you want, I can give you access to my computer. I have TeamViewer. Just ask me privately if you want to research about the problem.

JoeAlbahari

Thanks - I've sent you a private message.

Blackstar

I have exactly this issue and I believe I know the cause.

When I connect to the same Azure SQL database via SSMS I have to specify "AD domain name or tenant ID" in the Connection Properties or I get this error. I don't see a way to provide this value in LinqPad (6).

Here's the relevant documentation: https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-mfa-ssms-overview#azure-ad-domain-name-or-tenant-id-parameter

Is there a way to provide the tenant ID in LinqPad?

JoeAlbahari

LINQPad derives the tenant ID from the login name - it's everything after the @ symbol.

I've created a script that does almost exactly what LINQPad does to connect via MFA. Assuming that this script reproduces the issue at your end, you can play with the parameters to determine what's causing it to fail for you. If you can successfully identity the issue, I'm happy to update LINQPad accordingly. Here's the script:
http://share.linqpad.net/u7q4ax.linq

Please note that you must run this script in LINQPad 5 because it relies on the Microsoft.IdentityModel.Clients.ActiveDirectory NuGet package which doesn't support MFA in .NET Core!

Also note that I have identified an issue with LINQPad not recognizing the German, China and US military clouds. If your database server does not end in .database.windows.net, this is now a known problem which will be fixed soon in LINQPad 6.

Blackstar

Thanks for the script. Running it as provided yielded the same error message Login failed for user '<token-identified principal>'.. I also noticed that running the script caused the expected credential dialogue to appear, this does not happen with a LinqPad connection as specified above.

I was able to get this working though, by manually setting the tenant id.

int atPos = login.IndexOf ('@');
string tenant = atPos == -1 ? login : login.Substring (atPos + 1);
tenant = "my-tenantid";

The specific use case here (as detailed in the microsoft docs I linked to above) is that my account is in one tenant (A) Azure AD, but the resource I am accessing is in another tenant (B). My account is a guest in tenant B Azure AD.

JoeAlbahari

Thanks for identifying the issue. Can I also ask that you try setting forceMfaDialogToAppear to false in the script (this is what LINQPad does by default) and tell me if your solution still works? Make sure that you hit Ctrl+Shift+F5 to kill the cached process before testing.

Blackstar

With forceMfaDialogToAppear = false only I got the same error message Login failed for user '<token-identified principal>'.
With forceMfaDialogToAppear = false and tenant = "my-tenantid" the connection was successful.

JoeAlbahari

Thanks - I'll get a beta out soon.

JoeAlbahari

A beta is now out:
https://www.linqpad.net/linqpad6.aspx#beta

You can now override the login's domain with a different AD domain or tenant ID in the connection dialog.

Let me know how you get along.

SuperJMN

Worked for me! I used the tenant-id in the new textbox.

Blackstar

That's working for me too. Thanks!

The only thing I've noticed is that (what I'm assuming is) the auth popup comes up and disappears on every interaction with the database server (connecting, expanding list list of databases, running a query).

Functionally though, works perfectly.

Are you able to say when this will make it into a full release?

JoeAlbahari

That's great news. The next RTM is due end of June.

JoeAlbahari

Regarding the popup appearing and disappearing with every connection, I've added a potential workaround to the latest beta (6.9.10). To test it, prefix the Tenant ID with an exclamation mark. Let me know whether it works for you.

Blackstar

Using the 6.9.10 beta I had to recreate the connection (I may have deleted it from 6 I don't recall) and with or without the exclaimation mark prefix on Tenant ID the popup only appeared initially and did not flash up again.

esperadoce

Hello @JoeAlbahari
I have the same issue.
In SSMS or Azure Data Studio I can specify the database name

something that i'm not allowed to do in linqpad do you have any fix for this issue.

PS: I'm using the last LinqPad version

esperadoce

Humm,I just needed to specify the Initial Catalog in the additional connection string parameters section,
it's kinda missleading to have a database section and not use it, It would be nice to use it directly in this kind of use case

JoeAlbahari

When creating a new connection, you can click "Specify new or existing database" instead of "Display all in a TreeView" and it will let you choose a database. The reason that the option is grayed out in your case is because you're editing an existing connection (it's tricky for LINQPad to handle this kind of edit without breaking queries linked to that connection).

In any case, it's strange that login access to the default database should be defined. Could you please run the following query in that database, and let me know the output?

var token = ((SqlConnection)this.Connection).AccessToken;

var defaultBuilder = new SqlConnectionStringBuilder (this.Connection.ConnectionString);
defaultBuilder.Remove ("Initial Catalog");
Util.Try (() => Test ("default", defaultBuilder.ToString()), true);

var masterBuilder = new SqlConnectionStringBuilder (this.Connection.ConnectionString);
masterBuilder.InitialCatalog = "master";
Util.Try (() => Test ("master", masterBuilder.ToString()), true);

void Test (string name, string cxString)
{
    $"Opening {name}".Dump();
    using var cx = new SqlConnection (cxString);
    cx.AccessToken = token;
    cx.Open();
    $"Opened {name}".Dump();    

    new TypedDataContext (cx)
        .ExecuteQueryDynamic ("select name from sys.databases")
        .Dump ($"with {name} database");
}

esperadoce

Hi @JoeAlbahari
Maybe I'm wrong because I'm new in the app or I'm doing bad. this's the process I followed to create a new connection.

Here's the result when I execute the code

In Azure Data Studio and SSMS I have the same error unless, I specify the database as well.

Depact

Years later. Same issue