Error Connection to SQL Server using [HOSTNAME]\instance or [FQDN]\Instance, [IP]\instance working

Question

This one's kind of weird, at least to me.

I've got MSSQL server 2k5 (STD) installed on Server 2k3 R2 SP2 (on account of some legacy software that doesn't yet support x64 or Server 2008), using Windows Authentication. It's configured to accept local or remote connections, over either TCP/IP (2) or Named Pipes (3). (I've tried disabling Named Pipes, didn't help.)

I can connect to it remotely from any of our SQL servers, and if I pull up the SQL SMS, I can connect if I punch in ".\instance" or [IP]\instance but not if I try [hostname]\instance or even [FQDN]\instance - I get the "No connection could be made because the target machine actively refused it" code 10061 TCP/IP error. Of course, nslookup returns the FQDN and proper ip of the server.

Incidentally, the SQL Server Agent also won't start, because it times out trying to connect to [hostname]\instance, which strikes me as particularly odd, given that the other four services are starting just fine, and their properties (under SQL Server Configuration Manager) say they're connecting to [hostname], and the option to change it is all greyed out. Only other thing I can think of to add is that the services are set up to start under a domain SA account, not that it should make a difference.

Any thoughts? (On either what's causing it, or how to correct it?)

score 2 · Answer 1 · answered Apr 30 '12 at 20:44

2

In your SQL Server Configuration Manager, check the Network Configuration TCP/IP settings - on the IP addresses tab, is the IP address that your FQDN resolves to actually enabled?

If you connect to the local machine from SSMS, I think it'll use Shared Memory above all other protocols.

answered Apr 30 '12 at 20:44

Chris McKeown

7,188

It was not. I enabled it, rebooted the server and still no joy. – HopelessN00b May 01 '12 at 13:07
Check netstat -a to make sure that something is listening on port 1433 on the IP you're trying to connect to., – Chris McKeown May 01 '12 at 13:33
It's a named instance, so there's nothing listening on 1433, it's using dynamic ports. – HopelessN00b May 02 '12 at 14:02

score 1 · Answer 2 · answered Apr 30 '12 at 20:24

1

Check your hosts file. You may have an errant entry in there. nslookup intentionally looks at a DNS server but does not consult the local hosts file first.

answered Apr 30 '12 at 20:24

Wesley

32,840

127.0.0.1/localhost is the only entry in the hosts file. :( – HopelessN00b May 01 '12 at 13:07

score 1 · Answer 3 · edited May 23 '17 at 12:41

1

Have you explored NETBIOS failure as a possible cause?

Maybe try establishing a NETBIOS Session with the server first and see if you can connect like in the below post:

https://stackoverflow.com/questions/5237654/sql-server-management-studio-2008-runas-user-on-different-domain-over-vpn

NET USE \DBSERVER /USER:DOMAIN\USERNAME

edited May 23 '17 at 12:41

Community

1

answered Jun 29 '12 at 21:58

unhappyCrackers1

977

Thanks for the follow up, but I did, and that wasn't it. After a couple days of screwing around with it, I decided it would be a much better investment of my time to rebuild the server and make sure it got set up right from the start, without any weird unexplained errors haunting the build. – HopelessN00b Jun 29 '12 at 22:07

score 0 · Accepted Answer · answered Jul 15 '12 at 14:32

0

After trying the suggestions here and not being able to determine a root cause, or a fix, I rebuilt the server, and the problem went away. Now the MS SQL instance is acting as expected.

The joys of being a Windows admin, I guess.

answered Jul 15 '12 at 14:32

HopelessN00b

53,954

Error Connection to SQL Server using [HOSTNAME]\instance or [FQDN]\Instance, [IP]\instance working

4 Answers4

Linked