Analysis Services Synchronization fails with permissions error

When you want to run Synchronization to copy an Analysis Services database from one server to another, the account running the synchronization needs to be a server admin in the Analysis Services instance receiving the database.

For example, when running a job to do synchronization from a SQL Agent job, you may see the following error:

Executed as user: domain\SQLAgent.

Microsoft.AnalysisServices.Xmla.XmlaException: Either the ‘domain\SQLAgent’ user does not have permission to restore the ‘MyDB’ database, or the database already exists. Backup and restore errors: An error occurred while synchronizing the ‘MyDb’ database.

1. Open the Management Studio window, and connect to your instance of Analysis Services.

2. Right click the server name and view the Properties

3. View the security page, and note if there are any users already present

4. Click the Add button and enter the name of the account which needs to run the synchronization, such as domain\account

By the way…. if you are going to run synchronization, you may want to set up the scheduled job in SQL Agent and on the job step use a Proxy Account. Then you can limit the jobs which use the credentials of the proxy account, and add that account as a Server Admin in Analysis Services so that you don’t open up the SQL Agent account so widely to run work in Analysis Services.

Here are the instructions on using Proxy Accounts

1. Make an appropriate windows account (local account or domain account) in Users and Groups, or ask the Domain Admin to do so.