Can't connect to SQL Server named instance

[cmikos]

I can't connect to my SQL Server named instance with Power BI Desktop (October 2017)

I can only connect if I don't specify the instance name but then my datasource gets published without it and can't connect (I think).

When I put the instance name in with or without the port number I get:

Details: "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: 25 - Connection string is not valid)"

[Anonymous]

@cmikos，

Do you connect to the SQL Server using the machinename\MSSQLSERVER or servername\MSSQLSERVER connection string? If that is the case, MSSQLSERVER is for default instance, hence just provide the server name(machine name) alone in the connection string  and don’t use \MSSQLSERVER.

After you publish the PBIX file to Power BI Service, make sure that you enter same server name and database name as that you use in Power BI Desktop when adding SQL data source within gateway, then you are able to use gateway to refresh your dataset.

Regards,
Lydia

[cmikos · Accepted solution]

I figured out the issue, it was UDP port 1434 for the SQL Browser, non-standard, usually TCP 1434 is enough