Best practices for setting up service users
-
I'm very new to setting up SQL Servers, and I'm wanting be diligent about security. I'm currently setting up a SQL 2016 instance. I've been working through this whitepaper here (even though its for 2012):
https://download.microsoft.com/download/8/F/A/8FABACD7-803E-40FC-ADF8-355E7D218F4C/SQL_Server_2012_Security_Best_Practice_Whitepaper_Apr2012.docxHere is the current service account setup for one of our 2012 instances:
After reading the service account section on the whitepaper, I think we need to downgrade the last account (Agent) because it is also a domain admin. (So we would just use a non-domain admin account). However, would anyone have any recommendations on this setup? Should we be using a domain account for any other services? How can I follow the principle of the least privileges here? I know these are very basic questions, but I am very willing to learn and I thank you for any help.
-
Good additional reading can be found here: https://dba.stackexchange.com/questions/165363/recommended-sql-server-service-accounts
It boils down to what is best in your environment. I've worked at several organizations, and I always advocate for the database engine and SQL Agent service accounts to be domain accounts, and be configured for least necessary privilege. Sometimes (like Failover Clusters and AlwaysOn) those privileges need to be increased to communicate with all the nodes in the configuration.Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Thanks, Alan. I'm working through the link and noticed the MSDN article states Microsoft recommends using a Virtual account for the Database Engine and the Agent. Why do you prefer Domain accounts for these? Is it because you need the Engine and/or Agent to connect elsewhere also?
-
I must have misread that part in the MSDN article, or they're referring to something else.
I've always used a domain account, simply because its domain permissions (which would be the same for every database engine (theoretically) and every SQL Agent) are consistent across the enterprise. Also, following MS recommended best practice to use one domain account for db engine and a different one for Agent, AND, to use this differing combination on all instances of SQL, the initial domain service account, along with its permissions, can be easily cloned to create new ones as needed. Definitely will require communication and cooperation with the "AD Administrator" to make the process easier.
When using the "default" accounts, they would have to be manipulated after every installation to match whatever permissions are appropriate for your environment.Simply put, I'm a DBA...which means I'm lazy--I don't want to have to repeat required steps for something if it can be automated. 🙂
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Oh...and, if you ever run into instances that participate in clustering or AlwaysOn, the default "virtual" accounts are a MASSIVE headache to get to work. Domain accounts (and using the same one(s) for all instances) will make life easier. In some cases, especially with AlwaysOn, if a default account is used, and not given explicit permission (based on its local machine identifier: MachineName\MSSQL.InstanceName) in all other machines participating in AlwaysOn, the databases won't be accessible from one side or another (wherever the login is not given explicit permission).
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Thanks again. One more question if that's okay. Our current setup drive setup is as follows:
C: - SQL installation
D: - Data files (MDF and NDF)
S: - Log files
U: - Backups
T: - TempDB (MDF and LDF file for TempDB)Is this setup really necessary or optimal? We're running in a virtualized environment (VMWare). I've read a few older articles that suggested splitting everything out like this might be good. However, I saw a never webinar that suggested otherwise.
-
Since this is a virtual server, are these "drives" sourced from a SAN? If so, each LUN may be on the same physical spindles. In that case, it won't provide you any performance advantage to have separate drives, because each read and write will essentially be random I/O instead of sequential.
https://www.brentozar.com/sql/sql-server-san-best-practices/ -
Yes, it is sourced from the SAN. Thank you for the link. I will check it out.
- DataAnalyst011 - Friday, August 18, 2017 8:28 AMI'm very new to setting up SQL Servers, and I'm wanting be diligent about security. I'm currently setting up a SQL 2016 instance. I've been working through this whitepaper here (even though its for 2012):
https://download.microsoft.com/download/8/F/A/8FABACD7-803E-40FC-ADF8-355E7D218F4C/SQL_Server_2012_Security_Best_Practice_Whitepaper_Apr2012.docxHere is the current service account setup for one of our 2012 instances:
After reading the service account section on the whitepaper, I think we need to downgrade the last account (Agent) because it is also a domain admin. (So we would just use a non-domain admin account). However, would anyone have any recommendations on this setup? Should we be using a domain account for any other services? How can I follow the principle of the least privileges here? I know these are very basic questions, but I am very willing to learn and I thank you for any help.
Hmmph. Yet another "expert" white paper that recommends disabling xp_CmdShell "unless you absolutely need it" without mentioning the fact that, by default, the only people that could use it if it's enabled are the same people that can enable it. They also don't take the time to explain how to use it safely but they don't seem to mind using SQLCLR, which frequently requires the database that contains it to be set to TRUSTWORTHY, which they later explain is a bad thing.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - DataAnalyst011 - Friday, August 18, 2017 11:18 AM
Hopefully it's this article you are reading through:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissionsIt's really saying that if you use a built-in local account (such as local system, network service,etc) it will use the virtual accounts in place of those and then lists what they would look like. They are just managed local accounts.
It also references the issue on clusters - the SIDs need to match on failovers so this is why you need to use a domain account.
In terms of the permissions, you don't want to mess with those at all. You need to manage the accounts through Configuration Manager and let SQL Server manage the permissions. It will set the different permissions for the service account, the group that the service account belongs to, the login in SQL Server. And that will be the least permissions needed.
If you use a domain account, it will be a combination of the domain account, the service itself and the Windows group that is granted permissions based how SQL Server itself uses these. It's a bit complicated and that is some of the reason you should really let Configuration Manager (or the installation process initially) manage those. Some of this is explained by a MS employee in this post:
Service/Database Accounts - NT SERVICE\MSSQLSERVER & NT SERVICE\SQLSERVERAGENT … what are they for?If you want to understand it further, you can search on per service sid
And that will bring back quite a few results on the whole subject. The guy who wrote the security best practices you are reading wrote a little bit about it in the post:
About SQL Server’s usage of Service SIDsYou can tell from his post, there are some things that need to be granted to the windows group, some to the service, some to the domain account. So it's a combination of the three that are used for the service to have the correct permissions and rights.
Sue
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply