By Chris Exley
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).
Resolution:
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.
Problem solved!