February 19, 2013 at 5:14 am
Hi,
I have a SQL Server Standard 2008 R2 instance running which was upgraded from the Express version. This is on a Windows Server 2008 R2 machine set up as a domain controller.
I am now trying to implement some Replication (following the Stairways series here) and am getting problems due to (I think) the SQL Server Agent not running.
First confusion is that under services.msc it states that the SQL Server Agent is running, but inside SSMS the SQL Server Agent node has a little white x inside a red circle indicating that it is not running. If I try to start it inside SSMS I get a message:
Unable to start service SQLAGENT$SQLEXPRESS on server <my server name>. (mscorlib)
Additional Information:
The SQLAGENT$SQLEXPRESS service on < my server name> started and then stopped. (ObjectExplorer)
The SQL Server Agent is set to log on as "Network Service".
I have found lots of stuff about this on-line but nothing seems to work, or I am getting lost in the quagmire of too much information. Any guidance would be welcome.
February 19, 2013 at 6:14 am
Which service account is the Agent service logging in as?
Per this article in addition to enabling the service you must also change the SQL Server Agent service account and ensure the account specified is a member of the SQL Server Agent local group.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 19, 2013 at 6:28 am
It is the Network Service account.
I have changed it to use the administrator account (which I know is a bad idea) and it started successfully. I guess I'll need to get their IT admin to create a new user which I can then add in to SQL Server as a sysadmin and use that one instead.
February 19, 2013 at 6:48 am
I do not think you need to add anyone to the sysadmin Role for this. Per the article you just need to add the service account you decide to use to start the SQL Agent service to the SQL Server Agent local group. On my machine that group is named...
SQLServerSQLAgentUser$MYCOMPUTERNAME$SQL2008R2DEV
...where MYCOMPUTERNAME is my computer name and SQL2008R2DEV is my instance name.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 19, 2013 at 7:21 am
Thanks
February 19, 2013 at 11:07 am
opc.three (2/19/2013)
I do not think you need to add anyone to the sysadmin Role for this. Per the article you just need to add the service account you decide to use to start the SQL Agent service to the SQL Server Agent local group. On my machine that group is named...SQLServerSQLAgentUser$MYCOMPUTERNAME$SQL2008R2DEV
...where MYCOMPUTERNAME is my computer name and SQL2008R2DEV is my instance name.
Sorry for bugging you again, but I'm still struggling with this. The IT admin is not finding any SQL Server Agent local group or any group with a similar name to SQLServerSQLAgentUser$MYCOMPUTERNAME$SQL2008R2DEV.
This is on a Windows 2008 Server, is that the difference here?
The article has a different solution which mentions:
Step 2: Add the SQL Server Agent service security principal as a system administrator for SQL Server 2008
Note The service security principal is named "NT SERVICE\MSSQLSERVER" for default instances and is named "NT SERVICE\SQLAGENT$Instance Name" for named instances.
I do have this user as a sysadmin. Is this the user that should be used for the SQL Server Agent "Log on as"? How would I know what password this user has?
February 19, 2013 at 11:21 am
Frank Cazabon (2/19/2013)
opc.three (2/19/2013)
I do not think you need to add anyone to the sysadmin Role for this. Per the article you just need to add the service account you decide to use to start the SQL Agent service to the SQL Server Agent local group. On my machine that group is named...SQLServerSQLAgentUser$MYCOMPUTERNAME$SQL2008R2DEV
...where MYCOMPUTERNAME is my computer name and SQL2008R2DEV is my instance name.
Sorry for bugging you again, but I'm still struggling with this. The IT admin is not finding any SQL Server Agent local group or any group with a similar name to SQLServerSQLAgentUser$MYCOMPUTERNAME$SQL2008R2DEV.
This is on a Windows 2008 Server, is that the difference here?
The article has a different solution which mentions:
Step 2: Add the SQL Server Agent service security principal as a system administrator for SQL Server 2008
Note The service security principal is named "NT SERVICE\MSSQLSERVER" for default instances and is named "NT SERVICE\SQLAGENT$Instance Name" for named instances.
I do have this user as a sysadmin. Is this the user that should be used for the SQL Server Agent "Log on as"? How would I know what password this user has?
The service account, e.g. NT SERVICE\SQLSERVERAGENT on my 2008 R2 default instance, should have already been in the sysadmin Role is what I meant. And I can confirm that I have a group by the name of SQLServerSQLAgentUser$MYCOMPUTERNAME$MSSQLSERVER on my Server 2008 R2 machine as well, where MYCOMPUTERNAME is my servername and the instance is a default instance hence the MSSQLSERVER part.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 19, 2013 at 11:45 am
I can confirm that I have a group by the name of SQLServerSQLAgentUser$MYCOMPUTERNAME$MSSQLSERVER on my Server 2008 R2 machine as well, where MYCOMPUTERNAME is my servername and the instance is a default instance hence the MSSQLSERVER part.
So what do I do if I don't have this group available?
February 19, 2013 at 12:55 pm
When you changed the service account for the service did you use the Services MMC (e.g. from Control Panel>Administrative Tools) or did you use the SQL Server Configuration Manager?
Just confirming, is the SQL Agent service started? Is it still showing with a red X in Object Explorer?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 19, 2013 at 12:58 pm
I changed the account for the service from the SQL Server Configuration Manager.
Everything works if I use my administrator account. But I think this is a bad idea so I want to use a more secure account for it. This is where I have problems.
February 19, 2013 at 1:25 pm
The service account should not need to be a local admin, and I agree with you, I think it's a bad idea.
Here is the description of the SQLServerSQLAgentUser$MYCOMPUTERNAME$MSSQLSERVER group on my server.
Members in the group have the required access and privileges to be assigned as the log on account for the associated instance of SQL Server Agent.
That seems to point to the exact issue you're having, getting the service account the necessary permissions at the OS-level without granting Administrator.
I just ran a search on all ACLs on my machine and this is what my local group is able to access:
1. Full Control over C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\JOBS
2. These effective permissions on C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log
Traverse Folder
List folder / read data
Read attributes
Read extended attributes
Create files / write data
Create folders / append data
Write Atributes
Write Extended Attributes
Delete subfolders and files
Read permissions
You could create your own group, grant it these permissions, add your non-admin service account to it an give it a go. It may not get you there since there could be registry permissions or other local policies in play but it's worth a shot. If not then you could consider opening a ticket with Microsoft....or simply uninstalling and re-installing from scratch.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 19, 2013 at 7:29 pm
Thanks again. I just started trying that and found that there is no jobs folder under my sql server installation!
At this stage I'm thinking I need to reinstall.
< later >
I found the folders, they weren't installed on the C: drive but along with the data folder which was configured for another drive.
Interesting thing: the log folder has access rights granted for SQLAgent$SQLEXPRESS, but there is no such user or group defined in the active directory!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply