SQL Training Q&A - 3
Q) I specified the SQL Server service to run under LocalSystem, but noticed a domain user account is recommended. Can you talk more about best practices? Does the service run as its own user in its own group? Does it belong in the Administrators group? I also notice that when I looked at the configuration tool that SSIS was running using NT AuthorityNetworkservice as opposed to LocalSystem like the others. Any reason why it is not using LocalSystem?
LocalSystem works for isolated SQL Servers. If you are managing multiple SQL Servers in a network, you're better off using a domain account, which will facilitate things like using linked servers or MSX jobs.
In general you should run services (not only SQL, but any service) with the least amount of previliges required to run it. In the past, it was a common practice to use LocalSystem to run all services, but that could lead to issues, since that is an administrator-level built-in account.
The best practice is to create an account specifically to run SQL Server. This account does not need any administrator priviledges, but will need some special rights, like "Run as a service". The SQL Server setup will grant the required rights.
If you have Active Directory and will have multiple servers running SQL, it makes sense to create the SQL Service account in AD.
Network Service and Local Service are non-administrator built-in accounts that can also be used to run services. This is definitely an improvement over running under Local System, but there are limitations.
There's a good reference on the limitations for running SQL Server 2005 Agent under each type of account at: http://support.microsoft.com/?id=907557