SqlServer Module not imported, Ping-SqlServer fails

[CCdschwenz]

Hi,

I am currently evaluating DevOps CI & CD for us and I noticed your great addin, appreciate the hard work.

So I have set up a build server, installed SSMS and SqlServer from the PowerShell gallery.
Afterwards, I have tried to run deploy my dacpac with a publish profile, which failed with the error:

##[error]Error: Error Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this type is loaded.

I investigated a bit within the code and this was raised by Ping-SqlServer submodule. I installed this from the PowerShell gallery as well and faced the same issue in the console. I had to enter Import-Module SqlServer, and subsequent runs would run fine immediately.

As I am not a PS expert, I do not know if the issue is my setup or if an explicit Import should be included.
During investigation, I found out that the powershell profile (neither system nor user) is used for DevOps Build Agents, so I had to modify the files within the Agent Task Directory to Include the Import statement.

Maybe you can add an explicit Import or have an Idea for me where to look?

Thanks!

[DrJohnT]

The PowerShell will automatically attempt to install module SqlServer, but it may fail due to permissions on your server. Do you have in-house hosted agent or using standard Azure agents?

[CCdschwenz]

Its a newly built VM (Windows Server), inhouse. I took the Zip from the Azure DevOps on Premise instance and set up the agent on this VM.

Afterwards I installed SqlServer using Install-Module -Name SqlServer -Scope AllUsers.
Now, I have added the Import-Module SqlServer to the following file in line :[PATHTOAGENT]\_work\_tasks\DeployDatabaseTask_eabf475c-6daf-4aca-9baa-aec01ce7aa51\1.2.2\PublishDacPacTask.ps1

The pipeline is now able to deploy the dacpac to the destination system.

The Service Account running the DevOps Agent is in the Administrators group on the machine.

[DrJohnT]

Last week I republished all the extensions. @CCdschwenz let me know if there are any new issues