2

When I try to access a named instance of Microsoft SQL server 2008 R2 SP2 on the local network, I have no problem. But when access it outside office from the internet, I can not connect to the database server.

It send back the "famous" following error:

A network-related or instance-specific error occured while establishing a connection to SQL Server. The server was not found or was not accessible. Verify the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

This error happen in the winforms application and through the SQL Server Management studio.

The server instance name is correct since I can use it on the same local network. I precised that the server is a domain name such as "sql2008.domainname.com\sqldbname". I also make sure to configure SQL Server to allow remote connections from the SSMS > Server Properties > Connections > Remote connections.

More precisions: SQL Browser is up and running. TCP/IP enabled. No problem when installing the database server.

I did a lot of research the last few days and tried everything... I do not have any more ideas.

The system administrator responsible for this server assure me that the ports 1433 and 1434 are open on the database server. I can ping the server from home and when i use putty the command telnet host port I got no error. The command is waiting for me to type something.

Do you have any other ideas that may cause this problem? Port forwarding, etc?

Thank you, LB

LudoB
  • 21
  • 2
  • 1
    How are you connecting to your office network? Please tell me you're using a VPN connection! – Chris McKeown Aug 07 '12 at 12:17
  • Named instances do not always run on port 1433. UDP port 1434 is used for port discovery. Are you certain this instance is running on 1433? – James Sneeringer Aug 07 '12 at 12:22
  • Are you running with Dynamic ports? – KingyCronus Aug 07 '12 at 12:29
  • @Chris McKeown: Actually I am testing it on a production server for the moment. – LudoB Aug 07 '12 at 12:57
  • @JamesSneeringer yes, sqlbrowser.exe run on the port 1434. Should I use this parameter instead of 1433? – LudoB Aug 07 '12 at 12:58
  • @KingyCronus I left Dynamic ports blank and set the port as 1433 since the system admin told me he only opened 1433 and 1434 for testing at the moment. – LudoB Aug 07 '12 at 13:00
  • @LudoB What I'm getting at is whether your SQL server is exposed directly to the Internet with no VPN or rules to restrict access. If I knew your SQL server's IP address, could I connect to port 1433 from my machine? – Chris McKeown Aug 07 '12 at 13:05
  • @LudoB I'm not a SQL Server expert. I just know that the default instance uses 1433 by default, making it unavailable for named instances. Microsoft [KB 823938](http://support.microsoft.com/kb/823938) explains how it all works much better than I can. – James Sneeringer Aug 07 '12 at 13:19

1 Answers1

0

We had some weird network issues after applying SQL 2008 R2 SP2.

There is already a Cumulative Update 1 for it that includes 35 hotfixes.

Cumulative update package 1 for SQL Server 2008 R2 Service Pack 2
http://support.microsoft.com/kb/2720425

You may want perform a packet capture as the amount of data to reproduce the issue is quite low and the issue is easily reproducible. The SQL parsers for NetMon are actually quite good.

Greg Askew
  • 35,880
  • 5
  • 54
  • 82
  • OK, thank you Greg for your input, I will try to apply the update package 1 tomorrow. I downloaded also Microsoft Network Monitor 3.4. Will keep you updated. – LudoB Aug 07 '12 at 14:29
  • I changed nothing and it's working today according to another tester. I do not really know what happen, I sent an email to the system admin to ask some more clarifications. Will keep you updated if I found out what was causing the error. – LudoB Aug 07 '12 at 23:13