[Part 2] Analysis Service is not accessible Error : A connection could not be made to redirector. Ensure that ‘SQL Browser’ is running

Hi SQL Server Folks,

In the Part 1 of the same issue I discussed about the permission of NETWORK SERVICE account on path “C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin” . I gave full permission to NETWORK SERVICE account on mentioned path and problem got resolved. It’s the path where AS is installed, In your case this path might be different.

In this Part 2 even I tried all the options of Part 1 but none of these options were working. I will summarize the issue again here:-

I installed a SQL Server 2008 R2 EE Cluster named instance on machine Windows 2003 Server R2 SP2 (64-bit) and install was successfull i.e. all SQL Server components like Database engine, AS, IS , RS & Shared Components was successfully installed. All components are able to connect through SSMS except of AS. While I was gaving a try to connect AS with instance name it was throughing the below error: 

 

But when I didn’t specified the instance name i.e. only specified SQL Server virtual name then AS Was able to connect. This was a starnge behaviour but there is a reason for that. when we are specifying only SQL Server virtual name the request will be listen on default port 2383 for AS. In any case of Clustered SQL Server 2008 the port will be use by AS is 2383. But when we are specifying SQL Server virtual name then it will rely on Browser Service.

I checked all the things as described in Part1 and found all were set. I additionally opened the ports for SQL Server Analysis service on both the node and then again gave a try to connect AS but no luck.  Then I Reviewed, msmdsrv.ini file, looks like the port information was set to default:

<Port>0</Port>

Then I reviewed msmdsrv.log file, one error message is there but not much helpful:

Message: Failed to initialize SQLSQM timer. One of the following can be the source of the problem: registry doesn’t contain all necessary information, server instance name can not be retrieved correctly, failure during timer creation, etc. (Source: \\?\F:\OLAP\Log\msmdsrv.log, Type: 3, Category: 289, Event ID: 0xC121001B)

Research on this issue, and it seems like we are having some issues with browser service. Tested with UDL and BIDS (Visual Studio), the error message is consistent, this is related to Browser Service. Looked for the browser service file: msmdredir.ini and it was available in the following location: “C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig”. At first this confused us as it seemed like browser service is 2005 service because of the path but we confirmed that on a machine, where we have only 2008 SQL Server Service. But I found that for SQL 2008 OR SQL 2008 R2 browser service will also be located at: C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ folder structure, this is consistent, i.e. same browser service will be use for all SQL Server versions (SQL 2005, SQL 2008 & SQL 2008 R2). Then I went in to services.msc and stopped the SQL Server Browser Service and started the Processs Monitor tool to capture activity related to Browser service. I started Browser Service, it came online. But I looked in to the process monitor and I found below error:- 

 It gave me hint to check the permission of AS service account on path and as expected it was missing then I gave full permission to Analysis Service account on that pecuilar path and changed the account for SQL Server Browser Service from Local Service to Local System and re-started the browser service. Now I gave a try to connect SSAS with instance name and I was able to connect.

Hope it will Help Someone.

Advertisements

4 Responses

  1. I know this is an old post but would really like to thank you for it. I was having this issue and was really lost on how to proceed, I troubleshoot what you did and now I solved the problem with your help. this has also helped me to open my mind for future errors.

    Cannot thank you enough!!!!!

  2. This does not appear to work with SQL 2012 in a two node failover cluster?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: