Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
WFS
Regular 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

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.

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

Are you connecting live or is this import?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi Greg,

thanks for the quick reply. 

 

i am using DirectQuery for the data conectivity mode. 

 

 

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

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.

followed your instructions and now it works. 

thanks!!

 

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors