- We tried starting it from services.msc and SQL Server service could start successfully.
- When we checked SQL Server ERRORLOG, and found no errors when bringing online in a cluster.
- As a last resort, I generated cluster log and found the below SQL SERVER – Steps to Generate Windows Cluster Log?
- As per above we can see that the cluster service was not to connect to SQL Server
- The error was Login failed for user ‘PRODUCTION\SQLNODE1$’
- This means the account; ‘PRODUCTION\NODE1$’ did not have login permission on SQL Server. This is actually the local machine account (NODE1 was the machine name).
We started SQL Server from services console and gave the sysadmin server role of the LocalSystem (NT AUTHORITY\SYSTEM) account. Here was the command
ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT AUTHORITY\SYSTEM]
After this, we stopped SQL Server from the services console and tried bringing it online from cluster administrator and it succeeded.
As a safely measure, we tested a failover and failback between the SQLNOD1 and SQLNODE2 and it worked perfectly.
Reference: Pinal Dave (http://blog.SQLAuthority.com)