sqlcmd fails to connect to azure sql server because of mfa, but active directory says mfa is disabled

Sondre Dahl 20 Reputation points
2024-04-27T14:28:52.5466667+00:00

I have been following this guide:
https://learn.microsoft.com/en-us/azure/azure-functions/functions-identity-access-azure-sql-with-managed-identity

and have reached the step "Grant SQL database access to the managed identity". I have downloaded sqlcmd and executed the following command: sqlcmd -S myserver.database.windows.net -d my-azure-db -G -U myuser@domain.no (with correct values, not the ones you see here).

However, I get the following error:

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Login timeout expired.  
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Failed to authenticate the user 'myuser@domain.no' in Active Directory (Authentication option is 'ActiveDirectoryPassword').  
Error code 0xA190; state 41360  
AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new location, you must use multi-factor authentication to access '022907d3-0f1b-48f7-badc-1ba6abab6d66'. Trace ID: a9939218-6784-4fa8-a211-bf0890356200 Correlation ID: b0a2d9b9-b1a8-46aa-8470-4c8449545920 Timestamp: 2024-04-27 14:12:59Z.  
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Error requesting access token, HTTP status 400, expected 200.  
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : TCP Provider: Timeout error [258]. .  
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Unable to complete login process due to delay in login response.

When I check out the multi-factor authentication setup for our users at: Microsoft 365 Admin I see that MFA is not enabled for any of our users. When I check Authentication methods under Microsoft Entra in Azure, it says Microsoft Authenticator is not enabled and neither is SMS.

How come I still get the error above? Is there any other configuration I may have missed or some other error related to sqlcmd?

Azure SQL Database
Azure Functions
Azure Functions
An Azure service that provides an event-driven serverless compute platform.
4,400 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,912 questions
Microsoft Entra
0 comments No comments
{count} votes

Accepted answer
  1. Oury Ba-MSFT 16,891 Reputation points Microsoft Employee
    2024-05-01T16:55:54.84+00:00

    @Sondre Dahl

    Issue: I am trying to connect to the sql server to set up the managed identity with the correct roles.

    I have downloaded sqlcmd and executed the following command: sqlcmd -S myserver.database.windows.net -d my-azure-db -G -U myuser@domain.no (with correct values, not the ones you see here) and got the above error.

    Resolution:

    I used SSMS after giving up on sqlcmd and it worked.

    Regards,

    Oury

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Amira Bedhiafi 16,306 Reputation points
    2024-04-28T19:23:13.18+00:00

    Based on this old thread, ou need to check 3 things :

    1. Check if security defaults are turned on (which enforces MFA).
    2. Check whether you have a sign-in risk on your account. If there is a sign-in risk policy that enforces MFA, then this could be your issue.
    3. Check for Conditional Access Policies that enforce MFA on your account.

    Here is a workaround :

    • Create a user with no specific permissions in AAD
    • Assign a random password
    • Repeat the WAC registration you will be prompted change password on logon

    More links :

    https://learn.microsoft.com/en-us/answers/questions/1288552/how-to-fix-the-error-aadsts50076-due-to-a-configur

    https://techcommunity.microsoft.com/t5/windows-admin-center-blog/windows-admin-center-version-2306-is-now-generally-available/bc-p/3869089/highlight/true#M496


  2. Bruce (SqlWork.com) 58,121 Reputation points
    2024-04-29T22:16:59.1833333+00:00

    Managed Identities are for Azure Services to access SqlServer. They can not be used from your local box to access sqlserver, and sqlcmd certainly does not support using a managed identity. an Azure function as described in the tutorial could once it was deployed.

    it appears your Azure AD requires MFA to login. while currently sqlcmd does not support MFA, you can use SSMS or Azure Data Studio to connect with a MFA account that has access to the SQLServer.