Error on Deploy of Sql Analysis Services with Sql Agent

0
votes

I'm creating a full BI solution on my SQL server. I create the SSIS process and scheduled by SQL SERVER AGENT. Now, on my Analysis Services I create the solution and deploy it on my server. From Visual Studio I can't deploy the solution correctly. Now, to have the data refreshed every hour, I schedule the SSIS and also the SSAS with a SQL Server Analysis Services Comand (.xmla).

When I run the process not from Visual studio but from SQL Server Agent I receive this error:

Il processo Business Intelligence ha restituito :Microsoft.AnalysisServices.Xmla.XmlaException: La richiesta DDL JSON non è riuscita con l'errore seguente: Non è stato possibile eseguire uno script JSON. Errore restituito: L'utente 'NT SERVICE\SQLAgent$SQLBITAB' non dispone delle autorizzazioni necessarie per modificare l'oggetto 'VenditeAgentiClienti' oppure l'oggetto non esiste.

I can't find how to grant SQL SERVER AGENT to do this operation. Ther's other solution, after the SSIS process, to update the SSAS table?

1
sqlsql-serverssisssassql-agent

1 Answers

0
votes

Your SQL Server Agent account - NT SERVICE\SQLAgent$SQLBITAB - does not have sufficient user rights. You should execute SSIS package under specific user account with enough user privileges.

Here is a detailed walk-through instruction on how to create so called SQL Agent Proxy Account and use it in executing SSIS packages from SQL Agent.