Powershell DBAtools not working with Octopus

[nsantos.br]

Hello Guys,

I am trying to run the following script using the Powershell DBAtools module to export to a CSV file all my Databases size. From the server the script works fine, but using Octopus, all I can get is the
Databases names, but without their size. Does anyone know how to solve this or have any other idea to get the DB size through Octopus?

if (Get-DbaAgReplica -SqlInstance InstanceName | Where-Object {$_.Role -eq "Primary" })
{
    $Server= "InstanceName"
    $file= "G:\work\DBSize_$env:COMPUTERNAME$(get-date -f yyyy-MM-dd).csv"

    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null
    $SMOserver = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) -argumentlist $Server
    $SMOserver.Databases | select Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable | Export-Csv -Path $file -NoTypeInformation
}
    Else
    {
        Write-Host Secondary
    }

Thanks!

[Matt.Richardson]

Hi there

Thanks for getting in touch! At first glance, I would guess that the issue is due to a difference in user accounts when connecting to the database. It appears you’re using integrated authentication, so when you’re running it, its running as you, but when Octopus runs it, it runs as the service account that Octopus is setup under.

If that’s not the case, as this appears to be a DBAtools issue, I would recommend raising an issue over on their github repository.

Hope that helps!

Regards,
Matt

[nsantos.br]

Is there a way to force an Octopus step to run as another user?

Thanks!

Regards,
Ney

[Matt.Richardson]

Hi Ney

You could try using the special variables Octopus.Action.PowerShell.Username and Octopus.Action.PowerShell.Password . While not explicitly designed for this scenario, that may work to set the user to use when running your script.

Hope that helps!

Regards,
Matt