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

Enterprise Gateway Connection to SQL Server Says Connected yet Fails

Created enterprise gateway on my server in cloud that has my SQL server on it. I access SQL SSMS remote as I have remote access turned on that server.  

 

PBI desktop report connects to this data source for the report I created.  I did a Direct query. I wrote my sql script to gather data needed and have SQL  server agent updating a table every 20 minutes.  My Direct Query in PBI is just a Select * of all the data in this table. Like 500 records usually.  Shows status of our business for the day. 

 

Manage gateway on PBI says connection successful for gateway and datasource. I hit test all connections and that comes back connection sucessful. 

 

But I try to pull up my report from the workspace it is published in and it spins forever then says cannot connect to datasource. 

 

 

 

3 REPLIES 3
GilbertQ
Super User
Super User

Hi @Karljr 

 

What happens when you go into the dataset settings, can you confirm that the data source is connected to the data source?

 

Here are more details on how to connect to the data source: Tutorial: Connect to on-premises data in SQL Server - Power BI | Microsoft Docs





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

Proud to be a Super User!







Power BI Blog

Karljr
Frequent Visitor

Karljr_0-1626887597784.png

 

I looked at that yesterday and thought it was good.  But now questioning that it is saying in a strange way they do not work.  Says admin has granted access yet above it acts maybe like not valid.  I use those same creds to sign into SSMS remote into that sql server and database. 

Is that what the yellow highlights are telling me? 

Karljr
Frequent Visitor

Thinking maybe this has to do with desktop PBI data source and PBI gateway treat SQL server instance name differently. 

 

Using same user/pwd accoutn on both yet desktop connect and gateway does not. Apparently. 

 

PBI desktop has data source using public IP of my in cloud SQL server.  SSMS I use the same.  These connect no issue. 

 

Gateway I have IP in there and not connecting  apparently. I go to my cloud server and find instance name of SQL server.  Says XXXXXXXXSERVER. 

 

So now is my dilemma that I have to somehow on my cloud SQL server redirect IP to instance name?  Or is there an easier way to do this on gateway creation like XXXXXSERVER/IP address ?? 

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors