-> I created a Power BI report on my development server that connected to a development SQL Server instance. I then moved onto a different server which doesn’t have connectivity to development SQL Server instance, when I opened and refreshed the report. I got below error,

Refresh
AlwaysON_Latency
Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

-> Changing SQL Server data source dynamically when opening this Power BI report will help.

-> I performed below on the development server. Click on “Transform Data” in the report,

-> Created a new parameter for SQL Server Instance,

-> Click “OK”. Created another parameter for DatabaseName,

-> Both parameters are created,

-> Save the created parameter by clicking “Close & Apply”.

-> Click on “Transform Data” in the report again,

-> On the right side you will see “Query Settings”,

-> Click “settings” icon just next to Source,

-> You will see below dialog box,

-> Change the Server and database to use “Parameter” and select appropriate parameters as below,

-> Click “OK” and Save the changes by clicking “Close & Apply”.

-> Perform a Save as and save the file as a Power BI Template,

-> Now try opening the saved Power BI Template file and this time it will ask for SQL Server Instance and Database name,

-> From now on this file can be moved to other servers and can be connected to appropriate SQL Server instance and database.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.