Enterprise Gateway Connection to SQL Server Says Connected yet Fails

[Karljr]

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. 

[GilbertQ]

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

[Karljr]

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]

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 ??