Credentials greyed out - SQL Server Exposed to the Internet

[pey1]

I have seen many posts on this and many solutions - all of which I have tried, including going down a rabbit whole of trying to "silent" install power BI gateway only to find that's not supported anymore!.

This is a common question - surely??

I have a SQL Server hosted on a VM (at substantial SQL server licencing costs) that acts as a data warehouse for my various clients. This SQL server has many Databases - one for each of my clients and very securely set up so that each client cannot see the other client's data.

1) My clients want to use this "publicly internet aware SQL server" within their respective Power BI tenants. 
2) Power BI desktop can access the SQL server fine.
3) After publishing the Semantic Model, and trying to set the credentials for the data source, that option is greyed out.
4) Even when you create a cloud data source that connects to the same server and even though it works, this cant be used in the credentials or gateway cloude settings.
5) No I don't mind setting up a gateway (last resort really) for EACH tenant, but you can't install more than one gateway (even though you could have done this with older gateway installs using silent installer and powershell - the latest version of the gateway installer wont allow you to.

So my question is, How can I get these other tenants to refresh the data from this single "SQL server data Source" that is practically "Cloud".

It seems Microsoft are deliberately trying to get you to use the more expensive SQL AZURE (and I mean its way more expensive when your DW gets to be bigger than 2 terrabytyes and you have 40 odd databases.

[v-priyankata · Accepted solution]

Hi @pey1,

We are grateful for your participation in the Microsoft Fabric Community forum.

Since Power BI Service doesn't let you set credentials directly for a non-cloud SQL Server, it needs a gateway for scheduled refreshes. Here's the best way to set that up:

1.  Install one On-premises Data Gateway per client tenant, each on a separate VM or machine (can be lightweight cloud or client-hosted).
2. Point each gateway to your central SQL Server (just make sure it’s locked down with IP allow-listing and SSL).
3. In each tenant’s Power BI Service, register the gateway, set up the data source, and plug in the right credentials for that client’s database.
4. Use SQL logins or database permissions to make sure clients can only access their own data.
5. ⁠You can’t install more than one gateway on the same machine anymore (Microsoft blocked that), so each tenant needs their own box or VM.

Credential Option Is Greyed Out - Once you publish your dataset, Power BI sees that your SQL Server isn’t a cloud-native source, so it disables the credential box. At that point, only a gateway can handle auth and refresh.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Thanks.

 

[pey1]

Thanks - this seems to be the only solution although its a very poor solution. Can you believe that this is not possible! I am shocked that you cant use MS SQL on a VM with mutlpiple tenant semantic models!

[pey1]

I would further go to say that if Microsoft just "fooled" Power BI Service into thinking it was a cloud based SQL Azure connector and allowed credentials to be entered - this would be a solution too! Or all you to use a Cloud connection from the Manage gateways area to be signed as a Cloud connector. Very poor as its one small thing that stops a refresh - everything else that is expensive to set up is all for naught!

[lbendlin]

I have a SQL Server hosted on a VM 

Can you please provide more details?  Where is the VM located? On-Premise? Azure?  Elsewhere?

[pey1]

It's hosted with servers australia - not sure that helps as even though its publicly accessible without a VPN, Power BI service sees it as an on-premise source needing a gateway.

The simple answer is that you can not create a DW solution with many client databases on a SQL server running on a VM shared across many client tenants without having an expensive solution of multiple VMS running a gateway on each VM—so stupid and not very elegant from Microsoft at all!