Authentication dialog opens for .NET Core when using Active Directory Interactive Authentication

[TechWatching]

I have been using Microsft.Data.SqlClient in .NET Framework with Active Directory Interactive authentication and it worked great:

• When running on Azure, the App Service's identity was used to connect to the database
• When running locally, my Microsoft account was used to connect to the database
• I had nothing to do it just worked

I have seen that Active Directory Interactive authentication is now available for .NET Core as well but it does not behave at all the same way. Each time I do a connection.OpenAsync() it launches an authentication dialog in my browser for me to log in with my account. Then my code can successfully make request to the database but I don't want to do a manual action each time my code runs locally.

Am I missing something ? My database connection string has the following format : "Server=tcp:sql-dev-myserver-data.database.windows.net,1433;Initial Catalog=db-dev-kpis;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;Authentication=Active Directory Interactive;"
Is there a way to avoid this dialog and that my code connects to the database silently with my account already logged in in Visual Studio as it was doing in .NET Framework ? The solution I was using until now for .NET Core was to retrieve an access token and set it directly in the connection but that is something I was hoping to avoid now that Active Directory Interactive is supported in .NET Core.

[cheenamalhotra]

@TechWatching

"Active Directory Interactive" authentication mode by-design performs authentication interactively with a dialog window.

It seems like you want to make use of Managed Identity when authenticating.
In case of .NET Framework application, you've probably configured your application to use a custom authentication provider that's not inbuilt in driver, but implemented by AppAuthentication library, as documented here. That provider is not what we own, so you'd have to reach out to https://github.com/Azure/azure-sdk-for-net team for .NET Core support regarding that.

On the other hand, we'll be introducing a different authentication mode for Managed Identity based authentication specifically, to be used as "Active Directory Managed Identity" (Feature #616 - WIP #730).

[TechWatching]

Indeed you are totally right, I forgot I was using this library. Thanks for the information. Will this Managed Identity based authentication work for local debugging in Visual Studio as well (using the identity of the account logged in Visual Studio) ?

[cheenamalhotra]

@TechWatching

I'm not sure what you mean by "Identity of account logged in Visual Studio".
Identity based authentication works only with Managed Identities in Azure (System-Assigned and User-Assigned).
They are not attached to any user account.

Can you point me to any official documentation that discusses this approach?
Furthermore, if you want to authenticate with active directory user account, you can always use Active Directory Integrated/Interactive modes.

[cheenamalhotra]

To clarify these two behaviors you need working:

• When running on Azure, the App Service's identity was used to connect to the database
  You will be able to use "Active Directory Managed Identity" as authentication mode in connection string after Feature | Azure Active Directory Managed Identity authentication support #730 is merged and released.

• When running locally, my Microsoft account was used to connect to the database
  You can use "Active Directory Integrated" or "Active Directory Interactive" authentication modes (supported since M.D.S v2.0.0+ for .NET Core)

[TechWatching]

Yes sure I am referring to this documentation

That's every thing you need to connect to SQL Database. When debugging in Visual Studio, your code uses the Azure AD user you configured in Set up Visual Studio. You'll set up SQL Database later to allow connection from the managed identity of your App Service app.

I don't want to have to change my connection string depending on whether I a running the code locally or on Azure. I want the connection string to be specified in my app settings file and the code to use the identity appropriate to my context.
It is currently the case in .NET Framework with "Active Directory Interactive" and the library you mentionned : on Azure the Managed Identity from my App service is used, and locally my Microsoft account is used. It feels a little bit like magic but the developement experience is great. I would love if it could look like that in .NET Core.

[TechWatching]

By the way, I did not manage to make the "Active Directory Integrated" work for me maybe because the Azure SQL Resource in on a different tenant (from a client company) than my Microsoft account (of my consulting company).
"Active Directory Interactive" .NET Core works fine but having to manually connect each time I launch the debug is not a very convenient experience.
Most of my code now runs in .NET Core but I have the feeling the interaction with the dabase was smoother when my code ran in .NET Framework, once configured everything just worked without manual actions (to log in or to change a connection string).

[cheenamalhotra]

Yes sure I am referring to [this documentation][https://docs.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi#modify-aspnet)

That's every thing you need to connect to SQL Database. When debugging in Visual Studio, your code uses the Azure AD user you configured in Set up Visual Studio. You'll set up SQL Database later to allow connection from the managed identity of your App Service app.

I don't want to have to change my connection string depending on whether I a running the code locally or on Azure. I want the connection string to be specified in my app settings file and the code to use the identity appropriate to my context.
It is currently the case in .NET Framework with "Active Directory Interactive" and the library you mentionned : on Azure the Managed Identity from my App service is used, and locally my Microsoft account is used. It feels a little bit like magic but the development experience is great. I would love if it could look like that in .NET Core.

Interesting! I will look more into it and get back to you!

[cheenamalhotra]

Hi @TechWatching

I did discuss this internally and it may be possible improve the overall design of Managed Identity authentication experience to match that of AppAuthentication library if we take dependency on Azure.Identity and support fallback for developers. As of now, it won't be part of next release since we cannot take dependency on Azure.Identity for the same.

Keeping issue open to address this in future.

[cheenamalhotra]

@TechWatching

In the meantime, there's another solution you could implement that will bring back same experience for you with .NET Core too, as I mentioned here: #730 (comment)