I would like to refer to the following technical article
SQL Server 2005 Security Best Practices - Operational and Administrative Tasks
http://www.microsoft.com/technet/prodtechnol/sql/2005/sql2005secbestpract.mspx
Among best practices for SQL Server service accounts on page 8, it is recommended to 'use a separate account for each service'. I created separate account for each service as advised and assign account to relevant Windows group created for each SQL Server service during SQL setup.
Now when I run Best Practices Analyzer, its report seemed to contradict what the above article said. For example, BPA reports excerpts:
"We recommend that the service SQLBrowser on host MachineName be run under Network Service Account". I get similar recommendation for SQLSERVERAGENT account as well. Most importantly, it recommends that MSFTESQL be run under SQL Server Service Account.
Can anyone of you shed some light on it?
Thanks,
Asaf
BPA is actually accurate with respect to SQL Browser. The sole purpose of this service is to resolve named instances. So, the first thing that you do on a machine that does not have a named instance is to disable the service. If you have named instances, then simply setting it to Network Service is sufficient, because it does not need to access any resources external of the machine that it is running on.
I run SQL Server Agent as a named Windows account and could really care less what BPA has to say about it. The reason that I run it under a named Windows account is that every system I've worked on has jobs that need access to a variety of resources. Many of these drop backups on machines other than where the instance is running or grab and move files from other systems. You can't perform any of those operations running under Network Service.
As for MSFTESQL, if you are not using full text indexing, disable this service. If you are using full text indexing, I use a separate account for the service so that I can isolate it and separately manage it from everything else.
The "separate account for each service" is a really great concept, but you have to determine what is best for you. If you have a single SQL Server instance, having a separate service account isn't that big of a deal from a management perspective. What happens when you are suddenly managing 10 instances, which really isn't that hard. Are you really going to create 40 different accounts - one each for Browser, SQL Server, SQL Server Agent, and Full Text for each instance? (Even more if you are using SSIS, SSRS, SSAS, etc.) Are you even going to be able to remember the different passwords for each? This separate account thing can be taken to idiotic proportions. I worked with one large organization whose security group dictated that each service on each machine was required to have a different account, which also meant a different strong password that was a minimum of 12 characters with upper, lower, numbers, and special characters. One of the sysadmins carries a large notebook with him when he leaves the office and it sits on his desk the rest of the time that contains over 40,000 logins/passwords just for the systems he is responsible for. One of the senior DBAs walks around with almost 15,000 logins/passwords on a PDA with a paper backup in his desk for the database servers he is responsible for. When you have to start writing down the logins and passwords in order to accomplish "separate account for each service", you have obviously taken things too far and defeated the purpose of doing this in the first place.
|||Hi Michael,
I appreciate your comments and sharing your experience how complicated it could get if we decided to create separate account for each SQL Server machine. I manage 10 servers, all in DMZ, and I have been trying to decide on security policies.
Thanks Again,
Asaf
|||For each server positioned in a 'DMZ', I recommend keeping individual service login accounts.
If any one server is ever compromised, you do not want to make it so easy for the others the be compromised.
Inside the DMZ, (double firewall), I agree with Michael's comments in respect to using a single highly guarded domain account (with exceptionally strong password) for SQL Servers, and I probably would not allow that domain account to have 'interactive login' priviledges..
|||The interactive login privileges is a REALLY important one. Prior to SQL Server 2005, you didn't have a choice, since the service accounts used were local admins on the machines. But, service accounts for 2005 should NEVER have admin level authority, nor should they be able to login to the console. This brings up a really good example at another customer who has decided that under no circumstances will DBAs have access to the machines running their SQL Servers. There is an entirely different set of logins which are granted access to machines and only created for system admins. So, each system admin has two accounts, the one they login to their machine with and the one they login to servers with. What they forgot is that the DBAs are the ones who setup the SQL Servers and so they are given the service account and password. Since this is a SQL Server 2000 environment, they make those service accounts administrators on the machines. Then, since they are not allowed any access whatsoever to the machines their SQL Servers are running on, they bypass everything by using the service account to login to RDP so that they can actually do the things necessary to manage their SQL Servers. So, now it is impossible to actually audit any actions which occur. There are many examples of sheer stupidity in security policies.
Security systems exist for 2 reasons:
1. Keep the people out who shouldn't be there in the first place
2. Providing audit trails for those people authorized to perform actions
If you ever have a security policy that either impedes or prevents someone from taking legitimate actions which are required for their job, you need to throw the policy away and start over. Because, leaving such a policy in place will either damage your business, force the people who have to manage the systems to decide they should go somewhere that they are allowed to do their job, or will have the admins themselves concoting ways to bypass security leading to gaping holes that can also be exploited by hackers.
|||Arnie,
Thanks for making an interesting point for having a separate account for each DMZ server. So after all extra bit of work required to set up individual accounts for each machine seem to provide an extra layer of protection.
Michael,
I do very much appreciate another valuable post from you. It reflects your deep understanding of security and years of experience in the industry.
Asaf
Tim Hatcher wrote: