Hide Instance set to YES in SQL server Network Configuration

-> I was working on a 2 node cluster server where SQL server was failing to come online.

-> I tried starting the SQL services as a standalone service, that is tried starting the SQL services from services.msc or SQL server configuration manager and it started fine.

-> Started the SQL services from cluster administrator and it started to loop within the group on the physical node 1 and it failed.

-> I failed over the SQL server to physical node 2. All the resources came online.

-> I failed back to physical node 1 and the SQL server resources failed again.

-> Checked the SQL server errorlog and did not find any errors in it. I generated the cluster.log and found the below entry,

00001eb4.00003870::2016/11/31-01:43:04.009 INFO  [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] SvcTerminate: Service is stopped.
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] ODBC sqldriverconnect failed
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = ffffffff; message = [Microsoft][SQL Server Native Client 10.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] ODBC sqldriverconnect failed
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] checkODBCConnectError: sqlstate = HYT00; native error = 0; message = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] ODBC sqldriverconnect failed
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = ffffffff; message = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
00001eb4.00002d14::2016/11/31-01:43:35.116 INFO  [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] ConnectToSQL: asked to terminate while trying to connect to server.
00001eb4.00002d14::2016/11/31-01:43:35.116 ERR   [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] OnlineThread: Error connecting to SQL Server.
00001eb4.00002d14::2016/11/31-01:43:35.116 INFO  [RES] SQL Server <SQL Server (Instance_Name)>: [sqsrvres] OnlineThread: asked to terminate while waiting for QP.
00001eb4.00002d14::2016/11/31-01:43:35.116 WARN  [RHS] returning ResourceExitStateTerminate.
00000654.00003c78::2016/11/31-01:43:35.116 INFO  [RCM] HandleMonitorReply: TERMINATERESOURCE for ‘SQL Server (Instance_Name)’, gen(8) result 0.
00000654.00003c78::2016/11/31-01:43:35.116 INFO  [RCM] TransitionToState(SQL Server (Instance_Name)) [Terminating to Failed]–>Failed.
00000654.00003c78::2016/11/31-01:43:35.116 INFO  [RCM] rcm::RcmGroup::UpdateStateIfChanged: (<NODE1>, Pending –> Failed)

-> I tried starting the SQL service as a standalone service and tried connecting the SQL server using management studio. Could not connect to the SQL server instance.

-> It seems like SQL services starts fine, but cluster services cannot connect to the instance.

-> Opened SQL server configuration manager and checked for any bad aliases. Could not find any aliases created.

-> Opened the SQL server configuration manager. Opened the “Protocols for <Instance_Name>” under “SQL server network configuration”. “Hide Instance” was set to Yes. Please refer https://msdn.microsoft.com/en-us/library/ms179327(v=sql.110).aspx for more info on hide instances.

blog4_1

-> I changed “Hide Instance” to NO. Started the SQL services on cluster administrator and it started fine.

-> I tried failing over and failing back the SQL services between the 2 nodes and everything was online.

Thank You,
Karthik Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Leave a Reply