Sql-server – Process Data cube from job on SQL server

sql serversql server 2014ssas

I have an SQL Server Agent job on server(X) that has a step that is supposed to process a data cube on a remote server(Y).

Whenever I run the job it fails and says that server(X) does not have permission to process the Cube or it does not exist. I believe I have the job set up correctly but how do I give access on server(Y) to server(X) to process the cube?? Below is the script I am using. with the "SQL Server Analysis Services Command"

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
<Object>
  <DatabaseID>Analysis Services Project1</DatabaseID>
  <CubeID>S2E</CubeID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Batch>

When I go to add the server(x) to the admin role on server(Y) via object explorer I cannot find the server through check names button. Please help.

I am using SQL Server 2014

Best Answer

There are 2 ways you can go about this. You can either create a service account for the sql agent service and give that account permissions on the olap server, or you can create a sql agent proxy account.

Personally I think the proxy account is the cleanest solution.

To create a proxy account refer to this msdn article: Create a SQL Server Agent Proxy.

This way you can for example create a user DOMAIN\OlapUserOnY which is only used for agent jobs running things on the Y server Analysis Services.

If you want to create a service account you have to be sysadmin on the SQL server where agent is running to be able to configure that on the Agent job.

Then on the agent job you just select either the proxy account or the service account in the run as field.

Refer to Create an Analysis Services Job Step to learn how to create a job and specify the account it's running under.

From that link:

In the Run as list, select a proxy that has been defined to use the Analysis Services Command subsystem. A user who is a member of the sysadmin fixed server role can also select SQL Agent Service Account to run this job step.

Related Question