For months I’ve been itching to get the latest version of SQL Server, thanks to the addition of new functionality on the OLTP side that I can make use of, like FileTables and the new windowing functions. Two Fridays ago, I finally got my copy of SQL Server 2012 Developer Edition in the mail and was able to get it installed without much hassle – or so I thought. The setup process went fairly smoothly, as I expected after having such an easy time Installing SQL Server 2012 Release Candidate 0 back in November. During both installations the only hitch I ran into was this error, which should not have occurred in a Developer Edition upgrade: “Rule ‘SQL Server 2012 Feature Upgrade’ failed. The specified edition upgrade is not supported. For information about supported upgrade paths, see the SQL Server 2012 version and edition upgrade in Books Online.” Other SQL Server users like this one have reported the same issue. I was hesitant to upgrade to begin with, because I depend heavily on my SQL Server 2008 R2 installation and couldn’t afford anything to go wrong with the upgrade, so I went back to my original plan to do a side-by-side installation of both versions. As a result, I had to use a named instance for my 2012 version of SQL Server Analysis Services (SSAS). I vaguely remembered having problems with named instances after previous installs, perhaps around the time when SQL Server 2005 was released, but shrugged it off after the side-by-side setup process went off flawlessly. Later that night, I was able to log in to my new named instance of the relational engine and start upgrading and migrating my databases – which went quite well, except for a few problems that cropped up after I ignored my own advice from Misadventures in TDE: How to Restore an Encrypted SQL Server Database Completely Wrong. I had to laugh after typing in one of the error messages I received and finding my own post at the top of Google’s search.”

I wasn’t laughing a couple of days later, when I tried to log into the new SSAS instance to work on a series of data mining tutorials I’ve been planning to write for some time. I received an error stating that “A connection cannot be made to the redirector. Ensure that the ‘SQL Browser’ service is running,” as depicted in the window below. The subtext read: “No connection could be made because the target machine actively refused it 127.0.0.1:2382:”

After years of training myself on Microsoft products, I had the sinking suspicion this would be one of those arcane bugs that developers point out for years on end, without anyone ever identifying a definite solution or distinct cause. My worst fears were confirmed when I Googled the error and found scores of other programmers and DBAs reporting similar problems as far back as SQL Server 2005. Some of them spent weeks without access to their SSAS instances because of the issue, which Microsoft still hasn’t resolved after all of these years. I haven’t resolved it myself, but I must have made some progress in upgrading my problem-solving skills, because I was able to come up with a fix of my own within an hour or two.

This was necessary because none of the fixes posted on ‘Net to date worked for me – which wasn’t exactly surprising, because they varied widely and wildly. Even when the various workarounds allowed users to finally connect to the 2005, 2008, 2008 R2 and 2012 instances in question, there was often little indication as to exactly why they worked. Some of the solutions I tried included these:

• Starting the SQL Browser did not work. Even if it did work, it still wouldn’t be a viable solution for organizations that keep it disabled for security reasons as a matter of policy. My favorite retort was by Nicolae Mogoreanu in the Can Not Connect To Analysis Services 2008 thread at Microsoft’s SSAS forum, who wrote “Sarcastic reply: Well … did you make sure that SQL Browser is running?” That obvious fix was recommended many times in various threads I read through at miscellaneous sites, often by support staff who didn’t bother to actually read user descriptions of the problem.

• Another solution commonly recommended by Microsoft associates was to consult Resolving Common Connectivity Issues in SQL Server 2005 Analysis Services Connectivity Scenarios, which does not seem to directly address this particular SSAS bug.

• Scott at Discomboluator.Net fixed the problem by running the Repair option from Setup, despite the fact that the logs reported that no changes were made to the instance. When I tried the same thing, my logs reported twice that “Service SID support has been enabled on the service,” but I still couldn’t connect afterwards.

• The same poster reported that he had no luck with running SSAS under the LocalSystem account, which worked for others.

• Some users in this thread had success with a similar solution, by switching to the NetworkService account, but without any explicit understanding of why it worked.

• Disabling my firewall didn’t work. This wasn’t surprising, since it didn’t report blocking any traffic from SSAS. Moreover, it wasn’t reported to be a viable solution in any of the threads or blogs I consulted.

• Since my default 2008 R2 SSAS instance was running concurrently with the new named instance of 2012, I thought perhaps shutting it down would do the trick, but it didn’t. That did bring to my attention the fact that a project I thought I had deployed to the new SSAS instance through Visual Studio had actually been added to the 2008 R2 instance, because I had forgotten to change my connection settings from the default value of localhost. I originally thought the problem was confined merely to SQL Server Management Studio (SSMS), but once I failed to connect to the new instance through Visual Studio as well, I knew I had a much deeper problem on my hands.

• Connecting through the port number listed for msmdrv.exe (i.e., the SSAS process) in my firewall did not work either. I either received the same “A connection cannot be made to redirector” error or the following message: “A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.” This issue was compounded by the fact that I was unable to find a clear reference for how to use specific port numbers in the Additional Connection Parameters tab of the SSMS login window. What added to the mystery is that even when I used different port numbers, I still received the exact same message in the subtext of the error with the default SSAS port number: “No connection could be made because the target machine actively refused it 127.0.0.1:2382.” Even if I tried connecting through another port, it reported an error on Port 2382.

While experimenting with different connection parameters, I had a V8 moment – as opposed to an epiphany, or a Eureka!, because I should have thought of this at the beginning. Instead of connecting by the name of the new instance, I substituted the loopback address followed by the port number in the SSMS login window, like this: 127.0.0.1:15594. After logging in, I set a static port of 2383 for the new instance and then confirmed I could connect to it using both SSMS and Visual Studio. I also discovered that the list of SSAS server administrators was blank, so I added my own account. This didn’t fix the Redirector error when I log in using the instance name, but I am still able to connect by using my workaround, which is good enough for me at present. It’s definitely a workaround, not a fix, because I still can’t connect through normal means, but it’s better than spending hours or even weeks without access to the SSAS instance. It’s a pretty obvious stop-gap when you think about it (which I didn’t do initially, because networking is definitely one of my weak points) but I haven’t seen it mentioned yet on the Internet. Since this unfathomable error has persisted in the SQL Server community for almost seven years now, any workarounds or fixes we can add to our pool of knowledge have some value, even if they are ridiculously simple like this one. Slogging through my post to find the workaround at the end might actually be preferable to losing access to local cubes for days or even weeks, or banging one’s head on a fragile monitor while fruitlessly searching the Internet for a solution. I would prefer to add to the community’s knowledge base about data mining, since it is probably the most useful but commonly overlooked feature of SQL Server, but I had to deal with another lesson in basic problem-solving skills first. Within the next couple of weeks I hope to get back on track and post the first of a series of tutorials on data mining.[1] It is a subject I am trying to learn, not one that I have great knowledge of – which is why I probably won’t give it a title like “Stairway to Data Mining,” because I will be climbing that stairway myself.