get data from azure sql - why do i need data gateway???

[WFS]

Hi,

i am trying to get data from Azure SQL. 

for some reason in the PBI service 'Get Data' page it says that this data source is available only in PBI Desktop.

q1: why getting data from Azure SQL is not available in the PBI Service? or am i missing something?

in the PBI desktop, when i try to get data from Azure SQL (direct query mode), all is good but then when i publish it says that 'the published report cant connect to the data source because we were unable to find a gateway..."

q2: why do i need a gateway for connecting to Azure SQL database?

then i download and install the gateway, restart the PBI Desktop, try to publish, and again getting the same message.

(note: PBI Desktop and gateway have been downloaded and installed on April 21st 2019). 

when i go to PBI service i see the report but when i access it it is blank with some error message

q3: what am i missing here???

thanks for the help!

[Greg_Deckler]

Are you connecting live or is this import?

[WFS]

Hi Greg,

thanks for the quick reply. 

i am using DirectQuery for the data conectivity mode. 

[pbiadvisor]

I am seeing the same error.  Just updated the desktop to April 2019 and tried this.

[pbiadvisor · Accepted solution]

So actually, even though the Desktop complains about a gateway, you can go to the dataset in the service and just update the dataset credentials and it will connect and you can view your report.  Refer to this page: https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery

Until credentials are supplied, opening a published report or exploring a dataset created with a DirectQuery connection to such data sources results in an error.

For data sources other than Azure SQL Database, Azure SQL Data Warehouse and Redshift that use DirectQuery, an On-premises data gateway must be installed and the data source must be registered to establish a data connection. You can learn more about On-premises data gateway.

I did this, and it worked for me.

[WFS]

followed your instructions and now it works. 

thanks!!

[pbiadvisor]

As far as why the Azure SQL Database wasnt appearing in the Service:

In order to connect to Azure SQL Database using DirectQuery, you will need to use Power BI Desktop. This approach provides additional flexibility and capabilities. Reports created using Power BI Desktop can then be published to the Power BI service. You can learn more about how to connect to Azure SQL Database using DirectQuery within Power BI Desktop.

Taken from here: https://docs.microsoft.com/en-us/power-bi/service-azure-sql-database-with-direct-connect