Freigeben über

Sql Server Resource Fails to come online on Cluster with Error Error Locating Server/Instance Specified [xFFFFFFFF]

  • Artikel

As mentioned in my previous posts if the sql server fails to come online we need to start troubleshooting by looking at the sql server error log.

So we checked the sql server error log and found that sql server was started successfully and there was no error mentioned in the sql server error log.

We checked the cluster.log located in the C:\Windows\Cluster folder and found the following error

ERR SQL Server <SQL Server (SHHRDBCL2)>: [sqsrvres] ODBC sqldriverconnect failed
ERR SQL Server <SQL Server (SHHRDBCL2)>: [sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = ffffffff; message = [Microsoft][SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
ERR SQL Server <SQL Server (SHHRDBCL2)>: [sqsrvres] ODBC sqldriverconnect failed
ERR SQL Server <SQL Server (SHHRDBCL2)>: [sqsrvres] checkODBCConnectError: sqlstate = HYT00; native error = 0; message = [Microsoft][SQL Native Client]Login timeout expired
ERR SQL Server <SQL Server (SHHRDBCL2)>: [sqsrvres] ODBC sqldriverconnect failed
ERR SQL Server <SQL Server (SHHRDBCL2)>: [sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = ffffffff; message = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

As seen from the above Error message Error Locating Server/Instance Specified [xFFFFFFFF], the cluster service was not able to locate the instance of the sql server instance. We tried to connect to the sql server instance using management studio but it failed with the same error message as above.

CAUSE
=====

So we checked Sql browser service and found that the sql browser service was stopped and was in manual mode.

Sql Browser is the one which is responsible to route all the incoming connections for the sql server to their appropriate TCP port on which the sql server is listening. Sql browser service itself listen on port 1434 on UDP protocol. So whenever a client attempts to connect to the named instance of sql server, it will first try to connect to the Sql browser service which runs on port 1434 and then routes to the appropriate port on which sql server instance is listening.

However the above explanation does not hold true for default instance of the sql server which runs on port 1433 and hence the client does not require sql browser to route it’s connection but instead connects directly to port.

RESOLUTION
=========

So we started the sql browser service and changed it state to Automatic from manual. We observed that sql server came online fine without any error on node 2 without any error.

However some customers do not want the sql browser service to be started for security reasons so in that case for the cluster service to connect to the sql server it should have alias create for the Cluster service to know the port on which the sql is running on.

So we need to create a TCP/IP alias on both nodes of the server with

Alias Name: SHHRDBCL2\SHHRDBCL2
Server: SHHRDBCL2\SHHRDBCL2
TCP Port: <tcp-port>

Once the alias was created the sql server resource were coming online on both nodes of the cluster with sql browser service stopped.

Parikshit Savjani
SE, Microsoft Sql Server

Comments

  • Anonymous
    February 22, 2009
    I've reviewed your case in Clarify last Sunday, and found this article when I came back home...

  • Anonymous
    February 22, 2009
    Hi Kevin, Thanks for your comment on the Blog. Yes we have started this blog to help our Customers solve the sql related issues by themselves.  So when we solve a case we tend to blog the troubleshooting done on the case so that other customers who face the same issue will be able to resolve the issue by themselves. It also helps our internal users  and is a kind of Knowledge sharing. Please do let me know your thoughts on the articles which are posted on this blog. Best regards, Parikshit Savjani

  • Anonymous
    April 13, 2010
    The solution worked perfectly in environment. I did not create any alias, but started SQL browser service once on both the node and bring cluster group online. After that I stopped SQL browser service and tested failover /failback etc, and it worked !!! Thanks a ton for this post

  • Anonymous
    September 11, 2013
    Thanks Parikshit for sharing