1

I just installed a named instance of SQL Server 2008 Developer Edition on our Windows 2008 server. I've been working all morning trying to enable remote connections but have been unsuccessful. Here are some details:

  • SQL Server service is running using the network service account
  • SQL Server Browser service is running using the network service account
  • TCP/IP protocol is enabled
  • TCP/IP listens on a specific IP address, port 1433 (I have done a netstat to verify it is listening)
  • Port 1433 is open on to TCP traffic in the firewall
  • Server authentication is set to mixed mode
  • Remote connections are enabled

I am able to connect from SSMS 2008 on the server itself using the following:

ipaddress\instance_name (e.g. 123.45.67.89\SQLSERVER)
SQL Server authentication (e.g. sa, password)

However, I am not able to connect, using the exact same connection properties and login, from a remote client using SSMS 2008. At first I thought it must be a firewall issue, but I tried completely turning the firewall off and still get the same 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."

Anyone have any suggestions?

Thanks!

Improve this question
0

4 Answers 4

Reset to default
1

Open up UDP port 1434, you need this for the SQL Server Browser service.

or

Try connecting in SSMS using the following: 123.45.67.89\SQLSERVER,1433 Yo shouldn't need the browser service if you specify the port.

Improve this answer
3
  • I tried opening up UDP 1434 but still no luck. I tried turning the firewall completely off and still nothing. :(
    – Kevin Babcock
    Oct 23, 2009 at 17:30
  • How about connecting when you specify the port?
    – Nick Kavadias
    Oct 23, 2009 at 22:49
  • I tried specifying the port as you suggested. Still no luck. :(
    – Kevin Babcock
    Oct 24, 2009 at 1:31
1

Start with the basics. Can you ping the SQL server by ip address and hostname from the remote machine?

Run "netstat -a -b -n" from a command prompt on the SQL server to verify what port the SQL instance is listening on.

Improve this answer
4
  • I tried pinging the server by IP address, which was successful. When running the netstat command I see [sqlserver.exe] as the owner.
    – Kevin Babcock
    Oct 24, 2009 at 1:30
  • What port is SQL Server listening on?
    – joeqwerty
    Oct 24, 2009 at 4:10
  • I had the static port set to listen on 1433, but I changed it to another non-standard port.
    – Kevin Babcock
    Oct 24, 2009 at 19:16
  • So from the remote machine, are you trying to connect using the non-standard port you set on the server?
    – joeqwerty
    Oct 24, 2009 at 19:32
1

Can you telnet from the client to port 1433 on the server?

Improve this answer
2
  • No, I can't telnet from the server. When I type 'telnet 123.45.67.89 1433' I get the error 'Could not open connection to the host, on port 1433: Connect failed'
    – Kevin Babcock
    Oct 24, 2009 at 1:28
  • Then something is blocking the connection between your workstation and the server. Either a firewall on one of the machines, or a wirewall on the network between the two machines. Or the SQL Server isn't setup to listen on that port. Check the ERRORLOG file to verify the port number that SQL Server is listening on.
    – mrdenny
    Oct 26, 2009 at 2:32
1

Well, I changed the static port for the named instance to a non-standard port (not 1433). I'm not exactly sure why this fixed it but I am now able to connect. Thanks for the help and troubleshooting tips everyone!

Improve this answer
3
  • wow that's interesting! A named instance cannot listen to port 1433? Will have to test that and see if its documented anywhere
    – Nick Kavadias
    Oct 24, 2009 at 11:41
  • maybe try removing the instance name when connecting, if your connecting to the sql server on 1433?
    – Nick Kavadias
    Oct 24, 2009 at 12:16
  • I'm using a named instance and configured it to listen to port 1433. Works. But it's true that named instances by default listen to other ports, so you have to configure firewall accordingly (if setting up port exception and not program exception).
    – Robert Koritnik
    Feb 13, 2012 at 7:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .