MS SQL Express 2005 + cant connect remotely

Posted on 2010-11-27
Last Modified: 2012-05-10
i am trying to connect to my sql server which is sql express 2005, i am using sql 2008, i have changed the setting to allow remote settings, but get the following error, do i need to set my router to port forwarding or is there something else i am missing.


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) (.Net SqlClient Data Provider)
Question by:dkilby
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34223014
please check this article to start understanding/troubleshooting this issue:

Expert Comment

ID: 34223016
There are a number of things that can could contribute to this issue.

The connection failure shows that you are trying to use Named Pipes protocol.  Is this protocol enabled on the SQL Server instance?  IS TCP/IP protocol enabled?  If no protocols besides Shared Memory are enabled, remote connections will not work.  I recommend that TCP/IP be enabled.  Named Pipes can be enabled, but if working on a network where you have solid name resolution from DNS or WINS, you probably won't need Named Pipes (your requirements may vary).

If there is a router between your client computer and the SQL Server host, you will need to ensure that network traffic from the SQL Server is being passed through the router.  Check SQL Server Books Online for information on network and port information (use the Index to look for "network" or "ports").  Also, this article is about configuring Windows Firewall for SQL Server, but has good information on networking considerations for SQL Server:

To troubleshoot, see if you can connect to the SQL Server instance using TCP syntax for SQLCMD (command-line utility with SQL Server).  For example, if you're SQL Server instance is listening on port 1433 at IP address, and you are using Windows authentication, you would launch SQLCMD as follows from the command prompt:

sqlcmd -E -Stcp:,1433
If this is successful, then you know it is not an issue with router or firewall between your client and SQL Server instance, and is likely a resolution issue.  See for information on SQLCMD.

If you are using a name instance of SQL Server instead of a default instance, is the SQL Server Browser service running?  If not, start it and see if that resolves your issue.

Something else that has caused this issue for me in the past is when SQL Server Express is installed on a workstation in our Active Directory domain.  We normally have a group policy applied that prevents normal users from connecting to workstations from the network.  Generally only workstation administrators can do this.  We have to exclude this policy on workstations which will host server-type applications (SQL Server, IIS, etc.) if non-admin users will be connecting to those services/applications.  This is not a workstation-only policy; it can also be set in Windows Server.  In addition to this being set by group policy, it can be set in the SQL Server hosts local security policy.  Check with your group policy admins (if you use group policy) or the local security policy on the SQL Server host to see if you, or a group you're in, is enumerated in the "Deny access to this computer from the network" user rights assignment.

Hope this helps.

Author Comment

ID: 34223793
The problem seems to be with windows firewall and the router, if i turn off the firewall and try connecting while on same network, so putting in the machines ip address, i can connect, if i turn on firewall i cant connect, if i turn off firewall i cant connect remotely from another pc not on same network as server.
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI


Expert Comment

ID: 34223811
Is the Windows Firewall you are turning off located on the SQL Server host, or on the machine from which you are connecting?  Whichever it is, you'll need to configure the appropriate exceptions/rules to allow the connections.  

The article I referenced on configuring Windows Firewall for SQL Server access,, is for configuring Windows Firewall on the SQL Server host.  The information can also be used to configure the similar rules that would be needed if Windows Firewall on the client machine is affecting connectivity.

As for the router configuration, you will need to add the appropriate inbound and outbound rules to permit connections to the SQL Server host.  The information in the previously-mentioned firewall article should help with that, providing explanation of the ports and protocols used by SQL Server.

Expert Comment

ID: 34230895
May be a question you can disregard - but how are you putting in the server name when you try to connect... Server\Instance (ex BOB2\SQL2008 or BOB2Express\Express2005) or InstanceName(Express2005) or localhost? Try Server\Instance and see what you get

Author Comment

ID: 34234834
i have tried adding everything i can to windows firewall, and still cant access, as soon as i turn off window firewall i can access, havent worked on the router side yet as until i can get through router no point.

I added port 1433, plus add SQL as a program

Expert Comment

ID: 34235504
You say that when you disable Windows Firewall, then you can connect to the SQL Server instance.  Are you disabling Windows Firewall on the SQL Server host, or on the machine from which you are connecting (your computer or other client that is not the SQL Server host)?

Also, what operating system is the SQL Server host running?

Author Comment

ID: 34235531
turning off Windows Firewall from the host machine, and am running Windows Vista

Expert Comment

ID: 34235663
Here's what I recommend.

Verify the port that your SQL Server instance is using, and whether it is using a static or dynamic port assignment.  Open SQL Server Configuration Manager, expand the Network Configuration section, click on the "Protcols for <your instance>" (where <your instance> is the name of the SQL Server instance).  Right-click on the TCP/IP protocol and choose Properties from the context menu.  Click on the IP Addresses tab and look at the ports specified for your IP address(es).  You can click on the values for each and get an explanation of the setting at the bottom of the dialog box.

After determining what ports are being used and/or if you are using dynamic ports, take a look at this article:, which is a jump from the Windows Firewall configuration article I referenced previously.  There are steps that detail configuring Windows Firewall for SQL Server data engine access depending on whether you are using a static or dynamic port assignment.  

If you are using a static port assignment, you could configure Windows Firewall using the dynamic port scenario and see if it works.  From there, you could work on reconfiguring for static port assignment if that is what you are using and prefer.

Author Comment

ID: 34235793
when i look at IP Addresses tab, and go to the bottom and look at IP All i see the following:

TCP Dynamic Ports  1133
TCP Port

Should i put 1433 in TCP Port and take out the 1133 and leave it blank?

Accepted Solution

adamsjs earned 500 total points
ID: 34235882
I would leave the dynamic port configuration as is.  You'll then need to configure Windows Firewall accordingly.

First remove any of the Windows Firewall configuration you added for SQL Server up to this point.  Then configure Windows Firewall as described in, using the instructions to configure for dynamic ports.

Actually, I just realized those are for SQL Server 2008.  The SQL Server 2005 version of those instructions are here:

I believe you will be able to figure out what to configure by reviewing those items.  The 2008 instructions are a bit more detailed.  Keep in mind that the default directory structures for SQL Server 2005 are a little different from 2008, so you'll need to adjust accordingly if you are using the 2008 instructions as a reference.

Give this a try and let us know if this works.

Author Closing Comment

ID: 34244702
Thanks for all the help - finally got it to work

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question