November 4, 2009 at 12:49 pm
I'm working through my Security Technical Readiness scripts and output for SQL Server 2005 instance for our PRODUCTION server.
A very key question I have is the need to use a BUILTIN\Administrator group member account (domain) as the service account startup logins for DBEngine, Agent, SQL Browser services. That is what they are doing here in my shop, telling me that it is necessary and should be exempted from change. Something tells me from all that I've read that this is wrong. it completely violates the "Principle of Least Priviledge" and compromises SQL Server hardness.
Here's what we are doing:
Local Users and Groups (on W2003 server - MSSQL 2005 instance)
Administrators Group Members
...
....
ARCENTDS\arcentsp
SQL Server (MSSQLSERVER) Log on as: ARCENTDS\arcentsp
Ditto, same service startup account, for SQL Server Browser service, and SQL Server Agent (MSSQLSERVER)
Am I right here, that we should create a new Win Domain account and give it just enough priviledges for database, transaction log and backup folders/files? This domain account should NOT be a member of the Administrators group. This domain account SQL Server Login can be Alter[ed] to sysadmin for MSSQLSERVER purposes.
Just need verification that I need to push to change this practice ASAP.
Fred Z.
Atlanta GA.
November 4, 2009 at 12:55 pm
Service accounts should only have enough privileges to do the function they were created to do.
Adding them to the Local Admins group is NOT the best practice as you have surmised. Though it is not a best practice, it is a common practice.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 4, 2009 at 1:43 pm
You are correct that you should usually go for the Principle of Least Priviledge. However, there are different requirements for different scenarios. If you feel though that you should be implementing a domain account few permissions or a system account with fewer permissions such as Network Service but just need justification to give the other IT guys in your shop, just point them to Microsoft's own best practices on the matter.
Here is a link to Microsoft's Best Practices paper that goes over the topic in detail:
Here is the download link for the SQL 2005 BPA tool which can scan your system and tell you if your service account setup is recommended:
Joie Andrew
"Since 1982"
November 4, 2009 at 1:54 pm
With SQL Server 2005 and greater - changing the service account using the Configuration Management tool will add that user to the appropriate Windows Groups on that system. If you are on a cluster, the groups are not added - and the service account is created in SQL Server and granted sysadmin rights.
You are correct - it is not recommended that your service accounts be a part of the administrators group. In fact, the recommendation is that you disable the BUILTIN\Administrators group in SQL Server - or my favorite is to remove sysadmin rights. Make sure you have added yourself and granted sysadmin rights before you do this or there could be problems.
Just one more note - generally you'll find that every server will have the following group added to the Adminstrators group on each server: Domain Admins. This group contains every person in the organization that needs domain admin rights (e.g. server guys, desktop support, helpdesk support, etc...). Do you really want someone from your helpdesk having full sysadmin rights to your critical databases?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply