June 9, 2010 at 1:43 am
The accounts Local Service, Local System and Network Service are "virtual" accounts that have specific permissions. LocalSystem is essentially a local administrator and can access anything on the machine, and it talks to the network using the machine's computer account. LocalService has much more restricted permissions on the machine, and uses anonymous permissions when talking to the network. Finally, NetworkService is like LocalService but uses the machine's computer account when talking to the network.
If your SQL service is set to run as LocalSystem then it will be able to back up to any local destination, so you don't really need to worry about permissions in that case.
Note that none of these three accounts are real accounts--they exist only in the context of the Services Manager--so you can't explicitly use them in folder permissions. If you wanted to specify folder permissions to match the SQL Server service account you'd need to use a manually-created account, not one of the three built-in ones.
June 10, 2010 at 12:07 am
Paul, when would I choose to run SQL Server under a specific service account? Can you give me some examples why chosing one over another would benefit me (as the aspiring DBA)?
June 10, 2010 at 1:21 am
I can think of a couple of situations where it would be helpful to run SQL services under a specific account:
a) You want to back things up across the network. I believe this practice is frowned upon for large databases, but for ones that are only a few Gb I think it's OK.
b) If you're using log shipping or SQL replication to maintain a copy of your database on a remote machine.
In both these circumstances SQL services will need to be reading and/or writing to a remote machine. While it would be possible to set up the permissions so the computer account of the machine has permissions on the relevant folders at the other end, it's easier to manage if you have a specific service account in use.
Note that I'm more of a general sysadmin than a full DBA, so there are probably half a hundred other situations I don't know about where having a specific service account is a bonus!
June 10, 2010 at 1:23 am
When you need the SQL server to access network resources off the local machine. File shares, backup locations, replication, log shipping, etc.
It is a recommended practice to have SQL using a limited privilege domain account rather than local system.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2010 at 4:49 am
OK, thanks, paul.knibbs and GilaMonstor. Thank you for allowing me to whittle away at this big question, here.
So, on my own machine, where the instance that I am admin these service accounts don't come into play unless i decide to file share for bcp or back up purposes? In that case can I practice granting someone access to my server using a service account?
When i installed SQL Server 2008 the training kit advised I create several service accounts before installing. I recall not doing it and attempting to use another default config where the same account is used for all.
End result is that in Config Manager I have
SQL SSIS running under .\Administrator
SQL Full Text Filter Daemon Launcher running under NT AUTHORITY\Local Svc
SQL Server running under .\Administrator
SQL Server Browser stopped under NT AUTHORITY\Local Svc
SQL Server Agent stopped under .\Administrator
There's nothing in the training kit that i can't do (except start sql server browser, which isn't impacting anything right now) but now that i'm trying to understand the "point" of these, I want to think of something that I can practice doing to test the real meaning behind these service accounts.
Would one of you give me something concrete to do that would expose the basic use of service accounts?
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply