http://www.sqlservercentral.com/blogs/multidimensionalmayhem/2012/08/28/a-novel-workaround-for-an-infamous-ssas-connection-error/

Printed 2014/09/01 02:55AM

A Novel Workaround for an Infamous SSAS Connection Error

By SQLServerSteve, 2012/08/28

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:”

Screenshot for Redirector Error

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:

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.


[1] The Microsoft Time Series algorithm is predicting that at this rate, I’ll finally get that project started sometime after the whole Mayan catastrophe thing at the equinox in December. Since the Mayans had to end their calendars somewhere, just like we do every New Year’s Eve, I somehow suspect that the human race is safe from destruction. I won’t run for the nearest bomb shelter unless I run a Time Series prediction in SSAS and it turns up empty data after December 21.



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.