search

DrJohnT / AzureDevOpsExtensionsForSqlServer

Microsoft SQL Server deployment extensions for Azure DevOps Pipelines
MIT License
20 stars 14 forks source link

SqlServer Module not imported, Ping-SqlServer fails #21

Closed CCdschwenz closed 3 years ago

CCdschwenz commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 image

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 commented 3 years ago

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