Error processing SSAS cube using TMSL via SQL Server Agent Job

• squareoff

  Mr or Mrs. 500

  Points: 587

  More actions

  August 3, 2020 at 4:04 pm

  #3776928

  Hi,

  I am trying to automate SSAS cube processing by having an SQL Server Agent job run a TMSL script.

  I have 2 servers (SSAS where all the cubes live and ETL where all the jobs live).  Both are 2017 version.

  Below is what I tried and the error I am getting:

  1. Created TMSL script that processes a cube
  2. Created a job on the ETL Server with the following specifications:

     1. Type: SQL Server Analysis Services Command
     2. Run as "ETL Proxy" (this proxy is active to the "SQL Server Analysis Services Command" subsystem)
     3. Server: SSAS Server
     4. Command: {"refresh": {"type": "full","objects": [{"database": "SSAS_DATABASE_TO_REFRESH"}]}}

  Running the above command directly in SSMS on the SSAS Server, worked with no problems, refreshing the cube.

  Running the job, however, gave the following error (which I suspect has something to do with permissions, but I can't figure out what additional permissions are needed):

  "Message

  Executed as user: AD\Admin_User_Used_By_Proxy. Microsoft.AnalysisServices.Xmla.XmlaException: The JSON DDL request failed with the following error: Cannot execute the Refresh command: database 'SSAS_DATABASE_TO_REFRESH' cannot be found.. at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessageAndReturnResult(String& result, Boolean skipResult) at Microsoft.AnalysisServices.Xmla.XmlaClient.ExecuteStatement(String statement, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete) at Microsoft.AnalysisServices.Xmla.XmlaClient.Execute(String command, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete) at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.ExecuteStatement(String stmt, StatementType stmtType, Boolean withResults, String properties, String parameters, Boolean restrictionListElement, String discoverType, String catalog) at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.SendCommand(String command, Boolean withResults, String properties) at OlapEvent(SCH_STEP* pStep, SUBSYSTEM* pSubSystem, SUBSYSTEMPARAMS* pSubSystemParams, Boolean fQueryFlag). The step failed."

  Please advise.

  Thank you!

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  August 4, 2020 at 4:10 pm

  #3777253

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• alexponce10

  Newbie

  Points: 1

  More actions

  December 8, 2021 at 1:45 pm

  #3961660

  Hi,

  You must change all services logon account to a user account (that must have access on Analysis Services). The services are: SQL Server, SQL Server Agent and SQL Server Analysis Services.