Real Quick Post on one of the issue I faced today while connecting to SQL Analysis Services on my own laptop.
Today, when I tried connecting to named instance of SQL Analysis Services on my laptop using SQL Management Studio, I received following error
TITLE: Connect to Server
Cannot connect to *********sqlr2.
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 127.0.0.1:2382 (System)
This stimulated my troubleshooting side of the brain and I started researching the error message in Bing. I found the following article for troubleshooting connectivity issues to SSAS
However although I didn’t get the exact solution from this article, it gave me pointers to the possible workaround for the issue.
As you see in the above error message, the SSMS is unable to connect to the SQL Browser service on the local computer running on port 2382.
So, my first guess was that the SQL Browser service might not be running. However I checked and verified from Configuration Manager that SQL Browser service was up and running fine.
However SQL Browser service was running under Local System account. So I tried to change the SQL Browser Service to domain account who is also a Local Administrator on the box but that didn’t help.
So after restarting SQL Browser Service a couple of times, I decided to check event log for any errors or information and voila I found the following Warning message
“The SQL Server Browser service was unable to establish Analysis Services discovery”
I didn’t knew why this error message has occurred but this error message clarified that SQL Browser was unable to establish AS discovery and hence it was unable to listen on port 2382 where SSMS is looking for SQL Browser for SSAS.
I further verified using netstat –aonb and couldn’t find SQLBrowser running on 2382. I am not sure of what has changed since the last time I installed SQL 2008 R2 on my laptop but this definitely appears to be a bug or anomalous behaviour of SQL Browser.
I am not sure on how to fix SQL Browser without reinstalling it. However to workaround the issue I ran named instance of Analysis Services on static port by specifying the static port in <Port> </Port> section of msmdsrv.ini file located in following location in my case
C:Program FilesMicrosoft SQL ServerMSAS10_50.SQLR2OLAPConfig
Following the restart of SSAS service, I ensured that ssas is running on the static port specified by me by using netstat –aonb
Finally I connected the SSAS instance by specifying the connection string as MachineName: <Port>
Hope this helps !!!
Premier Field Engineer