Visual Studio Team Services (VSTS) is popular tool of choice for various purposes where product is heavily dependent on Microsoft technologies like .NET, Azure, etc. It does lot of work such as Source code management, Building CI/CD pipelines, Package Management, Agile Issue tracking, etc. It is a cloud hosted service offering from Microsoft.

Hosted agent is a build agent that is provided by Microsoft for build and continuous integration purposes. However, you don’t have much control over the configuration of the hosted agent. It comes with most of tools you would normally require for building your source code. Now, when building PowerShell, it will come with latest version of PowerShell, so you can use built in package management cmdlets like Install-Module. Occasionally, you would need to install custom PowerShell modules such as SqlServer (Formerly known as SQLPS).

However, if you would use command Install-Module -Name SqlServer, it will throw this error:

2017-08-17T12:14:05.6043973Z ##[error]Install-Module : Administrator rights are required to install modules in ‘C:\Program Files\WindowsPowerShell\Modules’.
Log on to the computer with an account that has Administrator rights, and then try again, or install
‘C:\Users\buildguest\Documents\WindowsPowerShell\Modules’ by adding “-Scope CurrentUser” to your command. You can also
try running the Windows PowerShell session with elevated rights (Run as Administrator).
At D:\a\r1\a\MyApp.BaseApp-CI-Clone\drop\dbscripts\running-sql-queries.ps1:2 char:1
+ Install-Module -Name SqlServer
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Install-Module], ArgumentException
+ FullyQualifiedErrorId : InstallModuleNeedsCurrentUserScopeParameterForNonAdminUser,Install-Module

Since we are not administrators on the hosted agent, and as such we cannot install a PowerShell module into the AllUsers scope.

The correct way to install a PowerShell module is this:

# Installs SQL Module
Install-PackageProvider -Name NuGet -Force -Scope CurrentUser
Install-Module -Name SqlServer -Force -Verbose -Scope CurrentUser

The first step will install the nuget.exe into our user’s scope, and the second step installs the PowerShell module “SqlServer” into C:\Users\buildguest\Documents\WindowsPowerShell\Modules\SqlServer. For this we do not need administrative permissions and our build will continue beyond this step. Same goes for release definition too.