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!
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
I am facing the same issue, even after changing the connection to “”
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).
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.
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.
It’s hard to say what’s going on. Maybe a security permissions or firewall issue?
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.
And this morning you saved my day – I forgot this issue and it’s still here in SP1 CU4 🙂
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
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.
Thx for the information Chris!
Best regards,
Ignace
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.
Are you talking about the cube browser in Visual Studio not working? Are you running into this problem? http://www.bp-msbi.com/2012/01/ssas-locale-identifier-bug/
Good post.