November 25, 2015 at 2:49 am
Hi,
I have a couple of questions around best practices for SQL Server Service accounts
1>> Should we be creating separate service account for each server/application.
2>> How frequently should we change the password of the service account?
3>> The services should be run under the SID created during installation or under the service account. If they are running under the SID will password change of the service account impact these?
4>> Should service account be having sysadmin permissions on the server?
November 25, 2015 at 3:02 am
Maverick100877 (11/25/2015)
Hi,I have a couple of questions around best practices for SQL Server Service accounts
1>> Should we be creating separate service account for each server/application.
Probably. If there is a single service account and this account is compromised all of your servers can be compromised.
2>> How frequently should we change the password of the service account?
What are the standards in your organization? You need to consider downtime, how many places this may need to be changed, and what mechanism. This can be a pretty difficult task depending upon your environment.
One suggestion is to change the passwords on a rolling basis. Do 1/4 of them on a monthly basis as an example.
3>> The services should be run under the SID created during installation or under the service account. If they are running under the SID will password change of the service account impact these?
They should run under the service account unless you have a requirement that makes that difficult.
4>> Should service account be having sysadmin permissions on the server?
Probably not. The service account normally only needs user level access to the server or SQL server. You will need to grant specific permissions on the server to this account.
A good link is this article by Jonathan Kehayias.
https://www.sqlskills.com/blogs/jonathan/sql-server-installation-checklist/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 25, 2015 at 3:38 am
Hi John,
In the link pasted by you its mentioned that provide permission to group SQLServerMSSQLUser$<ServerName>$<InstanceName> to the folders. I'm not able to find these in my SQL 2012 servers. Has this changed from older versions. I only see BrowserGroup.
November 25, 2015 at 3:56 am
The installation will assign the NT SERVICE group to the folder permissions.
NT SERVICE\MSSQLSERVER if using a default instance or NT SERVICE\MSSQL$INSTANCENAME if using a named instance.
When you change the accounts using SQL Server Config manager (if you ever do change accounts), it will add the new user into this group.
Have you thought about using managed service accounts instead of the standard service user account?
November 26, 2015 at 12:16 am
I guess MSA will not work with servers running on older versions like windows 2003 and SQL 2005. Also will MSA be useful if the servers needs access to domain resources.
November 26, 2015 at 2:41 am
MSA's are supported on 2003 and 2008 domain controllers aslong as you follow additional setup steps to enable the support.
MSA's are supported on 2008 R2 or above schema domain controllers.
I dont have a setup at the moment to test MSA's on older versions of SQL but shouldn't see any problems as long as the domain controllers are Server 2008 R2 or above schema.
As for domain resources, a MSA just acts like a normal account, you grant it rights to network areas it needs. The beauty is the password automatically changes every 30 days and AD takes care of changing the password for the services so you don't have to. The only caveat is in a WSFC as you still need to use standard user service accounts for them.
November 30, 2015 at 2:18 pm
I have installed many SQL instances using gMSA accounts. To use these with SQL 2012 or SQL 2014, the account name must end with a $ and be no longer then 15 character. With SQL 2016 the requirement to end with a $ is gone, but because a gMSA is a child of a AD Computer object the account length must still be no longer than 15 characters.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply