Error running the PowerShell script in Runbooks

Question

Any help is appreciated.

I am getting the error below when trying to execute the PowerShell script in Runbooks.

Invoke-ASCmd : The term 'Invoke-ASCmd' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. At line:27 char:1 + Invoke-ASCmd -Query $TmslScript -Server: $XmlaEndpoint -Database $Dat ... + ~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: (Invoke-ASCmd:String) [], CommandNotFoundException + FullyQualifiedErrorId : CommandNotFoundException

But when I execute the same script locally then it is successful.

Powershell executed locally

I tried to install the SqlServer module using the script:

Install-Module SqlServer -RequiredVersion 21.1.18230 -Scope CurrentUser -SkipPublisherCheck -AllowClobber -Force

but it gave me the errors below.

Error 1:

Exception calling "ShouldContinue" with "2" argument(s): "A command that prompts the user failed because the host program or the command type does not support user interaction. The host was attempting to request confirmation with the following message: PowerShellGet requires NuGet provider version '2.8.5.201' or newer to interact with NuGet-based repositories. The NuGet provider must be available in 'C:\Program Files\PackageManagement\ProviderAssemblies' or 'C:\Users\Client\AppData\Roaming\PackageManagement\ProviderAssemblies'. You can also install the NuGet provider by running 'Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force'. Do you want PowerShellGet to install and import the NuGet provider now?" At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:7455 char:8 + if($Force -or $psCmdlet.ShouldContinue($shouldContinueQueryMessag ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : HostException

Error 2:

Install-Module : NuGet provider is required to interact with NuGet-based repositories. Please ensure that '2.8.5.201' or newer version of NuGet provider is installed. At line:12 char:1 + Install-Module SqlServer -RequiredVersion 21.1.18230 -Scope CurrentUs ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [Install-Module], InvalidOperationException + FullyQualifiedErrorId : CouldNotInstallNuGetProvider,Install-Module

So, I tried to install the NuGet package using the script:

Install-PackageProvider -Name NuGet -Scope CurrentUser -Force

but it gave me the error below:

Install-PackageProvider : No match was found for the specified search criteria for the provider 'NuGet'. The package provider requires 'PackageManagement' and 'Provider' tags. Please check if the specified package has the tags. At line:11 char:1 + Install-PackageProvider -Name NuGet -Scope CurrentUser -Force + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidArgument: (Microsoft.Power...PackageProvider:InstallPackageProvider) [Install-PackageProvider], Exception + FullyQualifiedErrorId : NoMatchFoundForProvider,Microsoft.PowerShell.PackageManagement.Cmdlets.InstallPackageProvider

I tried installing the following scripts in succession hoping to resolve the issue:

1. PowerShell Install-PackageProvider -Name NuGet -Scope CurrentUser -Force
2. PowerShellInstall-Module SqlServer -RequiredVersion 21.1.18230 -Scope CurrentUser -SkipPublisherCheck -AllowClobber -Force
3. PowerShellInvoke-ASCmd -Query $TmslScript -Server: $XmlaEndpoint -Database $DatasetName

Answer 1

Error running the PowerShell script in Runbooks:

Reasons for error:

Need to check:

The term 'Invoke-ASCmd' is not recognized as the name of a cmdlet:

1. Add/import the required module by opening below path:

Azure Automation account -> Shared Resources -> Modules -> Add a module Search for sql server from PS Gallery and import.

If you are installing module with script, it is not installing properly sometimes. I suggest you add modules and then invoke Invoke-ASCmd command in runbook.

I tried to install SQL Server after importing, but it failed since "Administrator rights" were required.

To resolve this, You can run account as an administrator or assign managed identities.

I used the following command to assign a 'UserAssigned Managed Identity' to the Automation account:

set-azautomationaccount -Resourcegroupname "Jahnavi" -Name "Jahnaviauto" -AssignUserIdentity "/subscriptions/<SubscriptionID>/resourceGroups/Jahnavi/providers/Microsoft.ManagedIdentity/userAssignedIdentities/<UserIdentityName_user>"

Now, I installed using below command and it worked for me successfully:

Install-Module -Name SqlServer -AllowPrerelease -Force -Verbose -Scope CurrentUser

Exception calling "ShouldContinue" with "2" argument(s):

2. This error can occurs when you have a package installed in the list and attempt to override it with the required version again.

   Use Moduleversionoverrides parameter in these cases.