Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all
first time poster , so be easy on me
When trying to connect to SQL Server ( all 2008 r2 ) i get the following error messages
This is only a problem on my production servers,,, test servers are fine. ...
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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
So
Im running Power BI as an admin
have the correct username/password
Have checked the SQL server is running and all services are good
Have checked to allow ports through on the servers firewall
Have checked to allow incoming connections in SQL
TCP/IP and Named are enabled on config manager
anything else ?
I had the same problem and found the resolution for me was to set 1433 as the port for connection in the SQL configuration on the server. Once this was set it worked without issue.
I was using PowerBI desktop december 2020 release and SQL Server 2016 running on Microsoft Server 2016.
Hope this helps someone in the future looking for a resolution.
I am exactly the same problem and also on Win2008R2 (no SP1) running SQL2008.
1. I can connect to \\10.1.1.130\c$ from my PC by providing Administrator credential
2. I can remote desktop to 10.1.1.130 using Administrator credential
3. But when get data via PowerBI I failed with exactly the same error message.
My workaround now is to directly installed PowerBI inside the SQL2008 server. This seems to work - but I need to verify further.
Did you try connecting to the SQL Server from other applications e.g. Excel?
Did you set "Allow Remote Connections to this server" property? Right click the server node from management studio--> Properties--> Connections. Check "Allow Remote Connections to this server" property is checked?
Is your production server default/named instance? If named instance check if the SQL Server browser is running and ensure that you add SQLBrowser.exe as an exception in the firewall.
Similar error message when connecting through excel
remote connections is enabled
default instance
sqlbrowser service is stopped
Hi steveelder ,
Do you use IP address or computer name to connect to the remote SQL Server default instance? According to the above error message, there are some proposals for you troubleshooting this issue.
1. Make sure the machine that is running SQL Server is reachable. In other words, you are able to ping the server.
2. Verify that the target SQL Server is listening on 1433 port, if you are connecting to a port other than TCP port 1433, you must also open the UDP port 1434 for the SQL Server Browser service.
3. Use “IP address, port” or “tcp:computername” as server name to connect to SQL Server and check if it is successful.
For more detailed troubleshooting steps, please review the following blog.
http://social.technet.microsoft.com/wiki/contents/articles/2102.how-to-troubleshoot-connecting-to-th...
Thanks,
Lydia Zhang
Query resolved for me. it was a gateway issue. they added by email to it and worked fine.
Im connecting via ip address ( which i can ping etc )
disabled windows firewall, same error
So over the evening , the server was restarted
now Im getting a new error message
Details: "Microsoft SQL: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=13501; handshake=14400; "
Now Ive changed the timeout time ( was 600 ) now 0 ( which = no timeout ) on th e SQL server
and changed my powerbi timeout to 5 minutes
and this is the new error message
Details: "Microsoft SQL: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=13495; handshake=14488; "
Hi steveelder ,
Could you please firstly check that if you can successfully connect to SQL Server through SQL Server Management Studio with connection time-out setup?
Thanks,
Lydia Zhang
Hi Lydia
connecting through SQL management studio works fine
Hi steveelder ,
Is SSMS installed in the same server that is running Power BI Desktop?
Thanks,
Lydia Zhang
Power bi desktop is running from a workstation
SSMS is on the servers, and they can connect to other SQL services ok
Hi steveelder,
In Power BI Desktop, what is the result when specifying “IP address, port” or “tcp:computername” as server name to connect to SQL Server?
In addition, please set the value of "Command timeout in minutes" to a higher value, also write a custom SQL statement in “SQL statement” and check if the connection is established or not.
Thanks,
Lydia Zhang
I met the same error: A network-related or instance-specific error occurred while establishing a connection to SQL Server.... when i refresh the dashboard from powerBI, I could access to the server from SSMS also I have succefully refresh once in powerBI, but most of the other times it keeps on fail and error message like this, one thing i should mention is my data is extramly huge, the refresh would take 40 mins at least.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |