SQL Server exists and access is not denied, Cluster group does not come online

Yesterday evening an engineer from some other team pinged me to inform that they had a high severity incident and sought my assistance to resolve it.  He told me that the Application was not able to connect to the Database Server. However the instance was up and all the databases were accessible within the server hosting SQL Server. With this information I had a remote support session with him, without any knowledge about the environment.

Two SQL Server 2000 instances were running on a Windows 2000 Server Fail-over Cluster. The Application was getting SQL Server Does not exist or Access is denied error message when connecting to this instance. I logged onto the server and checked Cluster Administrator to see that the SQL Server Resource was in failed state! Now I realized that the engineer was under pressure and started gathering the information myself.

Even I failed to bring the SQL Server Resource online. All the dependencies of  SQL Server were online. The System Event Log had only the generic Cluster Resource failure messages. The Cluster.log file (c:\winnt\cluster\cluster.log) had these messages.

000008fc.00001460::2010/11/29-14:50:42.359 SQL Server <SQL Server (GWS1_INSTANCE)>:
[sqsrvres] ODBC sqldriverconnect failed
000008fc.00001460::2010/11/29-14:50:42.359 SQL Server <SQL Server (GWS1_INSTANCE)>:
[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 11;
message = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
000008fc.00001460::2010/11/29-14:50:42.359 SQL Server <SQL Server (GWS1_INSTANCE)>:
[sqsrvres] ODBC sqldriverconnect failed
000008fc.00001460::2010/11/29-14:50:42.359 SQL Server <SQL Server (GWS1_INSTANCE)>:
[sqsrvres] checkODBCConnectError: sqlstate = 01000; native error = 3;
message = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
000008fc.00001460::2010/11/29-14:51:03.281 SQL Server <SQL Server (GWS1_INSTANCE)>:
[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 11;
message = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

Even the Cluster Manager was unable to connect to the SQL Server instance. Since SQL Services were down, I got the Error Log path from the registry.

The SQL Server Error Log did not have any error messages. It looked as if the SQL Server service started and stopped normally. SQL Server was configured to connect using TCP/IP and Named Pipes. To verify it myself I started the SQL Server service from the Services applet (services.msc). As expected the SQL Server service started normally. But I was not able to the instance (ServerName\InstanceName) using Query Analyzer. Same error message. Since I knew the SQL Server Error Log location, I got the port number from it. Then tried connecting to the instance with the port number (ServerName\InstanceName, PortNumber). Voila! I was in!

I wanted to rule out the possibility of Cluster Service service account not having access to SQL Server. I checked the Startup account of Cluster Service. LocalSystem. Eh! LocalSystem account to start Cluster service? I came to know that even though this is on a Cluster, it is not Fail-over Cluster. The other node will always be offline. Alright, I did not have any say on this. I am here to fix it. I saw that NTAUTHORITY\SYSTEM the start up account of Cluster Service had sysadmin privileges on the instance. Hence the permissions issue was ruled out.

I was able to connect to the instance using the port number. It had to be the missing port number information causing this issue. I can add the port number in Query Analyzer to connect, but how to make the SQL Server Resource to use the port number? The only option to force any client applications to use the port number when connecting SQL Server are the Network Utility applications in SQL Server 2000. Since this is on a cluster, I could not use Server Network Utility. The only option that I had was to use SQL Server Client Network Utility. Even the Cluster Service is a client application for SQL Server, like any other application it needs to have the correct SQL Server details in its “connecting string”. Here is how I configured the Cluster Service to make use of the port number while trying to connect to the SQL Server instance.

Clicked on Ok here and went back to Cluster Administrator. The SQL Server Resource came online within seconds!

There may be better ways of fixing this issue, but this one did the trick for me when needed most.

2 thoughts on “SQL Server exists and access is not denied, Cluster group does not come online

  1. Gopu Narasimha Reddy

    Hi Pradeep,
    Hope you are doing well. I’ve been following up your articles in SQLDBADIARIES from many days. It was nice to work you with and your blogs are very useful and informative.
    This is regarding latest article “SQL Server exists and access is not denied, Cluster group does not come online”.
    I would like add one comment here. I think we also fix the issue in the following way.
    Since it is the Named instance which we are working on and running with a port other than Default port, in order to connect to such named instance we need to have a SQL Browser service to be up and running which helps applications in providing some details related to SQL instance like instance name and port number. Since SQL is clustered, we can create a SQL Browser Service in the cluster under SQL Group.Not sure if this could fix the issue.
    Let me know your comments on this please.
    Thanks
    Gopu

    1. PradeepAdiga Post author

      Thanks Gopu for your comments! Configuring the SQL Browser may fix this issue, but I was not lucky in this case since the instance was SQL Server 2000 🙁

Comments are closed.