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

avatar image

3 1 1 2

(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

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(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

avatar image

Grant Fritchey ♦♦
137k 20 46 81

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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jan 26, 2010 at 12:36 PM

Seen: 8045 times

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

Copyright 2018 Redgate Software. Privacy Policy