Here’s a little gem I discovered today. I am running down a permissions issue with BAM and SQL Server Analysis Services. On my local dev box, I wanted to fully emulate the permissions from my full-blown dev environment, so in my Services MMC, I changed the Analysis Services log on account from LocalSystem to the service account we’re using in our full environment. I started the service and BAM (no pun intended) I get the error:
The SQL Server Analysis Services (MSSQLSERVER) service failed to start due to the following error: Access is denied.
Not much else in the error logs either. After digging around Bing for a few minutes I found this article (Resolving Common Connectivity Issues in SQL Server 2005 Analysis Services Connectivity Scenarios). In it it had my Access Denied message explained as “If the service account is changed by using Computer Management or Services rather than SQL Server Configuration Manager, access to the encryption key file (CryptKey.bin in the ..\Data folder) and other files and folders is not automatically granted to the new service account.”
So my problem was that I changed the account and password in the Services MMC instead of SQL Configuration Manager. I thought that at least as far as starting/stopping and setting credentials for the SQL services, the Services MMC and the SQL Configuration Manager was interchangeable. WRONG!
Once I set the credentials using Configuration Manager, all was well