Can't connect to SQL Server 2016 SSAS Tabular Model on local network server from Power BI Desktop

[Douga]

Hello,

I've got a SQL Server 2016 SSAS Tabular Model instance up and running on a customer's local network server. I VPN into the customers network to access their network and the server with the SSAS instance.

I'm trying to connect to that SSAS Model from my local desktop PC (Windows 10)  using Power BI Desktop (just downloaded the most recent version). I have not been successful so far.

(Note that I can successfully connect to this Tabular Model from Excel, from this same PC, using Power Pivot.)

If I try to connect to the server using Get Data/Analysis Services, enter the server name and select "Connect Live" and click OK, I get the message "We could not connect to the Analysis Services server because the connection times out or the server name is incorrect."

If I try to connect using the Import Option and enter my Windows Credentials (AD credentials for the customer's network - same as when connecting via Excel) I receive the message "The user was not authorized".

I've seen a number of requests about this issue, but no clear answers. If this can be done and someone has a clear step-by-step procedure on how to do this, I'd sure like to see it.

Thanks for any help anyone can provide!

DWA

[leonardmurphy · Accepted solution]

Have you tried typing in the Database (cube) name? The field shows as optional, but I have one instance where, if I leave it blank, it won't find any cubes, but if I fill it in, it works just fine.

[Anonymous]

Hi all,

Seeking urgent help!

I am facing simillar problem but in my case, I am trying to connect Power BI desktop(on my laptop) to SSAS tabular(On -premise server) within my office network. I am getting follwing error

Details: "AnalysisServices: A connection cannot be made. Ensure that the server is running."

Whereas, I can connect to SQL Database on same server using SSMS. Can you Please advice 

Q1, Can I connect Power BI desktop(my laptop) to SSAS tabular server(On-Premise server) using my office network?

Q2, As SSAS uses windows authentication, do I need to create any hybrid authentication method ?

Please advice

[Douga]

mazharmh,

Your Windows Authentication username has to have the appropriate security access to the SSAS instance.

I've found that you need to enter the SSAS database name even though it says on the dialog that the name is optional.

[v-yuezhe-msft]

Hi @Douga,

I cannot reproduce you issue when connecting to remote SQL Server 2016 SSAS Tabular Model from the latest version of Power BI Desktop which is installed on my local desktop PC(Windows 10). In my scenario, the machine running SQL Server 2016 and the PC running Power BI Desktop are in same domain.

In your scenario, it seems that SQL Server 2016 and Power BI Desktop don’t exist in a same domain, in this case, I suspect that SQL Server 2016 cannot recognize your Windows Credentials. You can start SQL Server Profiler of SQL Server 2016 SSAS Tabular, then connect to SSAS from Power BI Desktop and capture details about the login process.

Besides, use runas /netonly command as follows to connect to SSAS from Power BI Desktop and check if it is successful.

1. Open command prompt and run the following command, enter the password of domain user when prompted.

runas /netonly /user: Domain\username "C:\Program Files\Microsoft Power BI Desktop\bin\PBIDesktop.exe"

2. Create a saved windows credential for the SQL Server you want to connect to.

For more details, please check the following similar blogs.
Connect to SQL Servers in another domain using Windows Authentication
Pretend You’re On The Domain With Runas /NetOnly


Thanks,
Lydia Zhang

[Douga]

Thank you for the advice! There was one small change I had to make to your command line, but I'm now able to connect, but with the new issue below. The command line I used was:

runas /netonly /user:userdomain\username "C:\Program Files\Microsoft Power BI Desktop\bin\PBIDesktop.exe"

However... I now have a different issue that I don't see when running to a SQL Server 2016 instance running locally on my PC.

It only successfully connects now when I choose the "Import" option. When I choose the "Connect Live" option I receive the following error message:

"Sorry the server you are trying to connect to doesn't have any models."

Any idea why it won't see the models?

Just to try and troubleshoot, I installed Power BI Desktop directly on the SSAS server, and I see this same behavior. Any idea why this is the behavior on the server (Windows Server 2012)"

Again, thanks for all your help!

Doug A.

[v-yuezhe-msft]

Hi @Douga,

In Power BI Desktop, go to File -> Options and Settings -> Data Source Settings -> Global permissions-> find your SSAS data source->Clear permissions, then connect to SSAS using live option and check if it is successful.

Thanks,
Lydia Zhang

[mmaldonado]

@v-yuezhe-msft, I am also having this same issue.  I am able to import data from the model, but am not able to connect live to the data source.

I am using Azure Analysis Services and created a SSAS 2016 Tabular Model with AdventureWorks2012.  I can connect with no issues in Excel 2013.  When I attempt to Connect Live I recieve the following error message:

Sorry, the server you're trying to connect to doesn't have any models.

However, when I attempt to Import, I then have no problems connecting to the data source.

Any idea on how I can resolve the Connect Live error message?  I cleared the Global Permissions and also reinstalled Power BI Desktop, but still wasn't able to connect.

Thank you,

Michael

[leonardmurphy · Accepted solution]

Have you tried typing in the Database (cube) name? The field shows as optional, but I have one instance where, if I leave it blank, it won't find any cubes, but if I fill it in, it works just fine.

[Douga · Accepted solution]

Thank you @leonardmurphy! That did the trick for me.

So in summary here are the two things I needed to do.

1. Run PowerBI Desktop from the command line under the context of the remote user.

2. Enter the database (cube) name (even though it says it's optional)

Thanks to all who helped with this!

Douga

[maghan]

In Visual Studio, where you deployed your SSAS database, go to Cube Structure > Properties > Visible setting set to True

Reference Article: https://social.msdn.microsoft.com/Forums/en-US/86f78c36-3450-4f9a-8a9d-7495c4b7bece/excel-reports-da...

[mmaldonado]

Hi @leonardmurphy, thanks for the information.  I did attempt this, but received an error message that I do not have database access or the database doesn't exist.  Both of these are not true - I can connect to the database in SSMS and Visual Studio, and am an admin in the Azure Portal as well as the Active Directory Admin on the Azure server.

It works fine when I import the data with the database left blank it works fine, but it defeats the purpose of hosting on Azure Analysis Services, imo.

It gives me the same error message when I import and fill in the database name.  I am apart of the Madison Power BI User Group, so maybe we can discuss at the next Meetup if you're there.

[Douga]

Lydia,

Thanks for your help with this.

I’m still getting the “Server doesn’t have any models” message.

As you suggested previously, I am able to connect using “Runas”, but only using the “Import” selection. Using that selection, I did not need to enter a specific username/password so I’m pretty sure that with the “Runas” my credentials are being passed along.

I double checked that the windows user is an SSAS administrator.

I’ve tried different variations for “Server” - using the server name, server IP address, domain name\server name, domainname\IP address, and the servername:2383, etc. None seem to work.

Doug A.

[Douga]

Hello,

I've got a SQL Server 2016 SSAS Tabular Model instance up and running on a customer's local network server. I VPN into the customers network to access their network and the SQL server with the SSAS instance.

I'm trying to connect to that SSAS Model from my local desktop PC (Windows 10)  using Power BI Desktop (just downloaded the most recent version). I have not been successful so far.

(Note that I can successfully connect to this Tabular Model from Excel, from this same PC, using Power Pivot.)

If I try to connect to the server using Get Data/Analysis Services, enter the server name and select "Connect Live" and click OK, I get the message "We could not connect to the Analysis Services server because the connection times out or the server name is incorrect."

If I try to connect using the Import Option and enter my Windows Credentials (AD credentials for the customer's network - same as when connecting via Excel) I receive the message "The user was not authorized".

I've seen a number of requests about this issue, but no clear answers. If this can be done and someone has a clear step-by-step procedure or reference on how to do this, I'd sure like to see it.

Thanks for any help anyone can provide!