Stata + ODBC driver requires login too often

Question

I must create Windows VM templates for Azure, so that people can create instances of this template. Templates and instances are created fine, no problem with that.

In the VMs I have installed Stata 18 (it's software for data analysis), and it connects to a SQL Managed Instance through ODBC driver version 17.10.3.1. Authentication and authorization use Microsoft Entra ID and MFA. No problems with this step also; when a user tries to list the available datasources, a browser is loaded showing the usual Windows login page. Once the user writes valid credentials, MFA flows takes place and everything goes fine.

Usually, users run queries in the datasource and work with the results. Very often happens that between 2 queries the user needs several minutes for analyzing the results (i.e., user runs query1, receives the result, analyses it in another software in the VM, and uses Stata 18 again for running query2). When it's time for running another query, the login page shows up again, and it makes the user experience really bad.

Stata 18 and the ODBC driver are installed during the VM template creation, using PowerShell scripts. Here are the scripts:

File win-install-stata.ps1:

# create TEMP folder if not exists
$temp = "c:\temp"
If(!(test-path $temp))
{
    New-Item -ItemType Directory -Force -Path $temp
}

#Download Stata
Invoke-WebRequest -Uri "https://<MY_BLOB_STORAGE>/SetupStata18.exe" -outfile "c:\temp\SetupStata18.exe"

#Install Stata
Start-Process "c:\temp\SetupStata18.exe" -Wait -ArgumentList '/s /v"/qn ADDLOCAL=core,StataMP64 /Lv C:\temp\stata-install.log"'

#Set Stata Desktop Shortcut
$TargetFile = "$env:ProgramFiles\Stata18\StataMP-64.exe";
$ShortcutFile = "$env:Public\Desktop\STATA18.lnk";
$WScriptShell = New-Object -ComObject WScript.Shell;
$Shortcut = $WScriptShell.CreateShortcut($ShortcutFile);
$Shortcut.TargetPath = $TargetFile;
$Shortcut.Save();

#Activate Stata Licensing",
Invoke-WebRequest -Uri "https://<MY_BLOB_STORAGE>/CPRD-STATA18-WIN.LIC" -outfile "$env:ProgramFiles\Stata18\STATA.LIC"

#Clean up
Remove-Item -Path "c:\temp\SetupStata18.exe" -Force

File win-install-odbc.ps1:

# create TEMP folder if not exists
$temp = "c:\temp"
If(!(test-path $temp))
{
    New-Item -ItemType Directory -Force -Path $temp
}

#Download ODBC
(New-Object System.Net.WebClient).DownloadFile("https://<MY_BLOB_STORAGE>/msodbcsql.msi", "c:\temp\msodbcsql.msi")
#Install ODBC
Start-Process C:\Windows\System32\msiexec.exe -ArgumentList '/i "c:\temp\msodbcsql.msi" /L*v "c:\temp\msodbcsql.log" /qb IACCEPTMSODBCSQLLICENSETERMS=YES ALLUSERS=1' -wait
#Clean up
Remove-Item -Path "c:\temp\msodbcsql.msi" -Force

# Configure ODBC
$odbcDriverName="ODBC Driver 17 for SQL Server"
$odbcConName="<MY_DATASOURCE_NAME>"
$odbcConServer="<MY_SQL_MANAGE_INSTANCE>"

## Setting log
Start-Transcript -Append C:\temp\odbc-con.log -NoClobber -Force

$odbcDriverObj=Get-OdbcDriver -Name $odbcDriverName -Platform "32-bit" 2>$null
if(!$odbcDriverObj ){
    Write-Error "No '$odbcDriverName' detected. Not creating ODBC configuration."
}
else {
    $odbcObj=Get-OdbcDsn -Name $odbcConName -DriverName $odbcDriverName -Platform "32-bit" 2>$null
    $odbc64Obj=Get-OdbcDsn -Name $odbcConName -DriverName $odbcDriverName -Platform "64-bit" 2>$null
    if(!($odbcObj -and $odbc64Obj)){
        $newDsn32 = Add-OdbcDsn -Name $odbcConName -DsnType "System" -Platform "32-bit" -DriverName $odbcDriverName -SetPropertyValue @("Description=Aurum", "Server=$odbcConServer", Trusted_Connection=Yes", "Authentication=ActiveDirectoryInteractive", "Database=<MY_DATABASE_NAME>") -PassThru
        $newDsn64 = Add-OdbcDsn -Name $odbcConName -DsnType "System" -Platform "64-bit" -DriverName $odbcDriverName -SetPropertyValue @("Description=Aurum", "Server=$odbcConServer", "Trusted_Connection=Yes", "Authentication=ActiveDirectoryInteractive", "Database=<MY_DATABASE_NAME>") -PassThru

        #WORKAROUND: Setting LastUser to "" to avoid username building image (packer)
        Set-ItemProperty -Path "HKLM:\SOFTWARE\ODBC\ODBC.INI\$odbcConName" -Name "LastUser" -Value ''
        Set-ItemProperty -Path "HKLM:\SOFTWARE\WOW6432Node\ODBC\ODBC.INI\$odbcConName" -Name "LastUser" -Value ''

        Write-Host "ODBC configuration named '$odbcConName' created successfully (32/64 bit): $newDsn32 / $newDsn64"
    }
    else { Write-Host "ODBC configuration name '$odbcConName' detected: $odbcObj / $odbc64Obj"}
}

Stop-Transcript

Both softwares have their install packages stored in a Storage Account and are downloaded, executed, configured and deleted, during VM template creation time. ODBC configurations set with -SetPropertyValue are working (i.e., when the user runs a query, the parameters shown on the screen agree with the one set in the PowerShell script).

Link [1] has the information on how to set the ODBC driver. It contains the following note: As of driver version 17.9, the interactive authentication behavior has changed. Users will always be prompted for credentials unless the driver has a valid access token cached. This change prevents users on Microsoft Entra joined devices from skipping the prompt and automatically signing in with cached credentials when using ActiveDirectoryInteractive authentication.

Unfortunately, I couldn't find nothing about the access token. Besides, in [1] it says that Windows Integrated Authentication can be used, so that the logged in user in the VM will be used to log in in the SQL server (link [2] explains how to set Integration Authorization). However, it's not my case.

So, any ideas on how to prevent such repetitive log ins through the ODBC driver?

[1] https://learn.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-ver16

[2] https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/using-integrated-authentication?view=sql-server-ver16