SQL Server 2008 server cannot connect to itself with alias name or "localhost"

Question

We have a SQL Server 2008 ENT 64bit SP3 server running Server 2008 R2 Standard SP1 64-bit. This is also a windows/sql clustered environment. There is the cluster alias for the server, and then we have 2 AD DNS aliases as well for the server

Suddenly I can't connect to the sql instance in Management Studio, or with a Powershell Invoke-SQLcmd IF I am doing it from that actual server, when using one of the dns alias names, or "localhost". If I use the Cluster alias name it works and if I use the other dns alias name it works.

Using management studio with the non-working dns alias I get "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."

If I use localhost, I get "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)"

Shared Memory and TCPIP are enabled in SQL Server Config manager but named pipes is not. But we've NEVER had named pipes enabled, and don't have it enabled on any of our other sql servers and they're set up the same (clustering, dns aliases...etc) and are having no issues.

I've Googled around and had a view people suggest listing what other SQL servers it can see (cmd prompt sqlcmd -L) If it do it from a good server I see all other servers on my network including the "bad" one. If I do that command from the "bad" server, I get no results at all.

Pretty much checked everything from this blog and it's all ok except the sqlcmd -L issue I just mentioned above.

http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

Windows firewall is not enabled.

This was working just fine for a couple of years. I'm not sure when this started happening, but I know that at some point, it was not happening.

And the other kicker...it's only happening locally to this server. If I connect with management studio or run a powershell Invoke-Sqlcmd from another machine, or my local machine connecting to the "bad" server, it works fine no matter what alias I use.

I'm stumped. I think it sounds like an issue with the AD alias (there are 2 ad aliases, 1 works locally the other doesn't)...but not being able to connect with localhost makes me question that.

any help is greatly appreciated.

Answer 1

Not sure if this will help or not, but i had the same issue with a clustered environment.

localhost will only refer to the same machine it was run on, if the sql server is located on a different machine, localhost will fail, as the server is not on that machine.

I found this issue when i failed over to the second box in the cluster, and from the first machine i ran the localhost and it failed, i jumped over to the second machine and ran the localhost command and it worked....

i failed the second machine back over to the original machine, and issued the localhost command on the newly failed over to machine, and it would work again, but the localhost would fail on the old machine.

long story short, localhost only applies to the machine it is being run from, and the cluster sub-system does not really recognize the localhost command... best to use the virtual IP address of the cluster/sql server for all access.