Forum Discussion

Karljr's avatar
Karljr
Frequent Visitor
5 years ago

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. 

 

 

 

Need Help

3 Replies

    • Karljr's avatar
      Karljr
      Frequent Visitor
      5 years ago

       

      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's avatar
        Karljr
        Frequent Visitor
        5 years ago

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