x

How can I connect to SQL server 2008 remotely using SSMS?

OS WS2008 R2 SQL Server 2008 Express Remote client Windows XP - MS SSMS2008

when I try to connect using the IP address of the server I get this error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

the same when I use the IP\InstanceName

enabled on the server is shared memory TCP/IP Named Pipes Allow remote connections

Same when MS firewall is off.

any help will be appreciated thanks

more ▼

asked Jan 26, 2010 at 12:36 PM in Default

Ultradiv gravatar image

Ultradiv
3 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

check the SQL Browser service is running on the server you are connecting to... and all the other suggestions from here: http://ask.sqlservercentral.com/questions/3350/new-server-registration-failednamed-instance

more ▼

answered Jan 26, 2010 at 12:39 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

it is running on the server
Jan 26, 2010 at 12:46 PM Ultradiv
Is this the only SQL instance on the server? There is a setting in SQL Express to limit the number of users, have you altered that?
Jan 26, 2010 at 01:00 PM Fatherjack ♦♦
This is not the only server instance it is the default instance though and no I haven't altered the default number of users - where is that?
Jan 26, 2010 at 01:16 PM Ultradiv
I can connect to the other instance - a named instance with no problems
Jan 26, 2010 at 01:17 PM Ultradiv
I get a TCP/IP error message when I use the 67.***.***.12, 1433 address but the named pipes version of the same error when I don't specify the port
Jan 26, 2010 at 01:27 PM Ultradiv
(comments are locked)
10|1200 characters needed characters left

It sounds like you don't have your client configured correctly. It sounds like you're using TCIP but the client thinks it's using named pipes. If you're using 2008, open the Configuration Manager on your client machine and navigate to the SQL Native Client Configuration, then down to Client Protocols. Disable named pipes and enable TCPIP. Also, if you only have an IP address for the server, in this same area, you can set up an alias so that you can connect to the server by name rather than having to try to remember the IP address.

more ▼

answered Jan 26, 2010 at 12:52 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
100k 19 21 74

I've followed those instructions and I get the very same error - still mentioning named pipes.
Jan 26, 2010 at 01:05 PM Ultradiv
When you went to the client configuration, was tcpip enabled?
Jan 27, 2010 at 01:02 AM Grant Fritchey ♦♦
yes on the client config tcp/ip is enabled
Jan 27, 2010 at 08:12 AM Ultradiv
Can you ping the IP & Port?
Jan 27, 2010 at 12:12 PM Grant Fritchey ♦♦
Ah! I can see the port 8484 open used by the named instance (working fine) but not port 1433. That port is closed. The FW on the server is set to open 1433 so there must be another hurdle somewhere. - Just found out that there is a hardware firewall too that just so happens to have TCP 1433 and UDP 1434 closed by default and TCP 8484 open for some reason! Once I get the ports opened I'll confirm normal operation - thanks
Jan 28, 2010 at 04:44 AM Ultradiv
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x75
x46

asked: Jan 26, 2010 at 12:36 PM

Seen: 7563 times

Last Updated: Jan 26, 2010 at 12:36 PM