Recently I uninstalled an instance of Analysis Services from a server running SQL Server 2008 R2. It was a development server hosting Reporting Services, a couple of instances of SQL Server, and a couple of named instances of Analysis Services.
The uninstall was uneventful, no error messages or issues were reported by the SQL installation program.
After the uninstall was complete I rebooted the box. Then I tested the connectity:
- Connect to SQL Server instances. Check
- Connect to Reporting Services. Check
- Connect to the remaining named Analysis Services instance… Fail!
At that point my day took a turn for the worse. When I attempted the connection in Management Studio I got an error stating:
A Connection Cannot be made to redirector.Ensure that 'SQL BROWSER' Service is running
No Connection could be made because the target machine actively refused it 127.0.0.1:2382
I had been attempting to connect remotely from my desktop so I thought I’d have a go logging on to the server and connecting locally, just in case the remote connection property had been switched off somehow. No joy.
I checked that the Analysis Services and SQL Browser services were both up and running. They were.
Out of ideas of my own I turned to Google. It appeared this was a relatively common problem, with people complaining about it on every version since SQL Server 2005. Unfortunately while there was a lot of whinging about the issue nobody seemed to have understood or actually solved it.
The error message mentioned ports so I checked the firewall configuration to ensure that port was still configured to allow connections through. It was.
The common work around people seemed to come up with was change the account that the Analysis Services and SQL Browser services are running as so they use the inbuilt Local System account. Not exactly a solution, the instance could be connected under the existing service account prior to the uninstall after all so this looked like a coincidence rather than a fix of the problem. Plus the SQL Browser service was already running as Local System.
I checked the ini file that SQL Browser uses for port mappings and everything was consistent with the equivalent file on a different server were the connectivity was working correctly.
I didn’t really want to uninstall, reinstall, and reconfigure the instance I could no longer connect to. So next I tried running the Repair option in the SQL Configuration Manager over the shared components which includes SQL Browser. No luck.
Finally I ran the Repair over the Analysis Services instance I couldn’t connect to. There was no logical reason for this, I hadn’t changed anything on that instance so it should all still be configured and functioning as it had been prior to my uninstall.
The Repair exercise reported back after 10 minutes that everything was fine and it didn’t output anything that indicated it had changed any configuration settings. However when I attempted to connect to the remaining named Analysis Services instance it succeeded. At which point I did the victory dance and decided to write up my misadventure here in case I ever run into the problem again and by then can’t remember what I did to remedy it.