cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
WFS Frequent Visitor
Frequent Visitor

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

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..."

Capture.PNG

 

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

Capture2.PNG

 

 

q3: what am i missing here???

 

thanks for the help!

1 ACCEPTED SOLUTION

Accepted Solutions
pbiadvisor Frequent Visitor
Frequent Visitor

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

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.

6 REPLIES 6
Super User
Super User

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

Are you connecting live or is this import?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


WFS Frequent Visitor
Frequent Visitor

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

Hi Greg,

thanks for the quick reply. 

 

i am using DirectQuery for the data conectivity mode. 

 

 

pbiadvisor Frequent Visitor
Frequent Visitor

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

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

pbiadvisor Frequent Visitor
Frequent Visitor

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

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.

pbiadvisor Frequent Visitor
Frequent Visitor

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

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

WFS Frequent Visitor
Frequent Visitor

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

followed your instructions and now it works. 

thanks!!