Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

“Unable to Establish Server Mode” Error in SSMS

with 14 comments

This morning when I tried to connect to my local SSAS 2012 (RTM) Tabular instance in the Object Explorer in SQL Server Management Studio I got the following error message:

TITLE: Microsoft SQL Server Management Studio
——————————

Error connecting to ‘localhost\tabular’.

——————————
ADDITIONAL INFORMATION:

Unable to establish server mode. (ObjectExplorer)

image

This was an error I’d seen before several times (as had Alberto – see here – and a few other people), and had previously associated it with memory issues and/or corrupted databases. However in this case I was confused because I was still able to query the SSAS Tabular instance in question from Excel and deploy to it from SQL Server Data Tools, so I was pretty sure there wasn’t anything wrong with the instance.

After a bit of looking around I found what the problem was. A few days ago I had been using SQL Server Management Studio to write queries against a workspace database and I had set the name of the database in the ‘Connect to database’ dropdown on the Connection Properties of the Connect to Server dialog, as shown below:

SNAGHTML23b0336

Now the SSDT project associated with this workspace database had its Workspace Retention property set to ‘Unload from Memory’, so when I closed the project the workspace database disappeared from the server. However in SQL Management Studio the Connection Properties dialog was still trying to connect to the database even though it was no longer there, and instead of giving a sensible error message it was giving me the “Unable to establish server mode” error. Fixing the problem was simple: I just went to the Connect to database dropdown and selected “<default>” instead of the name of the workspace database.

I would imagine that a lot of people will run into this issue, not only when they have been connecting to workspace databases but also if they connect to regular databases that are subsequently deleted. Hopefully this blog post will save them a lot of time spent wondering what’s gone wrong!

Written by Chris Webb

May 28, 2012 at 4:56 pm

14 Responses

Subscribe to comments with RSS.

  1. Well, once again a very helpful blog post. I was ready to reinstall SSMS on my VM. Since I was able to connect to the SSAS instance from SSMS in other servers I thought there was something wrong with the application in my VM but something was telling me that there was more to it so I had not reinstalled yet.

    I had connected to a workspace database about a week ago and I also have the Workspace Retention property in SSDT to “Unload from memory” I don’t recall changing the database in the connection properties but that was days ago so maybe I did. I followed your instructions and the connection property was in fact pointing to the workspace database. I made the change to default and I am able to connect to the instance once again.

    Thanks Chris!
    @luisefigueroa

  2. I am facing the same issue, even after changing the connection to “”

    Pavan

    October 11, 2012 at 8:27 am

    • It could well be that your database has become corrupt, or possibly that you’re trying to connect to a database in SSMS that doesn’t exist any more (go to the Connection Properties tab in the connection dialog and make sure that the Connect to Database dropdown is set to default, or to a database that exists).

      Chris Webb

      October 11, 2012 at 9:03 am

  3. Hi Chris
    I am facing the same issue. ‘Connect to database’ dropdown on the Connection Properties has <>. Even tabular database is not corrupt as i am able to access it from my Excel 2013. I think the problem is with the SSMS utility.

    Monish

    January 2, 2013 at 12:44 pm

    • The problem is not with SSMS utility because I am able to access Tabular Model from other machines. But tabular model to which I am not able to connect, is easily getting connected in Excel 2013. There is something wrong in the latter, I think.

      Monish

      January 2, 2013 at 3:42 pm

      • It’s hard to say what’s going on. Maybe a security permissions or firewall issue?

        Chris Webb

        January 2, 2013 at 4:07 pm

  4. Thanks Chris for the reply. The issue is fixed. I repaired the SQL Server Tabular instance using the setup.exe and restarted the SQL Server Brower, SSAS service and Database engine. It is working well now.

    Monish

    January 3, 2013 at 5:20 am

  5. And this morning you saved my day – I forgot this issue and it’s still here in SP1 CU4 :-)

    Marco Russo

    July 14, 2013 at 11:00 am

  6. I’ve had the same issue. But my issue is certainly not related to the database SSMS tries to connect to. Funny thing is that when I go to options and click on Browse Server, it asks to connect to the server to browse the databases. This works just fine, I can see all my databases, I select one, try to connect, but still get the above error msg.

    Other funny thing;
    When I RDP to the server and try to connect to the Tabular Instance locally with SSMS, this works just fine.

    Solution:
    I ‘just’ restarted the Tabular Model service and problem was gone.

    I’m also a bit curious what happens when you restart a Tabular Model service? Since all data is kept in memory I suppose with a restart all data is lost and it needs to be loaded again? This is probably the reason why it took SSMS so long to get connected the first time after restart?

    Best regards,
    Ignace

    Ignace Van Buggenhout

    August 7, 2013 at 12:19 pm

    • No, the data doesn’t need to be reloaded when the service is restarted – SSAS keeps a copy of the database on disk, and it’s this copy that gets restored when the service starts.

      Chris Webb

      August 7, 2013 at 4:28 pm

      • Thx for the information Chris!

        Best regards,
        Ignace

        Ignace Van Buggenhout

        August 8, 2013 at 2:09 pm

  7. Same thing is happeing to me but I do not have tabular model in place. So what can be happening. I can connect from Excel to the cubes, I see them when I brose but I can not connect to the server. I repaired SQL, stopped the services, reboot the server and nothing.

    Maria Royo Valdes

    February 3, 2014 at 10:14 pm


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.

Join 3,072 other followers

%d bloggers like this: