On Premise Gateway Will Not Appear

[Anonymous]

Hi there!

I'm having some trouble setting up a PowerBI Gateway Data source to appear for a user. Here are the details:

Gateway is installed on Server1 (Azure). Datasource exists on Server 2 (different Azure server). 

• Server 2 does not allow for windows authentication. To allow connection, we created a SQL Login with permissions over the database (read).
• I've created a data source within the gateway with Server 2 and Database name that match exactly to the pbix file. 
• I've added the user (through a security group and directly) as a user to the datasource within the gateway. 
• When the user goes to the report published in the service, they are unable to select the gateway because it does not appear to be a selectable option. 
• The user can select the gateway in question when referencing server 1 with a database on server 1. 
• User's colleague can select the gateway to refresh data from server 2 and its database. User 1 cannot.
• Colleague is the sysadmin over the server 2.

Steps Attempted: 

• Published the PBIX to the user's personal workspace. 
• Verified spelling is exactly correct for server/database in PBIX settings.
• Republished to the same workspace.
• Verified that the database permissions matched (i.e., one is not organizational while the other is none. They're both none)

Further Steps we're attempting: 

• I'm asking the user to use the database login set rather than windows in the report: with the set we used on the gateway when originally setting up the 
• Seeing if I can publish the report (I'm an admin over the gateway).
  • Note: Colleague is just a user over this gateway.

[Anonymous]

You've mentioned this is an Azure database.  That means its already hosted in the cloud correct?  Is there a reason you are using a gateway for a cloud source?

Azure has IP restrictions, are any of the IPs outside of the White list?

[Anonymous]

That's a fair question. Let me clarify, the sql server is hosted in an Azure instance. The data source itself is a SQL Server/Database. 

Would there be a separate way to connect to a sql server hosted in azure? The method I was using was the SQL Server connection method by specifying the server name and then the database name. 

To the whitelist question, the sysadmin over the server was able to see the gateway and apply it to another report successfully, so I don't think the IP would be at issue.

I'll also note that the successful deployment was done on the sysadmin's personal workspace, but the user's workspace would not proffer access to the gateway (would not show up). 

[Anonymous]

What sort of Server Address does it have? Its still possible it doesn't need a gateway.  Does it end with ".database.windows.net"?

When you said it worked in the sys-admins' personal workspace. Do you mean he was able to successfully connect the gateway to it?

[Anonymous]

Thanks Ross,

It does indeed have a .database.windows.net ending. Given that reality, does the user simply not need to schedule a refresh? What's the cadence?

To the second question: yes, the sysadmin was able to successfully connect his report to the gateway. I tried having the user in question publish to their personal gateway to see if they could connect to the gateway from there, but it was not successful. 

This user (not sysadmin) is able to connect using this gateway to another server. 

[Anonymous]

The purpose of a gateway is to be a "gateway" between the cloud and your internal network.  If your source is already in the cloud a gateway is not required.  This is assuming your data model doesn't include more than 1 source where at least 1 of those sources is an on-premise.

Refreshing is a separate thing, its the method of updating the dataset.  The gateway is an option of how to make that route occur.  Anything inside your network is behind firewalls and internal security.  The gateway sets up how that will all work.  A cloud source already has those set up and gateway is not required.

To simplify things, attempt a refresh without the gateway.  You will need to go into the schedule refresh section and turn off the gateway, then put the required credentials into "Data Source Credentials"

[Anonymous]

So that was a good thought, Ross. 

The user is showing that the credentials section where he would normally enter them is greyed out. 

Is there an alternative datasource he would use in the PBIX file when designing the query? It seems like SQL Server is going to default to needing a gateway and then we're stuck with the error we're running into. 

[Anonymous]

SQL is certainly the correct connector.  I've used it Azure databases and the gateway certainly wasn't required.  It still gives you the option, you can just leave it off and enter the credentials into that area.  If you have selected a gateway, then the credentials will be greyed out because the gateway will be handling it.

It will say above the gateway selection section that a gateway isn't required.

I'd inspect the PBIX file because i wonder if something else is in play here that hasn't been described.

[Anonymous]

To provide maybe a little more clarity, this is what the user sees:

Normally, I would think you would be right - he should be able to provide his own credentials (or another set) down in that section but both the gateway and the data source credentials are greyed out. I'm not sure why this isn't the case here. 

I took a look at the pbix file and it's set to use the user's micrsoft account, and the gateway credential type is set to basic. Would that mismatch be at issue?

[Anonymous]

While you are in the file, have a look at the Data Source Settings and see the full list.  Ensure there is only that single cloud source you are expecting.