[Invoke-QueryRawDatabases] Failure | You cannot call a method on a null-valued expression

[DriesVyvey]

Hi,

I have just started using the dbatools module to be able to generate scripts from an Azure Synapse serverless database, but I encounter a problem that I don't understand.
The Azure automation account's managed identity has the db_owner role on the database.

In Azure automation I have the following runbook code for an automation account (runbook type: 'powershell' runtime version: '5.1')
I masked the instance name and the database name.

start code

try
{
"Logging in to Azure..."
$ConnectProfile = Connect-AzAccount -Identity
}
catch {
Write-Error -Message $.Exception
throw $.Exception
}

$azureToken = (Get-AzAccessToken -ResourceUrl https://sql.azuresynapse.net -DefaultProfile $ConnectProfile).Token
$synapseSqlName = "xxxxxxxxxxxxxxxxxxxxxxxxxxx"
$databaseName = "xxxxxxxxxxxxxxxxxxxxxxxxx"

$server = Connect-DbaInstance -SqlInstance "$synapseSqlName.sql.azuresynapse.net" -Database $databaseName -AccessToken $azureToken
Invoke-DbaQuery -SqlInstance $server -Query "select * from INFORMATION_SCHEMA.TABLES"

Get-DbaDbTable -SqlInstance $server -Database $databaseName -Table 'd365.Companyinfo' | Export-DbaScript

end code

The connection to the database using the access token works just fine, as the 'Invoke-DbaQuery' returns me the list of tables (and views) in the databasse

any subsequent call for script generation (like the Get-DbaDbTable in the script above) returns me the following error:
[Invoke-QueryRawDatabases] Failure | You cannot call a method on a null-valued expression.

I tried to get the access token the dbatools way:
$azureToken = New-DbaAzAccessToken -Type ManagedIdentity

but this gives an error for the New-DbaAzAccessToken:
[New-DbaAzAccessToken] Failure | An attempt was made to access a socket in a way forbidden by its access permissions

Any Idea why I invoke a dba query, but I cannot run commands like Get-DbaDbTable or Export-DbaScript

[niphlod]

hi @DriesVyvey, Invoke-QueryRawDatabases has little to do with invoke-dbaquery .... it's called within Get-DbaDatabase so it seems that it gets called on one of the functions you use

[potatoqualitee]

Tho that does look like code that should work. I wonder what's going on 👎🏼

[DriesVyvey]

hi @niphlod , That's indeed exactly what I'm mentioning: Invoke-DbaQuery works fine, other 'functions' do not.

[potatoqualitee]

Hello, can you please run the the following command:

Install-Module dbatools -AllowPrerelease -Scope CurrentUser

then restart powershell, and try again.

this is prerelease2 of dbatools 2.0, which contains a ton of fixes