1

I have an Azure SQL Managed instance with public endpoint disabled (we store sensitive data and do not want an exposed endpoint on the DB). We have a VNET and when we need to access our SQL managed instance from an administrator's machine, we use a VPN. How do I connect Power BI to my SQL Managed instance for reporting? I've been digging and am surprised that the only way I can see is to either:

  1. Create a virtual machine and host a data gateway
  2. Expose the public endpoint on the SQL Managed instance.

I may accept 2 if I can limit access to Power BI itself but I'm not convinced that's possible. Any suggestions are welcome!

R2Bleep2
  • 77
  • 1
  • 7
  • Is "Allow Azure Services and resources to access this server" turned on? This is meant to enable internal Azure services (like Power BI to connect), although I get mixed results. https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure#connections-from-inside-azure – Nick.Mc Aug 25 '20 at 11:53
  • Hmm... I must see if that's available for Managed Instances. I know it is for SQL Databases. The trouble with that is that other people's compromised Azure hosted apps and VMs will have access. It's a smaller pool but still not ideal but thanks for the suggestion. – R2Bleep2 Aug 25 '20 at 12:44
  • I missed the managed instance bit. I’m sure you don’t have a say but try to get off it, it’s a dead end. – Nick.Mc Aug 25 '20 at 13:44
  • 1
    We ended up going with #1 on our list. Crazy as it seems, an $80+ a month VM is required to get Power BI to be able to securely access your data in an Azure managed instance. Beyond that the only option is to open the SQL endpoint to the world. Wouldn't it be nice if you could limit access to Power BI only? – R2Bleep2 Aug 27 '20 at 07:34
  • I agree it's kinda crazy that it's the only solution. Does MI have the "open to Azure services" button? Admittedly the doco on this says that it opens it to _every_ subscription which is generally unacceptable. – Nick.Mc Aug 27 '20 at 07:57
  • @Nick McDermaid no, I don't believe it does :/ I think that's only available to Azure SQL Databases. – R2Bleep2 Aug 28 '20 at 11:48
  • @R2Bleep2 how do you get $80+ a month VM for the gateway? what VM size do you use and what OS image? – idazuwaika Apr 18 '21 at 13:05

2 Answers2

1

You can also secure the public endpoint allowing access to powerBI only using Azure Service Tags. Documentation can be found here https://learn.microsoft.com/en-us/power-bi/admin/service-premium-service-tags

Ross
  • 26
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 20 '21 at 12:39
0

I'm confused with some of these supposed solutions since I'm looking at PowerBI.com and it only has 4 options and managed instance is not one of them.... See: Create Dataset - Databases & More option and there's only 4 options for connecting to a database... That's it folks. enter image description here

Sure you can connect from desktop but that won't do you any good once you publish it to powerbi.com unless that site supports connecting to the source.

Yes you can create a gateway which I believe is currently the only solution. Why oh why isn't Managed Instance one of the options for databases when you create a dataset on powerbi.com? That tells me they do not support it, regardless of whether you've enabled public endpoint. As far as I know you still would have to use a gateway to access it if it's not one of these options. Or...something silly like create Azure REST services just to talk to your database.

Gary
  • 69
  • 6