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

Question

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 ******@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 '******@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?

Answer 1

@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 ******@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

Answer 2

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

Answer 3

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.