How to invoke-sqlcmd (or sqlcmd.exe) with AAD+MultiFactorAuth

Question

All the docs and help threads I can find reference connection strings with Authentication=ActiveDirectoryIntegrated to hit SQL with AAD integration. If I'm using SSMS I can also choose "Active Directory Universal" which gives a prompt if MultiFactorAuth (MFA) is required.

I want to use powershell to invoke-sqlcmd, or even sqlcmd.exe directly -- do either support an MFA flow? How else can I get commandline queries against an AAD-enabled MFA-enabled SQLAzure instance?

invoke-sqlcmd : Failed to authenticate the user NT Authority\Anonymous Logon in Active Directory
(Authentication=ActiveDirectoryIntegrated).
Error code 0xCAA2000C; state 10
AADSTS50079: The user is required to use multi-factor authentication.
Trace ID: 54f0cb31-2f0f-4137-b142-b312a6cd441b
Correlation ID: 70204904-576c-4db5-9c3b-6ccd7fe6b409
Timestamp: 2017-02-09 22:56:39Z

I've seen https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication, and everything was working fine right up until MFA either was applied, or when it realized it was time to re-auth and prompt.

If there is a way for me to cache creds so ActiveDirectoryIntegrated generally works, and I just need to re-auth and re-cache when it decides it is time to force an MFA prompt I'm also open to that.

Answer 1

I want to use powershell to invoke-sqlcmd, or even sqlcmd.exe directly -- do either support an MFA flow?

No. As far as I know, the SSMS is the only tool currently enabled for MFA through Active Directory Universal Authentication.( refer here)

If you have any idea or suggestion about Azure SQL database, you can submit them from here.

Answer 2

Beginning with version 15.0.1, sqlcmd utility and bcp utility support Active Directory Interactive authentication with MFA.