Connecting live from Power BI Desktop to SSAS generates this error: The Server you’re trying to connect to doesn’t have any models or you don’t have permissions to access them.

I recently ran into an error while connecting live from Power BI Desktop to a SSAS server. Everything was on-premises.There was no cloud component involved. I had full admin rights to the SSAS server and could see all databases and models from SSMS and other tools but was getting this error from PBI Desktop.

The Server you’re trying to connect to doesn’t have any models or you don’t have permissions to access them.

If I explicitly specified the name of the database I wanted to connect to, then it would connect and show me the database contents but if I did not specify the database name, I would get this error. The question is why? Obviously it wasn’t a permissions problem because I could connect to that database if I specified its name and I was admin on the SSAS server and could see that database and other databases from other front end tools such as Excel and SSMS. You can get this exact same error because of permission issues and in those cases this is a valid error. Here I am only exploring the case, where all correct permissions are in place and I still get this error.

The problem was …

The default database on that server was created with a script as an empty shell database for someone to use later. It has stayed empty and never deployed to. This empty database was causing the problem. Here is an example of a simple JSON script that creates an empty SSAS database:

{

"create": {

"database": {

"name": "test 3",

"compatibilityLevel": 1200

}

}

}

Note: This exact same error can happen with SSAS Multidimensional if the first database created on the server has a cube that is in an unprocessed mode.

Default Database on SSAS

The first database that is created on a SSAS server becomes the default database. There is no server property that you can set to modify this later. At least I could not find one through SSMS. I suspect this is in one of the meta data files but I did not find it readily. On the other hand, as I will explain later, there is a SSAS property that can disable automatically setting the first database to the default database but that does not give a choice to set the default database explicitly. Changing this setting has its own implications that I will get into later in this post.

How I found out the cause of the error

I started a SQL Server profiler trace on the SSAS server (You can use Extended Events as well) and attempted the same live connection from Power BI Desktop with different databases on the server. I noticed something very interesting.  The query that Power BI Desktop submits to SSAS to discover the list of databases, seemed different in the following situations:

1. The SSAS sever is completely empty. No databases exist on the server.
2. The state of the of the default database. (Empty or unprocessed for Multidimensional)
3. The AutoDetectDefaultCatalog SSAS server setting.

Let’s explore these.

1) The server is completely empty.

In this case, there are no database on the server. Power BI Desktop can connect live to this server successfully, even though I got an empty list of databases as expected.

In this case, SQL Server Profiler captured the following query from Power BI Desktop:

Select [CATALOG_NAME], [DESCRIPTION], [DATE_MODIFIED], [COMPATIBILITY_LEVEL]
from $System.DBSCHEMA_CATALOGS 

Notice how this query was run against the server and that the DatabaseName column is empty in the trace shown above. This is possible because this is a server level query. It uses one of the SSAS DMVs (Dynamic Management Views) to get metadata. It returns a list of all databases on the server. You can run this query in SSMS in a DMX query window. Here is an example of what this query would have returned if there were databases on this server:

To summarize, Power BI Desktop worked successfully in this case. There were no databases to show and no errors were generated. The reason I included this case is for you to see how the query changes in the next case.

2) The default database is empty.

Continuing with the same empty server, I scripted an empty database using the script shown previously. The first database created on a server becomes the default database on the server. This is what I could see at this point from SSMS on this server:

I went to Power BI Desktop and attempted to live connect without specifying the database name. I got this error:

The question is: What did adding an empty scripted database do that changed the Power BI Desktop’s behavior?

Here is the DMX query that Power BI Desktop submitted this time which is different than the one in case 1:

Select [CUBE_NAME] from $System.MDSCHEMA_CUBES where [CUBE_SOURCE] = 1

Notice how in the Profiler trace, the Database is set to “test 3”.

If I run this query in SSMS, it returns no results because that database is empty. There are no models defined in it yet. If there were models in the database, I would have seen something like the following:

Note that this DMV has existed since SSAS  Multidimensional, hence the name Cube but it works against both Tabular and Multidimensional SSAS databases, as long as they have a model/processed cube.

3) An empty default database and a non empty database

To test this further, I added another database from a backup file of AdventureWorks. This is what SSMS was showing:

Select [CUBE_NAME] from $System.MDSCHEMA_CUBES where [CUBE_SOURCE] = 1

I went back to Power BI Desktop and attempted to live connect as before without specifying a database name. Power BI desktop still generated the same error even though AdventureWorks was fully browsable from SSMS.

Select [CUBE_NAME] from $System.MDSCHEMA_CUBES where [CUBE_SOURCE] = 1

This is the query that Power BI Desktop submitted to SSAS, still just one query and different than the query in case 1:

Notice how this query still ran against the default database which is “test 3”, the first (default) database created on the server. 

This is the exact case I was running into in the beginning. I had several databases with models in the them that were fully browsable from SSMS and Excel. There was one empty database that happened to be the default database and was causing the error because the query ran against it and returned no results!

When the above query returns no values, Power BI Desktop shows the error message. This seems like a bug that will hopefully get fixed at some point.

Obviously, one way to get around this error, is to either specify the database name you are trying to connect to or to remove the empty database. In the latter case, another database becomes the default database. You can also delete the empty database and script it again. It will look exactly the same as before except that it won’t be the default database. I tried this case to make sure this whole problem was really a default database issue and not the existence of an empty database.

4) A non empty database and an empty default database

I deleted “test 3” and created it again with the same script as before. This time it was not the default database anymore. SSMS looked exactly as before:

I went to Power BI Desktop and tried to live connect. This time it worked! This proved that it was not the empty database causing the issue because of its emptiness. The problem was that it was empty and the default database at the same time. This is what the traces showed this time:

Power BI Desktop submitted 3 DMX queries, 2 of which are identical and ran against Adventure Works (The default database):

Select [CUBE_NAME] from $System.MDSCHEMA_CUBES where [CUBE_SOURCE] = 1

This was followed by this query:

Select [CATALOG_NAME], [DESCRIPTION], [DATE_MODIFIED], [COMPATIBILITY_LEVEL]
from $System.DBSCHEMA_CATALOGS 

4) AutoDetectDefatInitialCatalog

This one is exactly the same query that ran in case 1 (empty server). In case 2, this query did not run because the first query had returned no values and Power BI Desktop stopped with an error message displayed.

How does Power BI desktop know which database is the default database? As mentioned before, the DXM queries were all submitted in the context of the default database, but how? It is not that Power BI desktop knows which database is the default database, it is that it creates a connection to SSAS without specifying a database name (since I did not explicitly specify one) and it lands on the default database.

This behavior of creating a connection and landing on the default database, is similar to what happens in SSMS when you connect to a SSAS server. You always get connected to the default database and you can change it by changing the name of the database from a drop down box. This behavior is controlled by a setting in SSAS called AutoDetectDefaultInitialCatalog.

AutoDetectDefaultInitialCatalog is one of the Advanced server properties and it is set to True by default.

Warning!

Make sure you understand the implications of changing this setting, especially on a production database, before you change it!

When this setting is set to Ture, when you connect to the server using SSMS, you get connected to the default database right away. You can see the default database in the drop down to the left of the Execute query button.

On the other hand, when AutoDetectDefaultInitialCatalog is set to False, when you connect to the server using SSMS, you get a query that is no connected to any database. You can see this in the drop down to the left of the Execute query button.

With this setting set to Fales, I repeated cases 2 and 3. This time everything worked even with the empty default database.

How did this work? Power BI desktop only submitted the following query:

Select [CATALOG_NAME], [DESCRIPTION], [DATE_MODIFIED], [COMPATIBILITY_LEVEL]
from $System.DBSCHEMA_CATALOGS 

Note how this query ran against the server and no particular database. In this case, Power BI Desktop was able to show the list of all databases, even though the first database created (“test 3”) was still empty.

Please remember that changing this setting can have other implications so I don’t recommend you change it permanently. I only did this for testing. The problem you may run into if you set this setting to False, is that it an cause problems for any code that was dependent on the connection landing on the default database. I have seen this in the past at one client, where custom .NET code was getting metadata from a SSAS database without realizing that they were getting results because their database happened to be the default database. Of course, this was not best practice; however, the point is you should change a production server’s settings very carefully and be ready to work through unforeseen consequences.

Summary

Power BI Desktop as of Sept 2019, has a small bug where you would get an error if you try to live connect to a SSAS sever and explore the database on it without specifying a particular database.

The Server you’re trying to connect to doesn’t have any models or you don’t have permissions to access them.

The error happens when the default database is empty on SSAS Tabular or if the default database is unprocessed in SSAS Multidimensional. You can get around this by removing and adding the empty/unprocessed database so that it is not the default database (assuming you have other good databases on that server.) The other workaround is to change the AutoDetectDefaultInitialCatalog to False but this can have other unforeseen implications on custom code connecting to the sever as explained previously.

Note: You can get this error under other valid circumstances as well. For example, if you truly don not have access to a server. In that case, the error is valid.