A Novel Workaround for an Infamous SSAS Connection Error

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:

  • 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.
  • 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.


[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.

About these ads

About Stevan Bolton

I am a VB programmer and SQL Server DBA with an interest in MDX and multidimensional applications. I have an alphabet's soup of certifications: * 3 MCTS certifications in SQL Server 2008 R2, including a recent exam in MDX and Analysis Services * an MCDBA in SQL Server 2000 * an MCSD in VB6. I've kept up with each version of VB since then but haven't taken the newer exams * I also have a Master's in American history with a concentration in foreign affairs, as well as some work towards a doctorate in Latin American history * My B.S. is in print journalism I'll be posting whatever code I can to help out the SQL Server and VB developer communities. There is always someone out there more knowledgeable, so if you're a guru, feel free to correct any information I might post. I haven't yet been paid professionally to work with some of the technologies I've been trained in and enjoy, like MDX, so the word of those who have ought to carry more weight. There's a shortage of information on some of the topics I'll be posting on, such as the arcane error messages in Analysis Services (SSAS), so users might still find some value in my posts. If you learn of any job openings for MDX, SSAS, SQL Server and VB, feel free to E-mail me.

Posted on August 28, 2012, in Uncategorized and tagged , , . Bookmark the permalink. 11 Comments.

  1. Thanks, nice workaround, I’m having this issue on a SQL2012 SSAS instance, none of the other suggestions have worked for me either.

    • Thanks – if you have any trouble with the workaround, let me know. I realize a few weeks ago that I accidentally posted an earlier version of the workaround in which the MaxIDDifference column didn’t work correctly (which was pretty embarrassing, given how I bragged about that column was populated by my means of circumventing the INSERT EXEC restrictions, LOL). The right version is working just fine on my development machine, so I’ll edit the workaround in my post by tomorrow, hopefully. If you didn’t reply to my thread, it would have slipped my mind again – thanks ;)

      – Steve B.

      P.S. I also forgot to mention that a few months after I published this post, I saw the identity values on a couple of columns jump by small increments of about 5 or 6 on a couple of occasions. It was only a couple of times, but there was no doubt about it; I saw it unfold before my eyes on Profiler. Most of the other references I’ve seen to the problem on the Web have been in increments of some base-10 multiple, most often 1,000. Just something else to watch out for.

  2. I have problem with workaround.

    Got the message: No connection could be made because the target machine actively refused it 127.0.0.1:2382

    What steps should I take to fix the problem, any screenshots would be helpful.

    I am very new to SSAS.

    thanks.

    • Here are some things you can try:
      1. Try substituting port 2383 for 2382 when connecting.
      2. Check in your firewall and see which port that particular Analysis Server is trying to connect with. Then try to connect to 127.0.0.1:portname.
      3. Make sure the Analysis Server has been started. This is actually the most common culprit for this error message.

      Also, are you trying to connect to the default instance or a named instance? The workaround I mentioned here was mainly pertinent to named instances on the same computer as another default instances.

      Hope that helps,

      Steve B.

  3. Hey Stevan, I have similar issues except from a sharepoint 2013 server with ssrs in integrated mode, I try to connect to my SSAS tabular model, if I use stored windows credentials, no problem, however if I switch to windows uathetication (integrated) is fails with “a connection cannot be made to redirector” checking the sharepoint logs it says No such host is known, which obviously is wong since I can connect with stored credentials without changing the connection string…any idea what could be causing this? broswer is running under local system, kerberos delgation is setup, no firewall etc. I basically tried every signle thing I found until I came across your post…thanks a ton

  4. Hey Greg, I’m not sure if can be of much help with that, since I’m not that familiar with SharePoint, although I played with the 2010 version awhile back. I’m also focused on the MDX side of data warehousing instead of DAX and tabular stuff – so if the problem is related to either that or SharePoint, it’s unlikely I’ll be able to figure it out. I’m more familiar with Reporting Services but haven’t experienced this error with it to date. My only suggestion might be to try to connect via the loopback address or the address of the Analysis server, followed by the port number in the stored credentials that worked. That’s similar to what I did here to rectify the problem I had, but I suspect yours may stem from a different issue related to SharePoint, RS or the tabular mode. Whether or not you are trying to connect to a default or named instance of Analysis Services may make a big difference, since this turned out to be the culprit in my case. Good luck, sorry I couldn’t help you more.

    – Steve

  5. Ok thanks anyway Steve, I have the same environment in DEV with SSAS named instances and could make it work after a big battle aroudn the kerberos side of things but in this one I get the proper kerberos tickets it’s just the datasource that tells me no such host where I can ping it, connect to it from the sharepoint server with SSMS without any issues, this is puzzling indeed…ah well I guess I’ll have to figure it out myself! :) Cheers

  6. I found that changing the service account for “SQL Server Browser” to an admin account or the same account that is running SSAS fixed the issue for me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: