System.Data and Micrsoft.Data SQLClient and MFA

Question

System.Data and Micrsoft.Data SQLClient and MFA

Lee Taylor 31

We are trying to figure out how to implement MFA with Azure SQL Server. Currently we are using Active Directory Password within the connection string, I have seen some references to Active Directory Interactive but using this in my System.Data.SQLClient in my .NET Framework application gives an error for unsupported authentication method. How would I go about implementing MFA with Azure SQL in my Winforms .NET Framework (Systeem.Data.SQLClient) and Core (Microsoft.Data.SQLClient) applications?

2 answers

Your answer

Answer 1

Sreeju Nair 12,666

The connection string does not support MFA. If you want to implement MFA for your application, you need to implement this in the application layer. MFA for windows forms application can be integrated in to your application flow. Another good idea is to implement your windows forms application with Azure AD. See the below reference video.

e.g. https://www.youtube.com/watch?v=73UAncCA2QQ

Hope this helps

Answer 2

GeethaThatipatri-MSFT 29,542 Microsoft Employee Moderator

Hi, @Lee Taylor Thanks for posting your question in the Microsoft Q&A forum.

Microsoft.Data.SqlCleint has multi-target frameworks support, including .NET Framework. S.D.SqlClient doesn't extend the features and to take advantage of them, you should consume M.D.SqlCleint.

Connect to Azure SQL with Azure AD authentication and SqlClient - ADO.NET Provider for SQL Server | Microsoft Learn

You can also check this sample if you're bound to use System.Data.SqlClient with MFA authentication:

https://github.com/cheenamalhotra/SampleSqlAuthProvider

More info on SQLAuthentication provider:

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlauthenticationprovider?view=netframework-4.8.1

Ideally, you should be migrating to Microsoft.Data.SqlClient as soon as possible.

Regards

Geetha

Lee Taylor 31 Reputation points

2023-10-19T15:44:35.07+00:00

As you know you do not hold a connection open throughout the life cycle of the application, you make and close the connection as necessary. So how does the driver handle this, does it cache a token or something for reconnections? what if you change the database you are connecting to but are still the same user?

Also, the content of the links you shared refer to Microsoft.Data SQLClient, does the same apply to System.Data.SQLClient? If not I will have to change from System.Data to Microsoft.Data.
GeethaThatipatri-MSFT 29,542 Reputation points Microsoft Employee Moderator

2023-10-25T00:58:29.85+00:00

@Lee Taylor If you are referring to the scenario with "Authenication=ActiveDirectoryInteractive" connection string property, the driver caches tokens until their expiry time and renews them 10 minutes before they expire. For any future reconnects the same token is used as long as it's active.

By change of database, if you mean you're changing connection string, a new token is acquired for the user. MSAL may return the same token as MSAL library also holds an in-memory cache so for the same user email, the same tokens are possible if the scope of access is the same.

This link is for System.Data.SqlClient, and the GitHub example applies to both as mentioned in the repo readme file.

If this answers your question, please consider accepting the solution by hitting the Accept as Solution ‌‌as it helps the community look for answers to similar questions.

Regards

Geetha
GeethaThatipatri-MSFT 29,542 Reputation points Microsoft Employee Moderator

2023-11-02T14:42:40.6566667+00:00

@Lee Taylor I am checking to see if you got a chance to look into the below responses. Please let me know if you have any further questions.

If this answers your question, please consider accepting the answer by hitting the Accept answeras it helps the community look for answers to similar questions.

Regards

Geetha

Share via

System.Data and Micrsoft.Data SQLClient and MFA

2 answers

Your answer