Should I run SQL Server services on multiple servers under different AD accounts? The 2019...
Is bread bad for ducks?
Is three citations per paragraph excessive for undergraduate research paper?
The difference between dialogue marks
Is domain driven design an anti-SQL pattern?
Why Did Howard Stark Use All The Vibranium They Had On A Prototype Shield?
Why don't Unix/Linux systems traverse through directories until they find the required version of a linked library?
How to reverse every other sublist of a list?
Time travel alters history but people keep saying nothing's changed
How to manage monthly salary
What could be the right powersource for 15 seconds lifespan disposable giant chainsaw?
Is flight data recorder erased after every flight?
Monty Hall variation
"Riffle" two strings
Where to refill my bottle in India?
Unbreakable Formation vs. Cry of the Carnarium
On the insanity of kings as an argument against monarchy
Are there any other methods to apply to solving simultaneous equations?
Is this food a bread or a loaf?
Can't find the latex code for the ⍎ (down tack jot) symbol
Deadlock Graph and Interpretation, solution to avoid
Inversion Puzzle
What is the use of option -o in the useradd command?
How to create dashed lines/arrows in Illustrator
How long do I have to send payment?
Should I run SQL Server services on multiple servers under different AD accounts?
The 2019 Stack Overflow Developer Survey Results Are InKerberos authentication not working with Linked Servers in SQL Server 2012What are the risks (if any) of sharing a service account on more than one server?Linked Server Security under SQL Agent Job ContextCan I grant multiple remote LocalService accounts access to a SQL Server database?Managing the Service accounts in large environmentSQL Server Service Account Configuration - one account per Server?“Login failed for user…” with cross-instance access on same server in SSIS packageWhat are points to consider when replacing the SQL Server service account?SQL Server service is running under an account that is a member of the built-in Administrators groupDoes changing “sa” password require a SQL restart (in mixed mode)?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I am looking at changing all of our SQL Server instances to use AD accounts for the running services. I plan to use a different AD account for each service type, e.g. DB, SSIS, SSRS, etc. My question is whether I should create an account for each type and use those accounts across all the instances in the environment or create accounts for each service per server.
For example, if I have 2 servers A and B both running a database instance and SSIS:
Scenario 1 (single AD account across domain)
- SQLDB (database service)
- SQLSSIS (SSIS service)
or
Scenario 2 (multiple AD accounts)
Server A accounts
- SVRA_DB (database service on server A)
- SVRA_SSIS (SSIS service on server A)
- SVRB_DB (database service on server B)
- SVRB_SSIS (SSIS service on server B)
I know scenario 2 won't work for failover clusters and does pose a potential headache in terms of maintenance if the AD accounts are set to expire. Just wondering if there are any other gotchas to either approach and what best practices might be?
As an idea of our environment, we have about ~75 servers running various versions of SQL Server from 2005 up to 2014.
Thanks in advance for any help or insight!
Cheers,
Chris
sql-server security
asked Mar 6 '17 at 16:18
Chris Di CarloChris Di Carlo
1
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I am looking at changing all of our SQL Server instances to use AD accounts for the running services. I plan to use a different AD account for each service type, e.g. DB, SSIS, SSRS, etc. My question is whether I should create an account for each type and use those accounts across all the instances in the environment or create accounts for each service per server.
For example, if I have 2 servers A and B both running a database instance and SSIS:
Scenario 1 (single AD account across domain)
- SQLDB (database service)
- SQLSSIS (SSIS service)
or
Scenario 2 (multiple AD accounts)
Server A accounts
- SVRA_DB (database service on server A)
- SVRA_SSIS (SSIS service on server A)
- SVRB_DB (database service on server B)
- SVRB_SSIS (SSIS service on server B)
I know scenario 2 won't work for failover clusters and does pose a potential headache in terms of maintenance if the AD accounts are set to expire. Just wondering if there are any other gotchas to either approach and what best practices might be?
As an idea of our environment, we have about ~75 servers running various versions of SQL Server from 2005 up to 2014.
Thanks in advance for any help or insight!
Cheers,
Chris
sql-server security
asked Mar 6 '17 at 16:18
Chris Di CarloChris Di Carlo
1
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Service accounts are not typically set to expire. I would have a different service account for each server with the exception that clustered machines may share the same service account. You can also look into using Managed Service Accounts which are non-interactive and can be fully managed from the AD Servers, further reducing the surface area.
– Jonathan Fite
Mar 6 '17 at 16:27
We don't currently have AD accounts used to run services set to expire but who knows if our security team will force the issue. I'll have to look at Managed Service Accounts. Are there any downsides to using them?
– Chris Di Carlo
Mar 6 '17 at 16:30
It's new (well, from Server 2008R2 and upwards) and not familiar. You can't log in as them so you can't troubleshoot issues that way. But they are the "way forward". Let's the security team manage them without needing to involve you or update the password on 75+ servers.
– Jonathan Fite
Mar 6 '17 at 16:41
add a comment |
I am looking at changing all of our SQL Server instances to use AD accounts for the running services. I plan to use a different AD account for each service type, e.g. DB, SSIS, SSRS, etc. My question is whether I should create an account for each type and use those accounts across all the instances in the environment or create accounts for each service per server.
For example, if I have 2 servers A and B both running a database instance and SSIS:
Scenario 1 (single AD account across domain)
- SQLDB (database service)
- SQLSSIS (SSIS service)
or
Scenario 2 (multiple AD accounts)
Server A accounts
- SVRA_DB (database service on server A)
- SVRA_SSIS (SSIS service on server A)
- SVRB_DB (database service on server B)
- SVRB_SSIS (SSIS service on server B)
I know scenario 2 won't work for failover clusters and does pose a potential headache in terms of maintenance if the AD accounts are set to expire. Just wondering if there are any other gotchas to either approach and what best practices might be?
As an idea of our environment, we have about ~75 servers running various versions of SQL Server from 2005 up to 2014.
Thanks in advance for any help or insight!
Cheers,
Chris
sql-server security
asked Mar 6 '17 at 16:18
Chris Di CarloChris Di Carlo
1
I am looking at changing all of our SQL Server instances to use AD accounts for the running services. I plan to use a different AD account for each service type, e.g. DB, SSIS, SSRS, etc. My question is whether I should create an account for each type and use those accounts across all the instances in the environment or create accounts for each service per server.
For example, if I have 2 servers A and B both running a database instance and SSIS:
Scenario 1 (single AD account across domain)
- SQLDB (database service)
- SQLSSIS (SSIS service)
or
Scenario 2 (multiple AD accounts)
Server A accounts
- SVRA_DB (database service on server A)
- SVRA_SSIS (SSIS service on server A)
- SVRB_DB (database service on server B)
- SVRB_SSIS (SSIS service on server B)
I know scenario 2 won't work for failover clusters and does pose a potential headache in terms of maintenance if the AD accounts are set to expire. Just wondering if there are any other gotchas to either approach and what best practices might be?
As an idea of our environment, we have about ~75 servers running various versions of SQL Server from 2005 up to 2014.
Thanks in advance for any help or insight!
Cheers,
Chris
sql-server security
sql-server security
asked Mar 6 '17 at 16:18
Chris Di CarloChris Di Carlo
1
asked Mar 6 '17 at 16:18
Chris Di CarloChris Di Carlo
1
asked Mar 6 '17 at 16:18
Chris Di CarloChris Di Carlo
1
asked Mar 6 '17 at 16:18
Chris Di CarloChris Di Carlo
1
asked Mar 6 '17 at 16:18
Chris Di CarloChris Di Carlo
1
1
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Service accounts are not typically set to expire. I would have a different service account for each server with the exception that clustered machines may share the same service account. You can also look into using Managed Service Accounts which are non-interactive and can be fully managed from the AD Servers, further reducing the surface area.
– Jonathan Fite
Mar 6 '17 at 16:27
We don't currently have AD accounts used to run services set to expire but who knows if our security team will force the issue. I'll have to look at Managed Service Accounts. Are there any downsides to using them?
– Chris Di Carlo
Mar 6 '17 at 16:30
It's new (well, from Server 2008R2 and upwards) and not familiar. You can't log in as them so you can't troubleshoot issues that way. But they are the "way forward". Let's the security team manage them without needing to involve you or update the password on 75+ servers.
– Jonathan Fite
Mar 6 '17 at 16:41
add a comment |
Service accounts are not typically set to expire. I would have a different service account for each server with the exception that clustered machines may share the same service account. You can also look into using Managed Service Accounts which are non-interactive and can be fully managed from the AD Servers, further reducing the surface area.
– Jonathan Fite
Mar 6 '17 at 16:27
We don't currently have AD accounts used to run services set to expire but who knows if our security team will force the issue. I'll have to look at Managed Service Accounts. Are there any downsides to using them?
– Chris Di Carlo
Mar 6 '17 at 16:30
It's new (well, from Server 2008R2 and upwards) and not familiar. You can't log in as them so you can't troubleshoot issues that way. But they are the "way forward". Let's the security team manage them without needing to involve you or update the password on 75+ servers.
– Jonathan Fite
Mar 6 '17 at 16:41
Service accounts are not typically set to expire. I would have a different service account for each server with the exception that clustered machines may share the same service account. You can also look into using Managed Service Accounts which are non-interactive and can be fully managed from the AD Servers, further reducing the surface area.
– Jonathan Fite
Mar 6 '17 at 16:27
Service accounts are not typically set to expire. I would have a different service account for each server with the exception that clustered machines may share the same service account. You can also look into using Managed Service Accounts which are non-interactive and can be fully managed from the AD Servers, further reducing the surface area.
– Jonathan Fite
Mar 6 '17 at 16:27
We don't currently have AD accounts used to run services set to expire but who knows if our security team will force the issue. I'll have to look at Managed Service Accounts. Are there any downsides to using them?
– Chris Di Carlo
Mar 6 '17 at 16:30
We don't currently have AD accounts used to run services set to expire but who knows if our security team will force the issue. I'll have to look at Managed Service Accounts. Are there any downsides to using them?
– Chris Di Carlo
Mar 6 '17 at 16:30
It's new (well, from Server 2008R2 and upwards) and not familiar. You can't log in as them so you can't troubleshoot issues that way. But they are the "way forward". Let's the security team manage them without needing to involve you or update the password on 75+ servers.
– Jonathan Fite
Mar 6 '17 at 16:41
It's new (well, from Server 2008R2 and upwards) and not familiar. You can't log in as them so you can't troubleshoot issues that way. But they are the "way forward". Let's the security team manage them without needing to involve you or update the password on 75+ servers.
– Jonathan Fite
Mar 6 '17 at 16:41
add a comment |
1 Answer
1
active
oldest
votes
Security answer: Absolutely every service on any service should have a dedicated account. This is noted in most best practice documents from Microsoft in the sense they are referring to one server. You give yourself less chance that if one service account is comprised then they only have access to that one server.
Overall answer: Base it on what amount of time you can dedicate to managing. In general if you have 75+ servers I would say limit the service account to each server for only certain services. As well, if you have cluster or AGs configured these will always need to be the same service account for SQL Server service (it is required for FCIs as well).
The less likely need is SSIS. This service does not execute packages for you. It is only there to manage Package Store in msdb or if you are using the file system (specific folder in SSIS configuration that is local to the server). Any package execution authentication required is either via a connection manager, or the caller that is executing the package...not the service.
SSRS is based on your reporting needs. If your SSRS subscriptions need to write out to a network share or other domain resource. If you do not require this then there is no real need.
In regards to password expiration. If that is required for security policy, definitely look into automation or reporting. This will help with the headache to know which account is expiring when so you can plan accordingly.
Some may tell you that Managed Service Accounts are an option, but...SQL Server cannot use MSA.
answered Mar 6 '17 at 16:34
Shawn MeltonShawn Melton
14.5k43882
Note: This only works to reduce access by a compromised account if that account is really only granted elevated privileges to one server. If you wind up granting three accounts sysadmin rights on three servers because a number of jobs interact with all three servers, then you would have been better server by using one account for those three servers. To put it another way - give each account the minimum access it needs, and then use the minimum number of accounts possible.
– RDFozz
Mar 6 '17 at 16:44
@Shawn It looks like MSAs are supported for 2012 and up link? - in a mixed environment including some pre-2012 instances, would you use MSAs for new servers and domain accounts for older servers? Or just bite the bullet and use domain accounts for all.
– Chris Di Carlo
Mar 6 '17 at 16:51
SQL Server can use MSAs and GMSAs, the link provided is from 2011. SQL server 2014+ can use both (if 2014 is updated with the latest patches and running on WS2012R2 + patches).
– Sean Gallardy
Mar 6 '17 at 16:52
@RDFozz - I see your point. How would you manage all the different inter-relations of the accounts and where they are used? Tie it into the inventory management?
– Chris Di Carlo
Mar 6 '17 at 16:53
That's an option. You would probably want to document the accounts somewhere, in case naming conventions change or conflict somehow.
– RDFozz
Mar 6 '17 at 17:01
|
show 1 more 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%2f166354%2fshould-i-run-sql-server-services-on-multiple-servers-under-different-ad-accounts%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Security answer: Absolutely every service on any service should have a dedicated account. This is noted in most best practice documents from Microsoft in the sense they are referring to one server. You give yourself less chance that if one service account is comprised then they only have access to that one server.
Overall answer: Base it on what amount of time you can dedicate to managing. In general if you have 75+ servers I would say limit the service account to each server for only certain services. As well, if you have cluster or AGs configured these will always need to be the same service account for SQL Server service (it is required for FCIs as well).
The less likely need is SSIS. This service does not execute packages for you. It is only there to manage Package Store in msdb or if you are using the file system (specific folder in SSIS configuration that is local to the server). Any package execution authentication required is either via a connection manager, or the caller that is executing the package...not the service.
SSRS is based on your reporting needs. If your SSRS subscriptions need to write out to a network share or other domain resource. If you do not require this then there is no real need.
In regards to password expiration. If that is required for security policy, definitely look into automation or reporting. This will help with the headache to know which account is expiring when so you can plan accordingly.
Some may tell you that Managed Service Accounts are an option, but...SQL Server cannot use MSA.
answered Mar 6 '17 at 16:34
Shawn MeltonShawn Melton
14.5k43882
Note: This only works to reduce access by a compromised account if that account is really only granted elevated privileges to one server. If you wind up granting three accounts sysadmin rights on three servers because a number of jobs interact with all three servers, then you would have been better server by using one account for those three servers. To put it another way - give each account the minimum access it needs, and then use the minimum number of accounts possible.
– RDFozz
Mar 6 '17 at 16:44
@Shawn It looks like MSAs are supported for 2012 and up link? - in a mixed environment including some pre-2012 instances, would you use MSAs for new servers and domain accounts for older servers? Or just bite the bullet and use domain accounts for all.
– Chris Di Carlo
Mar 6 '17 at 16:51
SQL Server can use MSAs and GMSAs, the link provided is from 2011. SQL server 2014+ can use both (if 2014 is updated with the latest patches and running on WS2012R2 + patches).
– Sean Gallardy
Mar 6 '17 at 16:52
@RDFozz - I see your point. How would you manage all the different inter-relations of the accounts and where they are used? Tie it into the inventory management?
– Chris Di Carlo
Mar 6 '17 at 16:53
That's an option. You would probably want to document the accounts somewhere, in case naming conventions change or conflict somehow.
– RDFozz
Mar 6 '17 at 17:01
|
show 1 more comment
Security answer: Absolutely every service on any service should have a dedicated account. This is noted in most best practice documents from Microsoft in the sense they are referring to one server. You give yourself less chance that if one service account is comprised then they only have access to that one server.
Overall answer: Base it on what amount of time you can dedicate to managing. In general if you have 75+ servers I would say limit the service account to each server for only certain services. As well, if you have cluster or AGs configured these will always need to be the same service account for SQL Server service (it is required for FCIs as well).
The less likely need is SSIS. This service does not execute packages for you. It is only there to manage Package Store in msdb or if you are using the file system (specific folder in SSIS configuration that is local to the server). Any package execution authentication required is either via a connection manager, or the caller that is executing the package...not the service.
SSRS is based on your reporting needs. If your SSRS subscriptions need to write out to a network share or other domain resource. If you do not require this then there is no real need.
In regards to password expiration. If that is required for security policy, definitely look into automation or reporting. This will help with the headache to know which account is expiring when so you can plan accordingly.
Some may tell you that Managed Service Accounts are an option, but...SQL Server cannot use MSA.
answered Mar 6 '17 at 16:34
Shawn MeltonShawn Melton
14.5k43882
Note: This only works to reduce access by a compromised account if that account is really only granted elevated privileges to one server. If you wind up granting three accounts sysadmin rights on three servers because a number of jobs interact with all three servers, then you would have been better server by using one account for those three servers. To put it another way - give each account the minimum access it needs, and then use the minimum number of accounts possible.
– RDFozz
Mar 6 '17 at 16:44
@Shawn It looks like MSAs are supported for 2012 and up link? - in a mixed environment including some pre-2012 instances, would you use MSAs for new servers and domain accounts for older servers? Or just bite the bullet and use domain accounts for all.
– Chris Di Carlo
Mar 6 '17 at 16:51
SQL Server can use MSAs and GMSAs, the link provided is from 2011. SQL server 2014+ can use both (if 2014 is updated with the latest patches and running on WS2012R2 + patches).
– Sean Gallardy
Mar 6 '17 at 16:52
@RDFozz - I see your point. How would you manage all the different inter-relations of the accounts and where they are used? Tie it into the inventory management?
– Chris Di Carlo
Mar 6 '17 at 16:53
That's an option. You would probably want to document the accounts somewhere, in case naming conventions change or conflict somehow.
– RDFozz
Mar 6 '17 at 17:01
|
show 1 more comment
Security answer: Absolutely every service on any service should have a dedicated account. This is noted in most best practice documents from Microsoft in the sense they are referring to one server. You give yourself less chance that if one service account is comprised then they only have access to that one server.
Overall answer: Base it on what amount of time you can dedicate to managing. In general if you have 75+ servers I would say limit the service account to each server for only certain services. As well, if you have cluster or AGs configured these will always need to be the same service account for SQL Server service (it is required for FCIs as well).
The less likely need is SSIS. This service does not execute packages for you. It is only there to manage Package Store in msdb or if you are using the file system (specific folder in SSIS configuration that is local to the server). Any package execution authentication required is either via a connection manager, or the caller that is executing the package...not the service.
SSRS is based on your reporting needs. If your SSRS subscriptions need to write out to a network share or other domain resource. If you do not require this then there is no real need.
In regards to password expiration. If that is required for security policy, definitely look into automation or reporting. This will help with the headache to know which account is expiring when so you can plan accordingly.
Some may tell you that Managed Service Accounts are an option, but...SQL Server cannot use MSA.
answered Mar 6 '17 at 16:34
Shawn MeltonShawn Melton
14.5k43882
Security answer: Absolutely every service on any service should have a dedicated account. This is noted in most best practice documents from Microsoft in the sense they are referring to one server. You give yourself less chance that if one service account is comprised then they only have access to that one server.
Overall answer: Base it on what amount of time you can dedicate to managing. In general if you have 75+ servers I would say limit the service account to each server for only certain services. As well, if you have cluster or AGs configured these will always need to be the same service account for SQL Server service (it is required for FCIs as well).
The less likely need is SSIS. This service does not execute packages for you. It is only there to manage Package Store in msdb or if you are using the file system (specific folder in SSIS configuration that is local to the server). Any package execution authentication required is either via a connection manager, or the caller that is executing the package...not the service.
SSRS is based on your reporting needs. If your SSRS subscriptions need to write out to a network share or other domain resource. If you do not require this then there is no real need.
In regards to password expiration. If that is required for security policy, definitely look into automation or reporting. This will help with the headache to know which account is expiring when so you can plan accordingly.
Some may tell you that Managed Service Accounts are an option, but...SQL Server cannot use MSA.
answered Mar 6 '17 at 16:34
Shawn MeltonShawn Melton
14.5k43882
answered Mar 6 '17 at 16:34
Shawn MeltonShawn Melton
14.5k43882
answered Mar 6 '17 at 16:34
Shawn MeltonShawn Melton
14.5k43882
answered Mar 6 '17 at 16:34
Shawn MeltonShawn Melton
14.5k43882
14.5k43882
Note: This only works to reduce access by a compromised account if that account is really only granted elevated privileges to one server. If you wind up granting three accounts sysadmin rights on three servers because a number of jobs interact with all three servers, then you would have been better server by using one account for those three servers. To put it another way - give each account the minimum access it needs, and then use the minimum number of accounts possible.
– RDFozz
Mar 6 '17 at 16:44
@Shawn It looks like MSAs are supported for 2012 and up link? - in a mixed environment including some pre-2012 instances, would you use MSAs for new servers and domain accounts for older servers? Or just bite the bullet and use domain accounts for all.
– Chris Di Carlo
Mar 6 '17 at 16:51
SQL Server can use MSAs and GMSAs, the link provided is from 2011. SQL server 2014+ can use both (if 2014 is updated with the latest patches and running on WS2012R2 + patches).
– Sean Gallardy
Mar 6 '17 at 16:52
@RDFozz - I see your point. How would you manage all the different inter-relations of the accounts and where they are used? Tie it into the inventory management?
– Chris Di Carlo
Mar 6 '17 at 16:53
That's an option. You would probably want to document the accounts somewhere, in case naming conventions change or conflict somehow.
– RDFozz
Mar 6 '17 at 17:01
|
show 1 more comment
Note: This only works to reduce access by a compromised account if that account is really only granted elevated privileges to one server. If you wind up granting three accounts sysadmin rights on three servers because a number of jobs interact with all three servers, then you would have been better server by using one account for those three servers. To put it another way - give each account the minimum access it needs, and then use the minimum number of accounts possible.
– RDFozz
Mar 6 '17 at 16:44
@Shawn It looks like MSAs are supported for 2012 and up link? - in a mixed environment including some pre-2012 instances, would you use MSAs for new servers and domain accounts for older servers? Or just bite the bullet and use domain accounts for all.
– Chris Di Carlo
Mar 6 '17 at 16:51
SQL Server can use MSAs and GMSAs, the link provided is from 2011. SQL server 2014+ can use both (if 2014 is updated with the latest patches and running on WS2012R2 + patches).
– Sean Gallardy
Mar 6 '17 at 16:52
@RDFozz - I see your point. How would you manage all the different inter-relations of the accounts and where they are used? Tie it into the inventory management?
– Chris Di Carlo
Mar 6 '17 at 16:53
That's an option. You would probably want to document the accounts somewhere, in case naming conventions change or conflict somehow.
– RDFozz
Mar 6 '17 at 17:01
Note: This only works to reduce access by a compromised account if that account is really only granted elevated privileges to one server. If you wind up granting three accounts sysadmin rights on three servers because a number of jobs interact with all three servers, then you would have been better server by using one account for those three servers. To put it another way - give each account the minimum access it needs, and then use the minimum number of accounts possible.
– RDFozz
Mar 6 '17 at 16:44
Note: This only works to reduce access by a compromised account if that account is really only granted elevated privileges to one server. If you wind up granting three accounts sysadmin rights on three servers because a number of jobs interact with all three servers, then you would have been better server by using one account for those three servers. To put it another way - give each account the minimum access it needs, and then use the minimum number of accounts possible.
– RDFozz
Mar 6 '17 at 16:44
@Shawn It looks like MSAs are supported for 2012 and up link? - in a mixed environment including some pre-2012 instances, would you use MSAs for new servers and domain accounts for older servers? Or just bite the bullet and use domain accounts for all.
– Chris Di Carlo
Mar 6 '17 at 16:51
@Shawn It looks like MSAs are supported for 2012 and up link? - in a mixed environment including some pre-2012 instances, would you use MSAs for new servers and domain accounts for older servers? Or just bite the bullet and use domain accounts for all.
– Chris Di Carlo
Mar 6 '17 at 16:51
SQL Server can use MSAs and GMSAs, the link provided is from 2011. SQL server 2014+ can use both (if 2014 is updated with the latest patches and running on WS2012R2 + patches).
– Sean Gallardy
Mar 6 '17 at 16:52
SQL Server can use MSAs and GMSAs, the link provided is from 2011. SQL server 2014+ can use both (if 2014 is updated with the latest patches and running on WS2012R2 + patches).
– Sean Gallardy
Mar 6 '17 at 16:52
@RDFozz - I see your point. How would you manage all the different inter-relations of the accounts and where they are used? Tie it into the inventory management?
– Chris Di Carlo
Mar 6 '17 at 16:53
@RDFozz - I see your point. How would you manage all the different inter-relations of the accounts and where they are used? Tie it into the inventory management?
– Chris Di Carlo
Mar 6 '17 at 16:53
That's an option. You would probably want to document the accounts somewhere, in case naming conventions change or conflict somehow.
– RDFozz
Mar 6 '17 at 17:01
That's an option. You would probably want to document the accounts somewhere, in case naming conventions change or conflict somehow.
– RDFozz
Mar 6 '17 at 17:01
|
show 1 more 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%2f166354%2fshould-i-run-sql-server-services-on-multiple-servers-under-different-ad-accounts%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
Service accounts are not typically set to expire. I would have a different service account for each server with the exception that clustered machines may share the same service account. You can also look into using Managed Service Accounts which are non-interactive and can be fully managed from the AD Servers, further reducing the surface area.
– Jonathan Fite
Mar 6 '17 at 16:27
We don't currently have AD accounts used to run services set to expire but who knows if our security team will force the issue. I'll have to look at Managed Service Accounts. Are there any downsides to using them?
– Chris Di Carlo
Mar 6 '17 at 16:30
It's new (well, from Server 2008R2 and upwards) and not familiar. You can't log in as them so you can't troubleshoot issues that way. But they are the "way forward". Let's the security team manage them without needing to involve you or update the password on 75+ servers.
– Jonathan Fite
Mar 6 '17 at 16:41