Last updated on August 1st, 2022 at 03:09 pm
I’m working on a project to create a SQL Server reporting database; basically it’s a clone of the ConfigMgr database for querying and reporting only. This server will act as a secondary database server where all reporting can be offloaded here and not affect the main SQL Server. By the way, I’m testing different methods of duplicating the database and trying to confirm what is officially supported by Microsoft, but that’s a different story… Like almost every time I setup a new SQL server I receive the Error: 40 issue when I first try to connect to the SQL instance! This article will show you how to solve the Error: 40 problem.
In this particular case, I created a few VMs to replicate the new reporting database. I ran into problems, however, when I tried to use SQL Server Management Studio (SSMS) to connect the new SQL Server database. Below is 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)
The network path was not found
I find this error interesting because at the same time I was using Remote Desktop (RDP) to access the server. Clearly the server does exist on the network!
This error catches me almost every time I setup a new SQL Server. I always forget to open network port 1433. It is network port 1433 which allows me to use SSMS from my desktop.
How to Solve Error: 40
How do you fix the Error: 40 problem? Simply open the network port.
Starting on SQL Server, open Windows Firewall with Advanced Security.
Click on the Inbound Rules node and then click on the New Rule… link.
In the New Inbound Rule Wizard window, on the Rule Type node, select the Port button and click Next.
Continuing on to the Protocol and Ports node, ensure that both TCP and Specific local ports are selected. Enter 1433 in the text box and click Next.
On the Action node, ensure that Allow the connection is selected and then click Next.
In keeping with the idea of least privileges possible, I will only apply the rule to the Domain profile. On the Profile node, make sure to deselect both Private and Public profiles, and then click Next.
On the Name node, enter SQL Server Access (TCP) in the name field and within the description field, enter SQL Server Remote Access (TCP). Once completed click on the Finish button.
When you are finished the new rule will be applied and SSMS will be able to access the server. One of these days I will script the install of SQL Server and I will make sure to open all of the ports that I require as part of that install.
If you have any questions, please feel free to contact me @GarthMJ.