Gateway connectivity issue

[l2aFa]

Hello everyone

I'm having a bit of trouble trying to get to work the following scenario:

- I have two machines, one is a production server with the gateway installed (let's call it A) and the other one its from where we publish the reports to the service (B).

- B has access to A database (SQL Server) through the A server's IP plus port (for security reasons, A's a restricted access environment). The access is made in Direct Query mode.

- Like I said before, A has an on-premises enterprise gateway installed and running.

- The Power BI service has A's gateway and datasource (SQL Server instance, one database only) configured and connected though its server name property its non the IP plus port combination which B employs neither its the server's real IP, at the moment only works with the SQL Server instance real name.

- Because B's Power BI report gets A's data through the IP plus port combination, whenever I publish it, it doesn't recognize the existing gateway so the report cant show any data online. His configured datasource (although its A's database) isnt viewed as such in Power BI's eyes so the gateway isnt employed.

Any thoughts?

I considered publishing the report from within A's environment in order to make it work but its not a viable route due to security and production restrictions.

Thanks in advance!

[Seth_C_Bauer]

@l2aFa I think this is the key part here:

"- The Power BI service has A's gateway and datasource (SQL Server instance, one database only) configured and connected though its server name property its non the IP plus port combination which B employs neither its the server's real IP, at the moment only works with the SQL Server instance real name."

If the datasource you created on the gateway does not match exactly to what is in the PBIX, then the datasource won't hook up. If you change the datasource in the PBIX to match what you created in the Service and deploy it, it should hook up correctly (in theory). This would break the PBIX on the B server (I believe), but should come alive in the Service.

I recommended this in a different thread, and didn't get feedback on whether or not it worked, but I would assume it would because the connection on premises would clearly no longer work, but if the connection is valid in the Service then I assume it does. Let me know if this works.

[l2aFa]

Hi there Eno

Thanks for your answer.

Unfortunately, I can't match the Power BI service defined datasource from within the development environment (B) cause it cant see the production machine in the way the service does, so like you said, they will never match.

However, I'm gonna try to ask for deploying the PBIX from witihin the production server just once and then I'm gonna replicate it on B connecting through the Power BI Service defined datasource connector instead of referencing A directly. I didn't find any other way.

Thanks again!

[Seth_C_Bauer]

@l2aFa Maybe I didn't explain myself. I know the connection from the DEV machine to PROD won't work based on your description. But if DEV mirrors PROD, you can develop against DEV in the PBIX file. When you are done. You can alter the connection string in the PBIX in DEV to use the PROD connection. This will break the report. Publish to the Power BI Service. I assume, the report begins to work because the connection to PROD is valid. (Is this not the case?)

If you are locked into DEV, this is an extra step, but my assumption is that it would work...

[l2aFa]

@Seth_C_Bauer 

Thanks again for your response but I'm afraid I cannot mirror DEV to look like PROD 😞 

Nevertheless, before coming here for help I tried something like you said, but I couldn't publish the report with PROD's connection string (from DEV) due to connection and validation errors that prompted as soon as PBI Desktop tried to validate the existing schema.

Also, I couldn't find a way to modify the published report connection string inside the service.

Maybe I did something wrong but I couldn't achieve any of these. Could it be cause I'm employing a Direct Query connection?

Thanks!

[Seth_C_Bauer]

@l2aFa Thanks for the feedback. No, I think it sounds like the solution I suggested won't work because of the validation. Thanks for responding with your testing it out. I'm going to ask around on this one, just because I don't like the result 🙂 I'll respond if I can scrounge up any more detail, but it looks like publish from the Prod environment has to be done, or somehow getting the Service connection to connect the same way so the datasources match look like the only options.

[l2aFa · Accepted solution]

@Seth_C_Bauer 

No problem, glad to provide it 🙂

I finally resolved it publishing the report from within the production environment and for future developments and maintenance this datasource will be accesed through the Power BI Service data connector like we discussed.

Thanks for your help!