System.IO.FileLoadException SQL? or Named Pipes Provider, error: 40

[Virtual_Ames]

Hello,

I am trying to update a SQL source to a new DB name - same server.  I am able to connect and see the new DB in SQL Server Management Studio.

However, in PowerBI, I get errors - for example:

DataSource.Error: 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Details:
    DataSourceKind=SQL
    DataSourcePath=azXXXXXXX1;XXXUSRXXX
    Message=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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
    Number=5
    Class=20

Becuase I can see the DB on the same computer in SQL Server Management Studio - using Windows Authenticaion - I would think that PowerBI desktop would be able to see it as well.

Enterprise Gateway connection to the Servier is set up an shows as successful....

Ideas on what is going on here?

Thanks in advance...

[kurtdn]

we got this kind of error today when a user wanted to create a new SQL connection in powerbi desktop while another instance of power bi desktop was open.  We see no other reason why this error came.  When we closed the 2 powerbi desktop instance, the connection was succesfully created without error.  Strange ...

[v-yuezhe-msft]

Hi @Virtual_Ames,

Based on your description, you install SQL Server Management Studio (ssms) and Power BI Desktop on the same computer and you have no issues when using ssms to connect to the new database.

In this scenario, I would recommend you perform the following steps and then connect to the new database in Power BI Desktop.

1. Go to “File –> Options and Settings –> Options –> Data Load”, and delete cache files.

2. Go to “File –> Options and Settings –> Data Source Settings”, under “Global permissions”, select any old SQL Server data source connection and click on clear permission.

3. Close Power BI Desktop, then right click Power BI Desktop and choose “Run as administrator” to start it, also use “<IP address>,< port>” or “tcp:<servername>[\<instancename>],<port>” as server name to connect to SQL Server in Power BI Desktop.

Thanks,
Lydia Zhang

[Seth_C_Bauer]

@Virtual_Ames Is the new database in the same SQL instance on the Server? Most, if not all, the remote access and configuration settings are either at the instance or server level...