Connecting PBID to SSAS Tabular when not in domain

[erop]

Hi!

As you know SSAS allows only Windows authentication to provide access to it. While connecting to SSAS model with Excel I can choose either Windows authentication or enter username and password to get access to SSAS. But in Power BI Desktop we have only Server field for entering SSAS server address. This should be enough if one logged onto machine as a domain user. But what if my machine is not a domain member? Is there any possibility to provide Windows username and password for connecting to SSAS model?

[erop]

First of all thanks everybody for replies!

@greggyb receip really works. Additional thanks, @greggyb , for the link about runas trick!

But I found out that there's an "official" way to provide credentials for data source. You should use File menu - Options and settings - Data source settings - choose datasource in the list and click Edit - one more click on Edit button under Credential area - and here we can enter credentials needed. Frankly it works strange, at least in my case.

First, it does NOT authenticate the user with alternate credentials if Windows authentication chosen. Scratching head with one hands I just for fun tried to use Basic authentication - and voila! - I can browse SSAS models. (Second,) But all of them! Not only the user has permissions to! I connect SQL Server Profiler to SSAS and tried to login once again. And it shows that Administrator logs onto SSAS, not the user which credentials I used. If runas trick is used  profiler confirms that proper users is logged and I can show only allowed model in PBID Navigator.

P.S. All the experiments are made on domain member with PBID installed to the SQL Server instance on another domain member through persistent demand-dial VPN connection between domain networks.

[Greg_Deckler]

Are you trying to connect Live? That is definitely not supported for Live, see:

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-analysis-services-tabular-data/

The question about whether it is secure.

Seems to indicate that if you are not using Live, that you may have other authentication options...

[greggyb · Accepted solution]

I've not had a chance to test this for PBI Desktop, but I see no reason it shouldn't work, since I use it daily for Visual Studio and SSMS when doing client work.

runas /netonly /user:<domain>\<username> <path to PBID executable>

Here you'd use the credentials that have access in domain\name format. This tells the program that when prompted for identity it will pass the name in the user option. When running this command, you'll be immediately prompted for a password before the program launches. Then it's as if you'd logged in with that domain user.

This obviously requires that you **be** on the target domain, physically or by VPN.

Here's some more reading.

[bahaaa_bouakl]

Thank you for the link. Your reply saved us a lot of time. Thx again.

After a lot of retries and internet searching, this is also the solution in case you are accessing a multidimentional SSAS model (live connection, not import) from another computer and not having an installed AD or windows domain.

(both server and Client PC on workgroups!!)

so the trick is to define a local user on the server with appropriate access right to SSAS cubes then use a batch file to run power bi.

batch command is:

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

double cclick the batch file and you will be prompted to enter a password.

enter the password and when power bi opens you can connect by entering the isntance name (even if it is not the default instance but a named instance, for ex: MyServer\SQL2017). and cclick continiue, et voila...you will see the list of cubes.

Hope this help.

thank you all for your efforts.

[Anonymous]

runas /netonly /user:<domain>\<username> <path to PBID executable>

 Hi all,

I am trying to connect SSAS Tabular database(On premise) from my Power BI desktop(My laptop) but I have trouble doing that, I am trying to follow this soloution but bit confused. Domain and username should be from On-premise server?  Can anyone please guide me

user:<domain>\<username> 

[VPetkevic]

user:<domain>\<username>

You are right mazharmh,  domain \username is from remote On-premise domain

run WHOAMI command from Powershell or CMD

on on-premise server to see your credentials.

For example this would run under user Andy from NorthCorp domain credentials

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