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

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