AAD password authentication no longer working for SQL Server access

Mark Young 0 Reputation points
2023-02-02T12:51:40.99+00:00

We have users who access Azure SQL databases from computers that are not Azure joined (a legacy thing, will get improved in time). They use scripts and SSMS and access the databases via Azure Active Directory Password authentication. This stopped for one database and server and is now affecting more and more of our databases and servers as time goes on. It first started affecting us about a week ago.

This feels like a change that is being rolled out, is this correct? Are there any details I can rad up on about this as I have not found anything so far.

I would like to use Azure Interactive authentication for SQL access, but the SqlClient doesn't seem to support this method. Can anyone confirm I am wrong and point me in the right direction? The interactive authentication gets me around the problem for SSMS, but the powershell scripts we have currently use SqlClient. The aim is to have users authenticate as themselves to the databases and I have been managing this using their AAD accounts, but integrated authentication cannot be used in all cases.

Azure SQL Database
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
19,455 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mark Young 0 Reputation points
    2023-02-07T11:07:56.57+00:00

    Hi Geetha,

    Our AAD Admins say that they have not blocked password logins.

    We are not using integrated authentication because not all of our work stations are AAD joined. Interactive AAD auth works in SSMS (Universal with MFA). It would be nice to use this in the powershell scripts, but I don't believe that SqlClient supports this. I have tested the Universal with MFA auth on accounts with MFA disabled and I can still successfully use that to log in in SSMS.

    Regards

    Mark

    0 comments
  2. GeethaThatipatri-MSFT 27,102 Reputation points Microsoft Employee
    2023-02-09T17:51:05.8366667+00:00

    @Mark Young As I mentioned We haven’t changed anything from the SqlClient side. Are you using System.Data.SqlClient (SDS) or Microsoft.Data.SqlClient (MDS)? The difference between the two wrt AAD authentication is SDS uses ADAL (which uses the v1 AAD endpoints) and MDS uses MSAL (v2 AAD endpoints). I don’t know if that makes a difference, but it could be relevant.

    Please do share the error message or an error screenshot.

    Regards

    Geetha

    0 comments