Update-TabularCubeDataSource Error

[apsych]

Dear Mr John, First of all I would like to truly thank you for your contribution on the custom tasks you created for the pipelines. For now I have two environments Acceptance and Test. In both environments:

• I am able to publish successfully the Tabular cube.
• I setup the self hosted agent under 'Network Service', which this login has sysadmin role in both SQL Servers.
• The 'Network Service' is a server administrator on the Cube.
• Configuration-> SSAS Server Name or IP address: localhost, Impersonation mode to be used by the data source: Use the SSAS service account(default) (The only different between environments is the SourceSqlServer) The issue that I am facing is that I cannot update the SSAS tabular cube data source on Acceptance, where in Test it succeeds. ##[error]SSAS Server localhost not found Am I missing/forgot something? Do you have any clue what may causing that?

[DrJohnT]

Hi, 'Network Service' will work fine on localhost, but not connecting across to a different server. Are the two Acceptance and Test on different servers? If so, you will need to use a domain service account for SSAS to connect from one server to the other.

Just guessed that perhaps you mean Acceptance and Test are two different servers and they are configured the same (with SQL database engine AND SSAS installed on both). If that is the case, then double check that SSAS service on each machine is running under the correct account (typically NT Service/MSSQLServerOLAPService) and this account has read access to the SQL Database tables/views on same machine. I would not recommend changing NT Service/MSSQLServerOLAPService.

If your DevOps agent is running under 'Network Service' then it will only be able to deploy to itself (i.e. localhost) unless you provide credentials to the task.

[apsych]

Thank you for you response!

Yes, Acceptance and Test are on different servers. But the pipeline have two different stages , so they are not connected.

Correct, they have the same configuration. As the DevOps agent is running under 'Network Service', why do I need the SSAS service? New pipeline -> Run on the self hosted agent and the solution is build (SQL DB, SSIS, SSAS). New release -> Code is being picked up by the DevOps agent (Network Service).Network service has admin access to the DB & SSAS.

How it's possible on Acceptance, where the error happens, to be able to deploy the cube (Invoking Publish-Cube) successfully but get the error during the update SSAS tabular cube data source 'Invoking Update-TabularCubeDataSource'?

[DrJohnT]

Does sound rather odd. Not sure what is going on there.

I would suggest using Remote Desktop onto the DevOps deployment agent (i.e. the server it runs on) and running the steps from the PowerShell command line. That may reveal the problem.

[apsych]

After running the steps from Powershell for Update-TabularCubeDataSource.ps1, I got the bellow error

Get-ModuleByName : Error PowerShell Gallery is currently unavailable.  Please try again later.
At line:150 char:9
+         Get-ModuleByName -Name SqlServer;
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Get-ModuleByName

SSAS Server localhost not found 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. Error PowerShell Gallery is currently unavailable.  Please try again later. 
PowerShell Gallery is currently unavailable. 

For those who encountered the same issue, I resolved that doing the following:

1) Download manually the the .nupkg file from https://www.powershellgallery.com/packages/SqlServer/21.1.18235 and place that on user download folder c:\Users\myusername\Downloads 2) Run on Powershell Install-Package sqlserver-verbose -Source c:\Users\myusername\Downloads\ -SkipDependencies 3) As Microsoft announced that the PowerShell Gallery has deprecated Transport Layer Security (TLS) versions 1.0 and 1.1 as of April 2020 run on Powershell [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 4) If the above where successful, you could see if Cmdlet Invoke-ASCmd is listed as a module of SQL server by executing Get-Command -Module SqlServer

Also the newest module of SQLServer requires minimum Powershell version 5