Problem: Inability to create an SSAS Database on a server.
I was provisioned a new development server for creating and testing SSAS cubes. The problem was, even though I was an administrator on both the machine and the SSAS server, it wouldn’t create them. I don’t just mean that I couldn’t deploy, there was no scenario that I tried that would allow me to create a database. Oh, one more problem, there were no error messages, nothing in the logs, nothing anywhere that indicated I was having a problem. The following are the ways that I tried to create an SSAS database on the server:
1. Execute a CREATE Script (XMLA) that I created from a cube on a different server. The Query executes successfully, but, the database does not appear and no data files are created in the file system.
2. Right Click the Database folder and Create Database. No error message returned, I refresh the list and database does not appear in the list and no files created in file system.
3. Tried to DEPLOY from BIDS, but, get the message that it can’t find database with an ID of ‘TestCube’ on the server. Of course it isn’t there …. I haven’t created yet. Yes, I did a Deploy All.
4. I tried a Synchronize between 2 servers. Sync happens, provides % complete stats as it is working and finishes without error. But, the database does not appear and no data files are created in the file system.
5. Tried backup and restore. Created a backup from a server with the same version of SSAS, and copied the backup file to my new server and tried a restore, and it tells me my file is corrupt. (Tried this a couple of times to ensure it was not corrupt).
The storage location that was set in the SSAS Server was set to a sub-directory on a local drive used to host mount points for the SAN storage. Even though I had permissions on the disk, and I was running both BIDS and SSMS with Administrator rights (FULL CONTROL), the process of actually creating the database on the server is left to the service account.
Unless otherwise changed, sub-directories under a mount point inherit their privileges from the mount point itself. Even though I was an administrator, I did not have rights to change the security on the mount point. So, what to do? I had to ensure that the Service Account (The account that is controlling the SSAS service) has FULL CONTROL to the sub directories that Analysis Services is using to store the data and the logs.
Once assigning the service account to have FULL CONTROL on the sub directories, SSMS, BIDS, XMLA, etc were able to execute correctly and actually create the databases.