July 3, 2008 at 3:59 pm
Should SQL Server 2005 use the local system account (run as local service) or should it have its own account created for it?
If the answer is, "It depends", depends on what?
I know it can be done either way and how to do it, the question is why?
I've had an issue with this. We have a server that will be deployed to a customer. We bought the server and installed SQL Server on it along with the application server it is intended to work with. Whoever set up SQL Server created a user account for it instead of using the local system account.
This was not noticed until today, when the server is being packaged for shipment, and the application server's backup command was failing with the simple message "Database backup failed." The engineer doing this spent hours talking to tech support with no luck before he sought my help. After a bit of log-digging and troubleshooting, the problem was NTFS permissions were not granted to the service user account on the place where the app server insisted on performing the backup.
And, that led me to wonder, why is there a service user account? If I ask the person who did this why he did it, he'll have an answer. It may or may not be correct, but I had better have an informed rebuttal cocked and ready. This person is above me in the food chain...
July 3, 2008 at 6:13 pm
Well good Question and will be a good discussions:
so here is my start and 2 cents:
The Local System option specifies a local system account that does not require a password to connect to SQL Server on the same computer. However, the local system account may restrict the SQL Server installation from interacting with other servers, depending on the privileges granted to the account. (BOL)
Check this link for complete explanation, if you have missed this one:
http://msdn.microsoft.com/en-us/library/ms143691.aspx
(good Explanation)
Maninder
www.dbanation.com
July 7, 2008 at 8:24 am
It looks like Microsoft recommends that a domain user account be used for the SQL Server service.
http://msdn.microsoft.com/en-us/library/ms144228.aspx
These are listed among security best practices:
Run SQL Server services with the lowest possible privileges.
Associate SQL Server services with Windows accounts.
The problem in this case is that SQL Server is, in effect, a component of an application server. The application server assumed that SQL Server had permissions to backup anywhere on a local drive (as would be the case with Local System), which caused the problem because the domain acccount didn't have those permissions.
I suppose my real problem in this case is responsibility and communication. Someone who was not responsible for the end product changed SQL Server's default security settings without telling anyone.
This server is heading to an isolated network with automation equipment as clients and very little chance of an actual DBA being available to support it. In that case, administrative ease takes precedence over security.
July 7, 2008 at 8:37 am
If you don't need permissions off the server, meaning no communications (mail, copy backup, agent, etc.), then use Local Service, not Localsystem. Local System is an admin level account.
If you need those permissions, you set the account with the Configuration Manager or SSMS and the permissions will be granted. If you do this, you should be OK, but f an application rights to a specific directory that isn't granted to EVERYONE on the domain, you will have issues.
Rights should be early on your list when something simple doesn't work. Various clients will lock things down more than normal and you should expect that as a possible cause.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply