“Unable to Establish Server Mode” Error in SSMS

Reposted from Chris Webb's blog with the author's permission.

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)

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:

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!

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com .