Analysis Services through Management Studio: cannot connect. Possible workaround.

I have a SQL server 2005 developer edition installation on my Windows XP SP2 workstation. SQL Server was installed after Visual Studio 2005 pro, and therefore after SQL Express. From what I recall, SQL Browser was also installed with SQL Express using the Local System account as the Browser service account.

As per best-security practice, I have installed 2005 developer edition with the accounts for the services as local accounts (different account for each service). Obviously, due to the VS2005 install, Browser was installed using the Local System account. I therefore created a local account for the Browser, and put it into the SQLServer2005SQLBrowserUser$MACHINENAME security group. I then switched the Browser service account to the local account using Configuration Manager, which therefore restarted the Browser service.

Next, when accessing Analysis Services (SSAS) through the management studio (SSMS) I got the following error:

Cannot connect to MACHINENAME\INSTANCENAME
|
--A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient)
|
--No connection could be made because the target machine actively refused it (System)

Changing the Browser logon account back to Local System allowed me to connect to SSAS through SSMS (after a Browser service restart).

Reading BOL for 2005, it states that the security permissions required for the Browser service account are:
90\shared\msmdlocal.ini
Read
90\shared
Read, Execute
90\shared\Errordumps
Read, Write
Looking at those folders, I could see that this was the set correctly, as implied through the SQLServer2005SQLBrowserUser$MACHINENAME security group membership which SYSTEM (set through the installation of SQL express, presumably) and the local account were members of. msmdlocal.ini does not exist on my installation.

However, the major clue that I could see was that SYSTEM account had Full access to the Shared and ErrorDumps folders by default; so I then elevated the SQLServer2005SQLBrowserUser$MACHINENAME security permissions to Full on the Shared and ErrorDumps folders, set the Browser service back to the local user account and restarted it. Bingo! I can now access the SSAS through SSMS.

Better still, I immediately reduced the SQLServer2005SQLBrowserUser$MACHINENAME to the recommended settings, restarted the Browser service, and I can still access SSAS through SSMS.

My question is ... why? What happened during my restart under elevated permissions that let the Browser service work properly? I suspect it is something along the lines of the local account taking ownership of something :-s, although I really have no idea. Is this a recognised problem? Is there a KB article on it? Do we have to do similar things to this when we change service accounts for other SQL services?

Andy

Did you use SQL Server Configuration Manager to change account or Windows Computer Manager?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Configuration Manager. Though I take it you mean the Service console snap-in rather than Computer Manager directly? However the answer is still SSCM.

Andy

|||

In this case I dont see any explanation to the behavior you describe.

Can you please try an contact Customer support about this situation.

Thanks.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I'll set up a virtual machine and try and reproduce it. Once I know I can I will get in touch with customer support.|||

There was something in your original description that may explain what is going on. According to your 1st post, you:

1. Change the account of the SQL Browser

2. Then added the account to the SQL Browser User group.

3. Then updated the account through SQL Computer Manger.

You should have only done step 3.

By doing 1 and 2, the SQL Computer Manger could have been tricked into thinking you only wished to changes the password. SQL Computer Manager needs to do other operation, individual to each service, than change the account and update the user group.

This also fits with the problem "going away" after you "fixed" the problem.

Does this seem like a possibility?

|||

I had the exact problem as you described, SQL Browser started fine, allowed access to a SQL instance, but actively refused connection to a SSAS instance.

Switching to System solved the problem, but the permanent fix was to increase the permissions to the Shared + Error dump folders as described above.

|||

Thanks so much for posting the details of your experience! I had the same problem and was able to fix it because of your post.

Cheers!