Permissions for Cluster service account in SQL server
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 AUTHORITYSYSTEM'. Reason: Could not find a login matching the name provided. [CLIENT: ]
so i went ahead created the login NT AUTHORITYSYSTEM 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.
- I've heard that creating NT AUTHORITYSYSTEM 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 added NT AUTHORITYSYSTEM to Sysadmin role - Is this really required or can i provide only the required permission explicitly to the login
sql-server sql-server-2012 clustering
asked Jul 24 '16 at 22:02
Midhun C NMidhun C N
145112
add a comment |
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 AUTHORITYSYSTEM'. Reason: Could not find a login matching the name provided. [CLIENT: ]
so i went ahead created the login NT AUTHORITYSYSTEM 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.
- I've heard that creating NT AUTHORITYSYSTEM 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 added NT AUTHORITYSYSTEM to Sysadmin role - Is this really required or can i provide only the required permission explicitly to the login
sql-server sql-server-2012 clustering
asked Jul 24 '16 at 22:02
Midhun C NMidhun C N
145112
add a comment |
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 AUTHORITYSYSTEM'. Reason: Could not find a login matching the name provided. [CLIENT: ]
so i went ahead created the login NT AUTHORITYSYSTEM 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.
- I've heard that creating NT AUTHORITYSYSTEM 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 added NT AUTHORITYSYSTEM to Sysadmin role - Is this really required or can i provide only the required permission explicitly to the login
sql-server sql-server-2012 clustering
asked Jul 24 '16 at 22:02
Midhun C NMidhun C N
145112
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 AUTHORITYSYSTEM'. Reason: Could not find a login matching the name provided. [CLIENT: ]
so i went ahead created the login NT AUTHORITYSYSTEM 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.
- I've heard that creating NT AUTHORITYSYSTEM 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 added NT AUTHORITYSYSTEM to Sysadmin role - Is this really required or can i provide only the required permission explicitly to the login
sql-server sql-server-2012 clustering
sql-server sql-server-2012 clustering
asked Jul 24 '16 at 22:02
Midhun C NMidhun C N
145112
asked Jul 24 '16 at 22:02
Midhun C NMidhun C N
145112
asked Jul 24 '16 at 22:02
Midhun C NMidhun C N
145112
asked Jul 24 '16 at 22:02
Midhun C NMidhun C N
145112
asked Jul 24 '16 at 22:02
Midhun C NMidhun C N
145112
145112
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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 AUTHORITYSYSTEM 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 AUTHORITYSYSTEM 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.
answered Jul 25 '16 at 3:11
Sean GallardySean Gallardy
15.7k22548
add a comment |
https://support.microsoft.com/en-us/help/2847723/cannot-create-a-high-availability-group-in-microsoft-sql-server-2012
Check SQL security/logins "NT AUTHORITYSYSTEM" 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.
answered 10 mins ago
guestguest
1
New contributor
guest is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f144777%2fpermissions-for-cluster-service-account-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 AUTHORITYSYSTEM 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 AUTHORITYSYSTEM 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.
answered Jul 25 '16 at 3:11
Sean GallardySean Gallardy
15.7k22548
add a comment |
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 AUTHORITYSYSTEM 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 AUTHORITYSYSTEM 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.
answered Jul 25 '16 at 3:11
Sean GallardySean Gallardy
15.7k22548
add a comment |
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 AUTHORITYSYSTEM 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 AUTHORITYSYSTEM 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.
answered Jul 25 '16 at 3:11
Sean GallardySean Gallardy
15.7k22548
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 AUTHORITYSYSTEM 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 AUTHORITYSYSTEM 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.
answered Jul 25 '16 at 3:11
Sean GallardySean Gallardy
15.7k22548
answered Jul 25 '16 at 3:11
Sean GallardySean Gallardy
15.7k22548
answered Jul 25 '16 at 3:11
Sean GallardySean Gallardy
15.7k22548
answered Jul 25 '16 at 3:11
Sean GallardySean Gallardy
15.7k22548
15.7k22548
add a comment |
add a comment |
https://support.microsoft.com/en-us/help/2847723/cannot-create-a-high-availability-group-in-microsoft-sql-server-2012
Check SQL security/logins "NT AUTHORITYSYSTEM" 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.
answered 10 mins ago
guestguest
1
New contributor
guest is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
https://support.microsoft.com/en-us/help/2847723/cannot-create-a-high-availability-group-in-microsoft-sql-server-2012
Check SQL security/logins "NT AUTHORITYSYSTEM" 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.
answered 10 mins ago
guestguest
1
New contributor
guest is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
https://support.microsoft.com/en-us/help/2847723/cannot-create-a-high-availability-group-in-microsoft-sql-server-2012
Check SQL security/logins "NT AUTHORITYSYSTEM" 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.
answered 10 mins ago
guestguest
1
New contributor
guest is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
https://support.microsoft.com/en-us/help/2847723/cannot-create-a-high-availability-group-in-microsoft-sql-server-2012
Check SQL security/logins "NT AUTHORITYSYSTEM" 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.
answered 10 mins ago
guestguest
1
New contributor
guest is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 10 mins ago
guestguest
1
New contributor
guest is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 10 mins ago
guestguest
1
answered 10 mins ago
guestguest
1
1
New contributor
guest is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
guest is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
guest is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f144777%2fpermissions-for-cluster-service-account-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
