Working with SQL Server, greyed out gateway, report authoring

[BlownMilkRun]

I've gone thru the posts on the "greyed out" issue and didn't find what I was looking for and am under time constraints.

Here's what I've got:  A pbix that only uses SQL Server.  No files, no Sharepoint, just SQL Server.

I've authored an SSIS package that loads a reporting database.  Again, in SQL Server.  I have authored one *.pbix file.

I've deployed the *.pbix to the Power BI Service.  I do *not* have access to the SQL Server where the *production* database is hosted directly.  For reasons, I'm not allowed access.  My dev/testing takes place on a local laptop.  

So, I need to change the connection for my report, but since I cannot access the production SQL Server locally in Power BI Desktop...  Apparently, you are hosed if you want to change the connection to use for the pbix but don't have access to the server.  This applies to the Power BI Desktop edit data source settings.

The Power BI Service does not let me alter the data source either.  

From what I can tell, Power BI doesn't let you deploy a SQL Server-based *.pbix and then change the report to point to the production data gateway and I am 100% confident that I am missing something. This gateway is *always* greyed out.  No gateways in fact are listed, but I am an admin for the gateway, can connect to it, can create a report using the Power BI Service as a data source, run connectivity tests, etc.  

Power BI Service:

I am able to see everything working as far as connections to the production on-premise SQL Server database.  How do I know it's working?  I check the status.  Then, I create a test report in Power BI and, using the new Online data feature, I actually use Power BI as the data source and can see all of the data there pop up in my report.  So, with a basic report using the online data, I'm confident that I'm getting the data indirectly from the Power BI Service. 

I'm sure I'm doing something wrong.  All I want to do is change a deployed report to point to a production SQL Server using the on-premises gateway.  I do not have direct access to this box; I *can* connect to it via the Power BI Service using a read-only SQL Server standard account.  

One nuisance is that even though the Power BI Online data source I see in the test report matches what I have as far as dimensions/measures/facts, I cannot copy/paste elements from the one to the other.  I would have to re-do the entire report and then maintain it in sync between the development version and the released version.

Thanks in advance for any help.

[BlownMilkRun · Accepted solution]

According to this article, the database name and server name used in Power BI Desktop have to match with whatever is used by Power BI Service (the online portal).

Ergo, if you author a report, you will have to be able to read the data from the production database that will be used by the data gateway.  If you are working in an environment where physical access to the hosting site for the production database is not available, you'll likely have to do something else like a) extract the data from your production database to delimited files and then put them on Power BI-aware location or b) start using Power BI Service as the data source instead of a SQL Server database. 

"Server and database name have to match between Power BI Desktop and the data source within the on-premises data gateway gateway!"

https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise-manage-sql/