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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Solution Authors
Top Kudoed Authors