Having trouble connecting to SQL Server from a different PC? Read on.
This scenario assumes SQL Server 2008 R2 and Windows Server 2008. I ran into this problem for the umpteenth time the other day while trying to connect to a SQL Instance, so here’s a quick write up on how you might fix it. Now there are all kinds of reasons you might not be able to connect, but if you’re able to do things like
· Remote Desktop into the server that SQL Server is installed on just fine
· Have no problems connecting to the SQL Instance through SSMS when logged into the server that SQL Server is installed on
And if you’re getting an error somewhat similar to the following:
“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: 1326)”
You might want to take a look at your windows firewall settings in Windows Server and make sure that the ports that SQL Server talks over are open (port 1433 by default).
To open up the ports, you could just turn the firewall off all together, depending on your environment, this may or may not be an option….either way, it’s not something I suggest. It’s better to set up an exception in your firewall to allow communication over that port…..and this is how you can do it:
1. Log into the server that SQL Server is installed on and click on “Start” and then type Firewall.cpl in the search/run box. This should bring up the screen shown below. (Alternatively you could go to startàcontrol panel and then “check firewall status” under the “System and Security” section.)
2. Click on “Advanced Settings” (highlighted in yellow above). This should bring up a new screen (shown below). Click on the “Inbound Rules” link highlighted in yellow.
3. Next we’ll want to create a new inbound rule for our firewall by clicking “New Rule…” (highlighted in yellow below)
4. The rest of the steps will be handled by a wizard. There are screen shots of each step. Important areas are highlighted in yellow.
Port below is 1433 (if it's hard to read)
5. Once you’ve hit finish you should see your new rule in the “Inbound Rules” window as shown below.
Depending on how you have your firewall setup, you may also have to configure an outgoing rule as well. To do so just follow the same steps above starting at step 2, only click “Outbound Rules” instead of “Inbound Rules”. Now this is not the end all fix for connection problems....and this assumes a few other things are in place, but it's an excellent starting point, and more often than not this is the problem from my experience. Hope this helps.