Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?)

share|improve this question
add comment

4 Answers

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.

share|improve this answer
    
127.0.0.1/localhost is the only entry in the hosts file. :( –  HopelessN00b May 1 '12 at 13:07
add comment

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.

share|improve this answer
    
It was not. I enabled it, rebooted the server and still no joy. –  HopelessN00b May 1 '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 1 '12 at 13:33
    
It's a named instance, so there's nothing listening on 1433, it's using dynamic ports. –  HopelessN00b May 2 '12 at 14:02
add comment

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:

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

NET USE \DBSERVER /USER:DOMAIN\USERNAME

share|improve this answer
    
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
add comment
up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.