Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server Agent not running Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 5:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:50 AM
Points: 65, Visits: 122
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.
Post #1421575
Posted Tuesday, February 19, 2013 6:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1421604
Posted Tuesday, February 19, 2013 6:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:50 AM
Points: 65, Visits: 122
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.
Post #1421611
Posted Tuesday, February 19, 2013 6:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1421618
Posted Tuesday, February 19, 2013 7:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:50 AM
Points: 65, Visits: 122
Thanks
Post #1421642
Posted Tuesday, February 19, 2013 11:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:50 AM
Points: 65, Visits: 122
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?

Post #1421777
Posted Tuesday, February 19, 2013 11:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1421783
Posted Tuesday, February 19, 2013 11:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:50 AM
Points: 65, Visits: 122
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?
Post #1421795
Posted Tuesday, February 19, 2013 12:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1421814
Posted Tuesday, February 19, 2013 12:58 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:50 AM
Points: 65, Visits: 122
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.
Post #1421817
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse