MFA connection with WriteDbaTableData doesn't seem to work

[spireitepaul]

I have the following code which seems to be 'correct' in my expectations.

I have an Azure Sql with MFA enabled.

Param (
    [string]$Subscription,
    [string]$File,
    [string]$SqlInstance="test",
    [string]$Database
)


$azureAccount = Connect-AzAccount 
$azureToken = Get-AzAccessToken -ResourceUrl https://database.windows.net
$azureSub = Set-AzContext -Subscription $Subscription

$server = Connect-DbaInstance -SqlInstance $SqlInstance -Database $Database -AccessToken $azureToken

Write-Output $server

foreach($sheet in Get-ExcelSheetInfo $File)
{
    Write-Output $sheet
$data = Import-Excel -Path $File -WorksheetName $sheet.name | ConvertTo-DbaDataTable
$tablename = $fileName + '-' + $sheet.Name

Write-DbaDataTable -SqlInstance $server -InputObject $data -AutoCreateTable -Table $tablename
}

However, it doesn't seem to work...

I receive the following error

WARNING: [14:11:24][Write-DbaDbTableData] Failure | Login failed for user ''.
  

Is there something stupid I missed?

[wsmelton]

Initial guess is that SqlBulk class we use doesn't support passing the token but I can't configure MFA on my Azure tenant to confirm that is true or not.

I can see though as well we are not capturing that error in proper way to show you where the issue occurs either. 😬 Can you run that command with -Verbose and provide that output, or the few messages before and after that login failed occurs? That will help us narrow down the step having issues.

[spireitepaul]

Ok, my entire Verbose output

VERBOSE: Performing the operation "log in" on target "User account in environment 'AzureCloud'".
WARNING: TenantId 'xx' contains more than one active subscription. First one will be selected for further use. To select another subscription, use Set-AzContext.
To override which subscription Connect-AzAccount selects by default, use Update-AzConfig -DefaultSubscriptionForLogin 00000000-0000-0000-0000-000000000000. Go to https://go.microsoft.com/fwlink/?linkid=2200610 for more information.
VERBOSE: Switching targeted subscription in the context to 'xx'
VERBOSE: [17:06:56][Connect-DbaInstance] Azure detected
VERBOSE: [17:06:56][Connect-DbaInstance] String is passed in, will build server object from instance object and other parameters, do some checks and then return the server object
VERBOSE: [17:06:56][Connect-DbaInstance] authentication method is 'azure token'

VERBOSE: [17:06:56][Connect-DbaInstance] Azure detected
VERBOSE: [17:06:56][Connect-DbaInstance] Server object passed in, will do some checks and then return the original object
VERBOSE: [17:06:56][Write-DbaDbTableData] FQTN processed: [dbo].[-Sheet1]
WARNING: [17:06:59][Write-DbaDbTableData] Failure | Login failed for user ''.

[wsmelton]

Since this command uses SqlBulkCopy to write data it is only going to pass in the connection string of the SMO Server object.

dbatools/public/Write-DbaDbTableData.ps1

Lines 543 to 547 in d7509c6

	try {
	$bulkCopy = New-Object Microsoft.Data.SqlClient.SqlBulkCopy($server.ConnectionContext.SqlConnectionObject, $bulkCopyOptions, $null)
	} catch {
	$bulkCopy = New-Object Microsoft.Data.SqlClient.SqlBulkCopy($server.ConnectionContext.ConnectionString, $bulkCopyOptions)
	}

This won't include the token for MFA that your environment is requiring.