Unable to Connect to SQL Server 2012 Analysis Services from SSMS
Recently we received an alert for a Analysis Service job failure. Upon checking the job history noticed and error that the connection to Analysis service named instance has failed. As part of troubleshooting, we tried to connect to the Analysis Service Named Instance from SSMS, but it returned an error too as mentioned below.
No connection could be made because the target machine actively refused it IPAddress:PortNumber
This issue can occur if the server that hosts the named instance of SSAS was configured to use IPv4 and IPv6 when SQL Server 2012 was installed. Then, the server was later reconfigured to use only IPv6.
To resolve this issue, follow these steps:
– Stop the SQL Server Analysis Services service. If Analysis is cluster aware, then bring the Analysis Cluster resource offline instead of stopping the service directly.
– Open the Msmdredir.ini file in Notepad. By default, the Msmdredir.ini file is located in the following folder:
C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig
– Backup the existing ‘Msmdredir.ini’ file.
– In the Instances section, verify that the values for the Port property and the IPv6 property are different for the named instance.
– Delete the PortIPV6 property from Msmdredir.ini file.
– Save the Msmdredir.ini file, and then exit Notepad.
– Start the SQL Server Analysis Services service or bring it Online from cluster.
This is applicable on below versions of SQL Server
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.