az account not prompting for login but still granting access

Question

I'm building a powershell script which interacts with SQL MI

There are a number of methods to do this. For various reasons I choose the .Net framework described here:

https://www.sqlshack.com/connecting-powershell-to-sql-server/

To authenticate I get a token using az account get-access-token

This works, but what concerns me is that it doesn't prompt for anything, including MFA.

How is this granting me access to a database without going through MFA?

Repro code below:

$SqlConn = New-Object System.Data.SqlClient.SqlConnection

$SqlConn.ConnectionString="Server=$DBServer;Encrypt=True;TrustServerCertificate=True;Connection Timeout=10;ApplicationIntent=ReadOnly;"

# note this finishes executing without prompting for anything and returns a valid token
SqlConn.AccessToken=$(az account get-access-token --tenant $tenant --resource https://database.windows.net --query accessToken -o tsv)

SqlConn.Open()

I then have some code that runs various selects in the database, including suser_sname() and this shows my AAD login

But I am never prompted for a password or put through MFA

How am I getting access to SQL MI data without going through MFA?

To rule out some kind of login caching I am;

• Completely logging out and in the RDP session
• running https://login.microsoftonline.com/common/oauth2/v2.0/logout to fully logout
• az login --tenant $tenant does prompt for login+MFA
• (Get-AzContext).TokenCache.ReadItems() gives You cannot call a method on a null-valued expression
• after logging in via this method, I log into SSMS and it prompts for login+MFA

So I don't think this is using an existing cached login

It seems like a SSO situation or something to do with the resource, but why would limiting to Azure SQL resource not require MFA or pwd?

Answer 1

It is using a cached refresh token to acquire tokens. This does not require MFA or a password as long as the refresh token is valid.

This answer mentioned where the cache is stored: https://stackoverflow.com/a/69011720/1658906.

This cache is separate from what SSMS or your browser uses so logging out/logging in there will have different state.

Get-AzContext won't return you this because it also uses a different cache as far as I know.

Answer 2

Posting a more detailed code answer here

Here is some Powershell code to

• login to Azure SQL db using a cached azure cli token if it exists
• If there is no cached token, prompt to login
• You can then use .Net objects to do all of your good SQL stuff

$SqlConn = New-Object System.Data.SqlClient.SqlConnection

$SqlConn.ConnectionString="Server=$DBServer;Encrypt=True;TrustServerCertificate=True;Connection Timeout=10;ApplicationIntent=ReadOnly;"

$jwt=$(az account get-access-token --tenant $tenant --resource https://database.windows.net --query accessToken -o tsv)

if ([string]::IsNullOrWhiteSpace($jwt)) {
  az login --tenant $tenant
  $jwt=$(az account get-access-token --tenant $tenant --resource https://database.windows.net --query accessToken -o tsv)  
}

SqlConn.AccessToken=$jwt

SqlConn.Open()

This whole process is very inconsistent.

• az cli can use a cached token for 90 days, not requiring login
• SSMS and Azure Portal always requires a login upon new windows session, also there are various login timeouts imposed
• I can't even get Azure Data Studio to login at all as it uses yet another set of logon procedures that seem to be based around VSCode and posting back to a docker image running on localhost. My current client is so locked down there's no way we will get this working