Life gives you learning lessons from unexpected times, here is one such experience that I felt is worth sharing. It may be a surprise to my blog readers that I also have SQL Server Analysis Services installed on my primary machine. I use it occasionally because most of the time I work with the SQL Server Database engine. One fine day, I opened SQL Server Management Studio and instead of a database engine, I selected analysis services (this was a mistake) and pressed on the connect button. Below is the error which I received for SQL Browser:
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 is the first time I saw such error while connecting to SQL Server, but soon I realized that I was trying to connect to SQL Server Analysis Services. I could have left it there because I don’t use it, but I did not. Since there was a little time, I thought it is worth solving this challenge in front of me. Here is the step by step resolution of what I did:
- Made sure that SQL Server Analysis Service was up and running. This got confirmed from my SQL Server Configuration Manager.
- As I do always, read the error message again and verified that the SQL Browser service was up and running.
- Since this was a local connection, I turned off the firewall as well.
None of the above solved the problem. The Port resolution is done by the SQL browser service in case of SQL Server connectivity. So I thought that it could be a permission issue – just a guess based on the error message.
I noticed that SQL Browser was running with the local system account. I found on the internet that changing account helped many. So I used the SQL Server Configuration Manager to change the service account for the SQL Server Browser. I have used below.
Here are the steps:
- Open SQL Server Configuration Manager
- Right click “SQL Server Browser” and choose “Properties”
- In the “Log On” Tab select “Local System” under “Built-In” account.
Have you ever seen such error and found some other way to fix it? Please comment and let others also know. The best way to learn something is by sharing.
Reference: Pinal Dave (https://blog.sqlauthority.com)