How to force Invoke-sqlcmd to use domain account for authentication?

Question

Dear Stackoverflow friends:

I have an issue with a script I got from Microsoft article "https://learn.microsoft.com/en-us/azure/sql-database/sql-database-file-space-management" It is a PowerShell script for calculating every database's allocated space and used space. I need check the elastic pool size regularly to ensure DB cloning could happen.

It is about the authentication method for this command "Invoke-Sqlcmd -ServerInstance $serverFqdn -Database $database.DatabaseName -Username $userName -Password $password -Query $sqlCommand". I suppose the default authentication method is for SQL database user which is the account created inside the database. But I have requirements from my boss to use either an Azure Active Directory (AAD) account or service principle. I tried my best to use the domain account, but couldn't make it work. It always create login failure error as below:

    2019-12-10T01:04:06.7785144Z At D:\a\_temp\80a23d17-9a10-4d09-a2d9-8fcfbdb3914b.ps1:65 
    char:10
    2019-12-10T01:04:06.7785735Z +         (Invoke-Sqlcmd -ServerInstance $serverFqdn -Database 
    ```

My modified version of Microsoft script is as following: (I removed the credentials for safety purpose)

    echo "Install Az module"
Install-Module -Name Az -Force -Verbose -Scope "CurrentUser" -AllowClobber
echo "Install sqlServer module"
Install-Module -Name "SqlServer" -AcceptLicense -Force -Verbose -Scope "CurrentUser" -AllowClobber
echo "Import SqlServer modules"
Import-Module SqlServer -Version 21.1.18179

$resourceGroupName = "AHC-DEV-WU-DataPipeline"
$serverName = "aaa"
$poolName = "ahdevdplusdbpool-ads"
$userName = "xxxxx-acab-4b27-a44e-xxxxx"  (this is the service principle ID)
$password = "xxxxx_tqSY0FxxxxxxxfqZ"  (this is the service principle password)
$tenantId="xxxxxx831-64a12xxxxx8da"   (this is our azure account tenant ID)
##$pscredential = Get-Credential Connect-AzAccount -ServicePrincipal -Credential $pscredential -Tenant $tenantId
$passwd = ConvertTo-SecureString $password -AsPlainText -Force


$pscredential = New-Object System.Management.Automation.PSCredential($userName, $passwd)
Connect-AzAccount -ServicePrincipal -Credential $pscredential -Tenant $tenantId


# get list of databases in elastic pool
$databasesInPool = Get-AzSqlElasticPoolDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -ElasticPoolName $poolName
$databaseStorageMetrics = @()
# $userName2="svc_xxxx@xxxx.onmicrosoft.com"   (this is the domain user account)
# $password2="_4xxxxx"                                   (domain user account password)


# for each database in the elastic pool, get space allocated in MB and space allocated unused in MB
foreach ($database in $databasesInPool) {
    $sqlCommand = "SELECT DB_NAME() as DatabaseName, `
    SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB, `
    SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB `
    FROM sys.database_files `
    GROUP BY type_desc `
    HAVING type_desc = 'ROWS'"
    $serverFqdn = "tcp:" + $serverName + ".database.windows.net,1433"
    $databaseStorageMetrics = $databaseStorageMetrics + 
        (Invoke-Sqlcmd -ServerInstance $serverFqdn -Database $database.DatabaseName `
            -Credential $pscredential  -Query $sqlCommand -Verbose)
}

# display databases in descending order of space allocated unused
Write-Output "`n" "ElasticPoolName: $poolName"
Write-Output $databaseStorageMetrics | Sort -Property DatabaseDataSpaceAllocatedUnusedInMB -Descending | Format-Table

>