February 6, 2009 at 1:18 pm
Hi everyone,
We are just starting to use SQL Server Schedule jobs in our enviroment for our backups vs our other third party scheduling tool. So here I go enabling SQL Server Agent service on all of our servers. I ran across an issue with one of our servers where the agent just would not start up, no matter how many times we entered the password in. So finally I got the bright (maybe not so bright) idea to add the actual sqlserveragent domain account manually in sql server managment studio and I gave it sysadmin for fun... Sure enough it started fine without a hitch... So here I go and do some reading and found this article...
http://www.sqlservercentral.com/articles/Administration/sqlserver2005serviceaccounts/2341/
I now have a better idea on what may have happened... but no idea on how to fix it... I believe that the step during the installation to setup SQL Server Agent using a domain account was skipped until our security admin group was available to type in the password (The sql server service accounts are not managed by DBA's as part of segregation of duty). I checked the local group and sure enough SQLServer2005SQLAgentUser$InstanceName contains the domain user we wsh to use for this service... So why isn't working without the the user being manually added to the ACL?
Any thoughs?
February 6, 2009 at 1:24 pm
The SQL Server Agent is off by default so you need to go into Configuration Manager and change that, you can also change the account because if you skip the account SQL Server gives the limited permissions Network Service account to the Agent which may not be able to run Jobs.
Kind regards,
Gift Peddie
February 6, 2009 at 1:35 pm
yeah, that's the part that wouldn't work when we tried using our configured domain account for this service.... The only way to get it started was to have that domain account added to he SQL Server users list and give it sysadmin. I don't think that is the proper/best security practice way of having a service start up.
February 6, 2009 at 1:44 pm
On our servers we create one domain admin account per sql server instance this is used to run sql server, sql server and the network location where the backups are going to be saved. I have always assigned the sysadmin role to this user. The user is not used for anything else unless it is administering the server.
February 6, 2009 at 1:47 pm
In SQL Server 2005 we use the Agent for data automation so you really cannot use a none admin account because if you use the Agent to run DTS/SSIS package it must run in the context of an admin agent though that account can be a proxy account. That is the reason SQL Server gives the Agent the limited network service account.
Kind regards,
Gift Peddie
February 6, 2009 at 2:01 pm
We have a similar account for us DBA's here also, use to administer database servers. The services on the other hand are not managed by us, but by our security group. I beleive its a SOX thing, or maybe it's just company policy. Each service has it's own domain user account, which is part of the local windows group mentioned above. That group is then added (automatically) during the install to SQL Server and it has sysadmin. I believe the security reason for this type of setup is so that the service account, can't be used maliciously by the DBA's (why would we ever?!) to have access to the server and limit what the account can actually do to the server.
I'm not a security expert or anything, but I think that is the reason...
Does anyone know for sure?
February 6, 2009 at 2:10 pm
Each service has it's own domain user account, which is part of the local windows group mentioned above.
The account cannot be used to run Replication or data automation because the Agent does not have access to network resources. You have to spend time to know the impacts of the limited account and educate the security group because security group will not answer to management when replications or jobs are failing.
Kind regards,
Gift Peddie
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply