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

asked Jan 26 '10 at 12:36 PM in Default

Ultradiv
3 ● 1 ● 1 ● 1

(comments are locked)

2 answers:

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

answered Jan 26 '10 at 12:39 PM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

it is running on the server

Jan 26 '10 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 '10 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 '10 at 01:16 PM Ultradiv

I can connect to the other instance - a named instance with no problems

Jan 26 '10 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 '10 at 01:27 PM Ultradiv

(comments are locked)

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.

answered Jan 26 '10 at 12:52 PM

Grant Fritchey ♦♦
62.2k ● 12 ● 20 ● 66

I've followed those instructions and I get the very same error - still mentioning named pipes.

Jan 26 '10 at 01:05 PM Ultradiv

When you went to the client configuration, was tcpip enabled?

Jan 27 '10 at 01:02 AM Grant Fritchey ♦♦

yes on the client config tcp/ip is enabled

Jan 27 '10 at 08:12 AM Ultradiv

Can you ping the IP & Port?

Jan 27 '10 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 '10 at 04:44 AM Ultradiv

(comments are locked)