Permissions for Cluster service account in SQL server

Question

Recently i faced an issue where the SQL server 2012 being hosted in a 2 node failover cluster failed to start. And since the cluster and windows event log didn't help much, i started checking the SQL server error log, which had the below error every time i tried to start the SQL server role in failover cluster manager

Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Could not find a login matching the name provided. [CLIENT: ]

so i went ahead created the login NT AUTHORITY\SYSTEM and also added it to the Sysadmin role. and it worked!,

Now i'm skeptical whether what i did is right or not security wise.

1. I've heard that creating NT AUTHORITY\SYSTEM within SQL server is not safe, If that is the case, Should i change my cluster service to a domain account?, Will this affect anything?
2. I added NT AUTHORITY\SYSTEM to Sysadmin role - Is this really required or can i provide only the required permission explicitly to the login

Answer 1

Yes, it failing is expected. It is documented here.

AlwaysOn Availability Groups and SQL Failover Cluster Instance and Privileges

When installing the Database Engine as a AlwaysOn Availability Groups or SQL Failover Cluster Instance (SQL FCI), LOCAL SYSTEM is provisioned in the Database Engine. The LOCAL SYSTEM login is granted the ALTER ANY AVAILABILITY GROUP permission (for AlwaysOn Availability Groups) and the VIEW SERVER STATE permission (for SQL FCI).

To answer now:

1.I've heard that creating NT AUTHORITY\SYSTEM within SQL server is not safe, If that is the case, Should I change my cluster service to a domain account?, Will this affect anything?

I'm assuming you're talking about the actual cluster service. If that's the case, then no do not change it. You could change it in WS2003 when domain accounts were used but since 2008 this is not possible. If you do this most likely your cluster will break.

2.I added NT AUTHORITY\SYSTEM to Sysadmin role - Is this really required or can I provide only the required permission explicitly to the login

Sysadmins is not required. Per the link/quote above, VIEW SERVER STATE is required.

Answer 2

https://support.microsoft.com/en-us/help/2847723/cannot-create-a-high-availability-group-in-microsoft-sql-server-2012

Check SQL security/logins "NT AUTHORITY\SYSTEM" properties. Check "Securables" You should see your server hostname listed. (If not then add it with "Search.." button.) Select hostname and verify checkboxes for only, 1)Grant Alter any avail group, 2)Grant Connect SQL and 3)Grant View Server State. No others should be selected.