SSAS Backup to Remote Location

Question

I followed the steps in the best answer on this question - How can I dynamically back up all SSAS databases on a given instance? - and can successfully create backups of our BI Cubes to a local drive on the server (Windows Server 2008 R2 running SQL Server 2008 R2 Standard), but no matter what user I attempt to execute the package as, I cannot save the file to a remote drive (via UNC or mapped network drive).

The package simply fails with the following non-descript error...

File system error: The following error occurred during a file operation:  Access is denied.

I have created new shares with read/write access on our NAS devices and I have tested whether the user that is executing the package (whether in VS, SSAS Backup Server dialogue or SQL Agent) can create files (and folders, as per the above linked article) and in each case, they can, but the package will always fail when writing the .abf file off-server.

The user accounts in question are also in the CubeAdmins role.

The XMLA is as follows (with UNC replaced)...

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>BI Database</DatabaseID>
  </Object>
  <File>\\UNC\SQL\BI\BI Database.abf</File>
</Backup>

Have I hit a limitation of the SQL licensing? When I was attempting a SSAS database restore, I did run into a licensing error with regards to Storage Locations, but presumably that is just storing the OLAP cubes outside of the default location.

All help is appreciated!

Answer 1

Ok, this was the answer. The SSAS Server does the actual backup, not the SQL Agent or user executing the job, so it needs to be running as an appropriate user with permissions, rather than the SYSTEM account.

I updated the service entry, restarted the server and all is fine.