1

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.

migrated from serverfault.com Jan 30 '14 at 18:13

This question came from our site for system and network administrators.

  • 1
    Is it a default instance or a named instance? How important is it that you can connect via "localhost" when using the proper name works? – Aaron Bertrand Jan 30 '14 at 16:18
  • Default. Sorry forgot to add that in. And connecting as localhost isn't that important, I bring it up for possible trouble shooting...and the fact that it denotes something is wrong. – Emo Jan 30 '14 at 17:21
  • Are you trying to connect with localhost from the passive node? – Cougar9000 Jan 30 '14 at 18:09
  • Sorry to cross post. Wasn't aware that was an issue. You can remove from Serverfault and leave on dbastackexchange – Emo Jan 30 '14 at 18:33
  • No I'm on the active node. Double checked that. – Emo Jan 30 '14 at 18:35
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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