Dataflow credential conflict

[rassen]

I'm not sure if I do things right but I am using dataflow to get data from 2 source:

Sharepoint though my organzation window account

SQL Server through on-premise gateway

Each load just fine but when I sign in one source the other would sign out.

Sign in gateway then Sharepoint table will display error: SharePoint: Request failed: The underlying connection was closed: An unexpected error occurred on a send.

Sign in Sharepoint then the gateway will sign out

(I do get this notification when switching sign in: Changing the on-premises data gateway will apply to all queries in this mashup. )

So my understanding is that I can only choose one connection is that correct ? If so what is the work around ? (I also tried to add the sharepoint source into the gateway but keep getting authentication error message)

[collinq]

Hey @rassen ,

Are these two totally separate userids and passwords?  Is one local and one Active Directory?  It sounds to me like one userid has permissions to one source and the other userid has permission to the other source.  If that is true, my thinking is that you would set up two gateways - one to SharePoint and one to SQL.  And then, when you upload the file you use the appropriate gateways and set the permissions separately.  

[rassen]

Sharepoint use my Microsoft Window login and SQL Server is "basic" connection with separate id and password

I can't setup  sharepoint in the current gateway though, it's something to do with user permission issue

[collinq]

Hey @rassen ,

The solution to this issue is to go to the Data Source Settings in the file and then sign into each datasource separately with the two userids.  The key is not what you are signed into Power BI as but what the sign in is for each data source.  This is an example of three separate data sources with three separate userid/password combinations in a file that I have:

[rassen]

This is Desktop right, as I have mentioned, I have no problem with manual refresh on Desktop (all data sources signed in) 

What I'm having issues with is the scheduled refresh on service, I did several test and this is what I realized:

_Personal gateway work perfectly and I can sign in both SQL server and Sharepoint then refresh work just as intended. But this require my computer to stay up to happen.

_Without gateway SQL server will not sign in

_With On-premise gateway I can sign in SQL server but cannot authenticate Sharepoint 

[collinq]

Hi @rassen ,

Yes, I was showing the screenshot from the Desktop.  However, if you go to settings on the Data Source in the Service, you can do the same thing.  And then you can use the "Data Source Credentials" in the Service and set the permissions to be the same way.  And that will allow you to use two separate permissions.

And, as you stated, the gateway will ahve to be used for SQL since it is in house and not on the cloud.  By using the separate userid/password combination in both the desktop and the service you should be ok.

[rassen]

Well, I'm here because that approach did not work. The scheduled refresh just fail when I have credentials in both sources despite each sources work fine alone.

Also in my testing I find that if I try to setup dataflow tables for both sources, once I login one credential the other will log out. 

With a personal gateway, they work fine just as intended but not for the on-premise gateway.