Picking Service Accounts
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/pickingserviceaccounts.asp
-
Steve, I pretty much agree on all items you raise and have pretty much adopted this for my installs of sql2005. I also adopted this for sql 2000 where I could. One point though which needs to be considered is the 'buggy' nature of sql server in respect to service accounts. Specifically under sql2000 running the agent and sql services under different logins can actually cause functionality to 'break'. I had an instance of this, but before you ask I cannot for the life of me remember what it was (should have documented it as memory not as good as it used to be). This may not be the case under sql2005. Best practice is great but may not always be possible and can cause much pain on some occasions.
Derek
-
Thanks for making some suggestions on how to configure service accounts. I'd really appreciate if you could expand a little bit more on the pros and cons of utilizing a domain account to run the services, and why is it more beneficial to utilize individual accounts for each server. I can see the benefits of having separate accounts for each service, but creating an individual account for each service, on each server, seems like a tremendous maintenance chore.
-
Thank you for answering my question. I have been tasked with the “additional task” of security beyond the database and for the life of me could not remember the level or complexity of a multi-server service security.
Thank you again,
Erik Skinner
-
There is a not explanation how to work with scheduled DTS packages between different servers. If each one has a different agent service account login there is no ability to use Windows security. Then DTS should use SQL account. It is not very convenient as well. In many cases it may be reasonable to separate production, validation and development service accounts, but looks like it bring a lot of overhead to maintain separate logins per service per server.
Theory and practice is not always the same.
-
I would be hesitant to use names which so clearly identify what the purpose is. I understand it from a manageability perspective, but there is an information disclosure risk...
In a default Active Directory install, any authenticated user has the ability to browse the directory. Don't believe me? Try and run ADUC (Active Directory Users and Computers) with a non-privileged user account. While you won't be able to change anything, you will be able to see all users, etc. As a result, it would be easy for an attacker or malicious user to figure out what service account to target.
We try to blend in our service accounts and privileged accounts within the naming convention of our normal users. While this won't stop a knowledgeable user (rogue admin, for instance), it does stop the merely curious. It also puts one more stumbling block in the path of an external attacker.
K. Brian Kelley
@kbriankelley -
Steve,
good article.
One comment for everyone:
Many of us install SQL Server as a database server for the third-party applications. Application vendors mostly develop when running SQL Server on Local System account because as Steve mentioned, on OS prior to 2003 it was a convenient built-in account allowing to run as a service. Application features may require drive access for application attachments, some other rights for other functionality. Consult with the application vendor what are the minimal permissions needed for your SQL Server Startup account. I did have a calls when someone removed System (Local System Account) from NTFS permissions on the SQL Server folders and SQL Server could not start with the access denied message because it was trying to start on the Local System account.
Regards,Yelena Varsha
-
Thanks for a great article, Steve. We are using most of the conventions described in it (OK, maybe not "15-20 character, mixed case, numbers, letters, very strong passwords") and at times I've wondered if the extra work setting it up was worth it. After reading your article I no longer have those doubts.
I also wanted to share experiences similar to those in the post from LP in the hopes others may benefit. I apologize if this topic has been covered in previous articles/posts.
I had some struggles when first developing DTS jobs using "Windows Authentication" and scheduling them with Server Agent. I'd develop the jobs using an account with Sys Admin privileges (poor practice) and wonder why they didn't work when scheduled with Server Agent. Then I realized when I ran the job from DTS designer on my workstation it used MY domain account's security context (and my workstation's file system context) but when I ran the job from Server Agent it used Server Agent's domain account security.
As soon as I adopted the practice of creating SQL Server logins from windows domain groups, putting them in database roles, and granting permissions to the roles, life got much better. I have a "normal" test domain account that I put into the appropriate domain group. I then log into my workstation with it and test the DTS job in DTS Designer. I also create a Server Agent domain account login on the target SQL Server(s), add it to the appropriate SQL Server role(s) and rest assured it will not run with reduced permissions. This works for server to server jobs because the Server Agent account uses Active Directory authentication to the target servers. If security problems are still suspected, one can always dig out that paper list of very strong passwords and log in to their workstation with the Server Agent account to brute force test it directly in Designer.
Note:
It would be much cleaner to put the Server Agent domain account in the domain group (instead of the SQL Server role on the target server) , but changes in domain group memberships are only recognized at log in. (If you're already logged in with an account and you add it to a domain group, your "session" won't reflect addition to the group.) So, you'd have to stop and start the Server Agent service for its new domain group membership to be recognized. In some cases that's no big deal, in others it may not be practical.
-
Thanks for the complements and apologies for the delay in responding.
I think that using a separate account for each service is a pain. A real admin pain, but it's a one time deal. Or a very rare deal, but it does allow you to separate out their permissions and also track back issues. You can also, say, increase SQLAgent or SSIS permissions without messing with the base server, thereby keeping your attack surface low.
As far as a domain account. I think this is best because of DR. If you need to quickly move to a new server and you install sql, you have the account ready to go. Just specify it for the new server and you're in good shape.
-
great article, but I still have one question. why not use the included Network Service instead of a domain acct? there are no passwords to change. I know MS advises against it, but I cannot find out why? Please respond with any ideas on the matter.
thanks!!
-
The Network Service account is supposed to run with less privileges than System on the local server. However, when it has to go across the network, it effectively does as the System account, meaning it runs as the computer account itself.
If we just look at locally, there are some rights SQL Server will need in the Local Security Policy that aren't granted normally to Network Service. Among those are the Log On as a batch job. The SQL Server service account needs this right. In SQL Server 2000 this is handled by modifying this right and granting the user the right directly into the local security policy. In SQL Server 2005 it's handled by creating a local group and then granting the local group the right. This is something, by the way, that System has by default, so it's not seen in log on as a batch job. However, Network Service does not have this right normally.
Also, should you install any other applications, such as IIS, on the system, the default application pool identity is Network Service. If Network Service is the service account for your SQL Server, that means any web applications will have sysadmin access to your SQL Server. This would include Reporting Services, which requires IIS, and though it uses its own application pool (ReportServer), the identity for that application pool is, you guessed it: Network Service. A compromise of the Reporting Services web application would therefore lead to a potential compromise of the entire SQL Server, not just the Reporting Servers databases.
K. Brian Kelley
@kbriankelley -
How about the service account name itself ?
We're testing service account names structured like e.g. SQL.DB.Servername.Instancename
This works except for Reporting server.
What are orther issues that point on using special characters (the decimal point) in service account names ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Nice article, I just wanted to know if we already have SQL 2005 setup to run as a local administrative how can I change it to a normal user?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply