Analysis Services Synchronization fails with permissions error

  • Article

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.

at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForSoapFault<XmlReader reader, XmlaResult xmlaResult, Boolean throwError) at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForErrormlReader reader. XmlaResult xmlaResult, Boolean throwliError)

at Microsoft.AnalysisServices.Xmla.XmlaClient. SendMessage(Boolean endReceivalliException.Boolean readSession, Boolean readNamespaceCompatibility)

at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessageAndReturnResult(String& result. Boolean skipResult)

at Microsoft.AnalysisServices.Xmla.XmlaClient.Execute(String command. String properties. String& result. Boolean skipResult. Boolean propertiesXmllsComplete)

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 Microsolt.SqlServer.Management.Smo.Olap.SoapClient.SendCommand(String command, Boolean withResults,String properties)

at OlapEvent(SCH_STEPX pStep. SUBSYSTEMX pSubSystem. SUBSYSTEMPARAMSX pSubSystemParams. Boolean IQueryFlag). The step tailed.

 

To add the permissions:

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

2. Right click the server name and view the Properties

image

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

image

 

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

image

 

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.

2. Make a credential (a saved account+password) in SQL Server Database Engine. https://msdn.microsoft.com/en-us/library/ms190703(v=sql.105).aspx

3. Make a proxy account to map the credential to a certain kind of jobs. https://msdn.microsoft.com/en-us/library/ms189064(v=SQL.105).aspx

4. Change the job step which launches the XMLA command or the SSIS package which runs your synchronization to use the proxy account.