TIPS: Solve SQL Server Connection Error

ES: Error en Conexión a SQL Server

Surely you agree with me when we say that there isn’t anything more frustrating than the error messages when we cannot connect to our database instance. There are different causes because we have those problems, so now, we will see the origin of this SQL Server connection error.

---

What’s telling the error 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) (Microsoft SQL Server, Error: 53)

(provider: TCP Provider, error: 0 – No such host is known.) (Microsoft SQL Server, Error: 11001)

Depending on the version you are working with, maybe you’ll see some additional text but in general you’ll see something exactly like the message above.

Honestly, the error message is not telling anything specific, right? OK, we cannot establish the connection to SQL Server, but now what?

---

Where do I begin?

Just to be sure you have everything prepared and ready to get connections in your instance, first check you meet this requirements out.

1) Allow remote connections

Go to the Instance Properties and select the Connections tab. There it must be checked the checkbox for allowing the access from outside the server.

2) Service Up and Running correctly

Take a look at Services within SQL Server Configuration Manager. The one that is related with your instance must be Running.

3) Open Ports

If you have enabled the Firewall in your box or in your server (and it should be like that), it’s necessary to open specific ports we need to work with. You can see the process for enabling Firewall Rules in this official link from Microsoft and also you can see the list of SQL Server ports you’ll surely need.

If you did a default installation, in a default instance, you will only need to create a Rule to use port 1433.

4) TCP/IP Protocol enabled

Finally, to have all the connections reaching the server you need to ensure that in the Network configuration there is the TCP/IP protocol Enabled. You can also find this configuration in SQL Server Configuration Manager within Network Configuration.

---

Additional Tips to avoid the SQL Server connection error

First of all, check you meet all the previous requirements shown in this post.

Check if you have additional instances besides default. If you have named instances, you should take a look at SQL Server Browser Service ports.

It is important you have a close relationship with the Network Engineer who can work with you to validate correct routes between client and server networks, or VLANs or just check your net architecture and segmentation.