• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

MS SQL Express 2005 + cant connect remotely

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)
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please check this article to start understanding/troubleshooting this issue:
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:  http://technet.microsoft.com/en-us/library/cc646023.aspx.

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 http://msdn.microsoft.com/en-us/library/ms188247.aspx 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.
dkilbyAuthor Commented:
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.
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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, http://technet.microsoft.com/en-us/library/cc646023.aspx, 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.
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
dkilbyAuthor Commented:
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
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?
dkilbyAuthor Commented:
turning off Windows Firewall from the host machine, and am running Windows Vista
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:  http://technet.microsoft.com/en-us/library/ms175043.aspx, 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.
dkilbyAuthor Commented:
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?
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 http://technet.microsoft.com/en-us/library/ms175043.aspx, 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:  http://technet.microsoft.com/en-us/library/ms175043%28SQL.90%29.aspx.

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.
dkilbyAuthor Commented:
Thanks for all the help - finally got it to work
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now