1

I want to host a Microsoft SQL Server database. This can be anywhere: on a dedicated server or on Azure.

I want to access to this sql server database from a power bi report.

This report will be built with power bi desktop. But it will be published on app.powerbi.com

I have tried several things and i always need to install a Microsoft software called "data gateway". Without this software, app.powerbi.com is not able to access to my database.

This is very strange: How can you explain app.powerbi.com is not able to connect to an sql server database directly ?

Is there a way to do that without this data gateway ? I can change sql server by another technology like mysql if needed

Stephan Vierkant
  • 9,674
  • 8
  • 61
  • 97
Bob5421
  • 7,757
  • 14
  • 81
  • 175

1 Answers1

2

The app.powerbi.com is a cloud service on local internet whereas the SQL server database which you are creating is within vnet in onpremises. So for having a connectivity between cloud and onprem ,you need a bridge which is the on premises gateway. In case if you don't want any gateway to be configured, your source needs to be on cloud like Azure SQL database etc. Which is most likely for your case.

Azure SQL Logical server settings :

enter image description here

Nandan
  • 3,939
  • 2
  • 8
  • 21
  • Are you sure azure sql database can work without data gateway ? I have tried to create azure sql instance and it is the same thing than a dedicated server... – Bob5421 Jan 21 '21 at 15:31
  • Yes Power BI can connect to an Azure SQL DB without a Gateway, however you may need to let Azure SQL DB be assessable to other Azure services – Jon Jan 21 '21 at 15:43
  • Hey Bob, You need to create a logical SQL server and then configure an Azure SQL database. You need to enable the property of Allow Azure services at server level so as to allo free cloud to cloud communication without gateway – Nandan Jan 21 '21 at 16:00
  • hey @Bob5421 , was it helpful or you are still facing any issues ? – Nandan Jan 25 '21 at 12:31
  • "In case if you don't want any gateway to be configured, your source needs to be on cloud like Azure SQL database" -> It dit not work with Azure SQL database. Are you sure ? – Bob5421 Jan 25 '21 at 12:41
  • hey @Bob5421, I have edited the answer wherein the Allow services must be enabled at the server level settings and then there is no need of any gateway between app.pwerbi.com and Azure SQL database. Can you please provide screenshots of your network settings of the Azure server and the error msg which you are getting? – Nandan Jan 25 '21 at 12:52
  • I am not sure to create the good type of resource in azure devops portal. Which one should l create exactly for my database ? – Bob5421 Jan 25 '21 at 15:41
  • Hey,What is the amount of data which you would want to maintain in database and how frequently it would be updated and to what extent? Are you familiar with tsql ? These are the factors we consider before deciding on the source. But ideally would suggest using an Azure SQL database – Nandan Jan 25 '21 at 16:34
  • hey @Bob5421, did it help you? – Nandan Feb 02 '21 at 16:32
  • I am familiar to sql server and tsql. My database is not very heavy (less than 500Mo). I have create an Azure SQL Database and the gateway is still necessary... – Bob5421 Feb 02 '21 at 16:54
  • Hey @Bob5421, Can you please go the SQL server in Azure Portal And within the server >>> Security >>> Firewall and networks And provide the screenshot of the status of the AllowAzure service property ? That would help me take it further as Azure SQL database should not have any need of a gateway – Nandan Feb 02 '21 at 17:00
  • this is not a security/firewall/network problem: Everything works because i can connect to this sql instance from home and from other services in azure portal (virtual machines, etc.). Here is what i've done: i have create a report on power bi desktop and i upload the .ipbx file to app.powerbi.com This is at this step in need to configure the gateway... – Bob5421 Feb 02 '21 at 17:20