Solved

MS SQL Express 2005 + cant connect remotely

Posted on 2010-11-27
12
390 Views
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.

Error:

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)
0
Comment
Question by:dkilby
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34223014
please check this article to start understanding/troubleshooting this issue:
http://www.experts-exchange.com/A_1881.html
0
 
LVL 5

Expert Comment

by:adamsjs
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:  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 192.168.1.205, and you are using Windows authentication, you would launch SQLCMD as follows from the command prompt:

sqlcmd -E -Stcp:192.168.1.205,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.
0
 

Author Comment

by:dkilby
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.
0
 
LVL 5

Expert Comment

by:adamsjs
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, 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.
0
 
LVL 7

Expert Comment

by:rmm2001
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
0
 

Author Comment

by:dkilby
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
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 5

Expert Comment

by:adamsjs
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?
0
 

Author Comment

by:dkilby
ID: 34235531
turning off Windows Firewall from the host machine, and am running Windows Vista
0
 
LVL 5

Expert Comment

by:adamsjs
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:  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.
0
 

Author Comment

by:dkilby
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?
0
 
LVL 5

Accepted Solution

by:
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 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.
0
 

Author Closing Comment

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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now