MS SQL Server 2005 - SQL Agent problems

[Database Administrator]

I am currently running SQL Server 2005 on a stand alone (not a domain
member) Windows Server 2003, this server was installed during setup
using local user accounts as service accounts.

As far as I recall at the time everything, including the SQL agent
functioned normally. At this time, the server itself still seems to
be running fine, however the SQL Agent service will not start.

I can not be sure as up until recently we haven't really been using
the SQL Agent, however I believe the problem would have started around
the time that service pack 1 was installed.

The following is the event log error message that is generated
everytime the SQL Agent service attempts to start:

###
Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Service Control
Event ID: 103
Date: 7/24/2007
Time: 3:55:22 PM
User: N/A
Computer: DBSERVER
Description:
SQLServerAgent could not be started (reason: SQLServerAgent must be
able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a
member of the SysAdmin role).

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
###

Please note that where the error message should indicate the name of
the service account (svc_sqlagent) it instead indicates "Unknown".

Also that SQL Server database engine running under the its own local
user service account (svc_sqlserver) works fine.

I have since tried recreating and reassigning a new local user service
account for the SQL Agent using two methods,

the first, the official approved method using the SQL Server
Configuration Manager:

results in the simple and vague error dialog box:
Title bar: WMI Provider Error
Error message: The process terminated unexpectedly. [0x8007042b]

The above error however produces no entries in any of the event logs.

If I start with a plain vanilla local user account, the config manager
does manage to setup some but not all of the special permissions/group
memberships necessary for the account to operate, before the error
occurs, but it does not appear to be consistent in how far it gets in
applying the correct parameters to the account.

the second method I used, was the manual service account setup from
knowledge base article Q283811.

this allows me to get the account setup, and the service set to log in
as the account, but when the service attempts to start, it fails, with
the same event log entry shown above.

also while trying to resolve all this SP2 became available, I first
tried to resolve this issue before installing SP2 however when I was
unable to, I went ahead and applied SP2 in the hopes that it might
help, as near as I can tell it has no effect, I attempted all the
previous methods and got the same results.

Any help with this would be greatly appreciated.

Thank you,
Jeffrey Williams

[Database Administrator]

Did you verify that the local account that you want to use for running SQL
Agent service is a member of the Local Users & Groups in the Group
'Administrators'?
and that the SQL Server login has the server role of 'sysadmin'

If the local user has that security it should be able to run SQL Agent

~Ellen

--
Thank you for sharing your knowledge


"sailorfej" wrote:

> I am currently running SQL Server 2005 on a stand alone (not a domain
> member) Windows Server 2003, this server was installed during setup
> using local user accounts as service accounts.
>
> As far as I recall at the time everything, including the SQL agent
> functioned normally. At this time, the server itself still seems to
> be running fine, however the SQL Agent service will not start.
>
> I can not be sure as up until recently we haven't really been using
> the SQL Agent, however I believe the problem would have started around
> the time that service pack 1 was installed.
>
> The following is the event log error message that is generated
> everytime the SQL Agent service attempts to start:
>
> ###
> Event Type: Error
> Event Source: SQLSERVERAGENT
> Event Category: Service Control
> Event ID: 103
> Date: 7/24/2007
> Time: 3:55:22 PM
> User: N/A
> Computer: DBSERVER
> Description:
> SQLServerAgent could not be started (reason: SQLServerAgent must be
> able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a
> member of the SysAdmin role).
>
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ###
>
> Please note that where the error message should indicate the name of
> the service account (svc_sqlagent) it instead indicates "Unknown".
>
> Also that SQL Server database engine running under the its own local
> user service account (svc_sqlserver) works fine.
>
> I have since tried recreating and reassigning a new local user service
> account for the SQL Agent using two methods,
>
> the first, the official approved method using the SQL Server
> Configuration Manager:
>
> results in the simple and vague error dialog box:
> Title bar: WMI Provider Error
> Error message: The process terminated unexpectedly. [0x8007042b]
>
> The above error however produces no entries in any of the event logs.
>
> If I start with a plain vanilla local user account, the config manager
> does manage to setup some but not all of the special permissions/group
> memberships necessary for the account to operate, before the error
> occurs, but it does not appear to be consistent in how far it gets in
> applying the correct parameters to the account.
>
> the second method I used, was the manual service account setup from
> knowledge base article Q283811.
>
> this allows me to get the account setup, and the service set to log in
> as the account, but when the service attempts to start, it fails, with
> the same event log entry shown above.
>
> also while trying to resolve all this SP2 became available, I first
> tried to resolve this issue before installing SP2 however when I was
> unable to, I went ahead and applied SP2 in the hopes that it might
> help, as near as I can tell it has no effect, I attempted all the
> previous methods and got the same results.
>
> Any help with this would be greatly appreciated.
>
> Thank you,
> Jeffrey Williams
>
>

[Database Administrator]

On Aug 15, 2:56 pm, Ellen wrote:
> Did you verify that the local account that you want to use for running SQL
> Agent service is a member of the Local Users & Groups in the Group
> 'Administrators'?
> and that the SQL Server login has the server role of 'sysadmin'
>
> If the local user has that security it should be able to run SQL Agent
>
> ~Ellen
>
> --
> Thank you for sharing your knowledge
>
> "sailorfej" wrote:
> > I am currently running SQL Server 2005 on a stand alone (not a domain
> > member) Windows Server 2003, this server was installed during setup
> > using local user accounts as service accounts.
>
> > As far as I recall at the time everything, including the SQL agent
> > functioned normally. At this time, the server itself still seems to
> > be running fine, however the SQL Agent service will not start.
>
> > I can not be sure as up until recently we haven't really been using
> > the SQL Agent, however I believe the problem would have started around
> > the time that service pack 1 was installed.
>
> > The following is the event log error message that is generated
> > everytime the SQL Agent service attempts to start:
>
> > ###
> > Event Type: Error
> > Event Source: SQLSERVERAGENT
> > Event Category: Service Control
> > Event ID: 103
> > Date: 7/24/2007
> > Time: 3:55:22 PM
> > User: N/A
> > Computer: DBSERVER
> > Description:
> > SQLServerAgent could not be started (reason: SQLServerAgent must be
> > able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a
> > member of the SysAdmin role).
>
> > For more information, see Help and Support Center at
> >http://go.microsoft.com/fwlink/events.asp.
> > ###
>
> > Please note that where the error message should indicate the name of
> > the service account (svc_sqlagent) it instead indicates "Unknown".
>
> > Also that SQL Server database engine running under the its own local
> > user service account (svc_sqlserver) works fine.
>
> > I have since tried recreating and reassigning a new local user service
> > account for the SQL Agent using two methods,
>
> > the first, the official approved method using the SQL Server
> > Configuration Manager:
>
> > results in the simple and vague error dialog box:
> > Title bar: WMI Provider Error
> > Error message: The process terminated unexpectedly. [0x8007042b]
>
> > The above error however produces no entries in any of the event logs.
>
> > If I start with a plain vanilla local user account, the config manager
> > does manage to setup some but not all of the special permissions/group
> > memberships necessary for the account to operate, before the error
> > occurs, but it does not appear to be consistent in how far it gets in
> > applying the correct parameters to the account.
>
> > the second method I used, was the manual service account setup from
> > knowledge base article Q283811.
>
> > this allows me to get the account setup, and the service set to log in
> > as the account, but when the service attempts to start, it fails, with
> > the same event log entry shown above.
>
> > also while trying to resolve all this SP2 became available, I first
> > tried to resolve this issue before installing SP2 however when I was
> > unable to, I went ahead and applied SP2 in the hopes that it might
> > help, as near as I can tell it has no effect, I attempted all the
> > previous methods and got the same results.
>
> > Any help with this would be greatly appreciated.
>
> > Thank you,
> > Jeffrey Williams


Hi Ellen,

Yes, I did. As I described in my previous message, both methods I
used in setting up the service account resulted in the correct
permissions for the account, in addition, the original account setup
carried out during the SQL Servers installation setup the service
account with the correct permissions, and the service didn't start to
have problems until later.

I believe the there is some break in the interface between the WMI/NT
Auth components, and the SQL Server Auth mechanism, this is why I made
sure to include the info for the WMI error dialog, and pointed that in
the event log error message the service account is not correctly
identified, but is instead listed as "unknown". If this were a
standard permissions issue, the account would have been correctly
identified as "svc_sqlagent".

If you have any other ideas, I would very much appreciate hearing
them.

Thanks,
Jeff

[mm11683@hotmail]

I ran into the same issue you are having and found a way to correct it. Frist if you start a tract you will notice the service is starting with the following lines:

set quoted_identifier off
go
select @@microsoftversion
go
select convert(sysname, serverproperty(N'servername'))
go
SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())
go
declare @agent_enabled bit declare @show_advanced bit select @show_advanced = cast(value_in_use as bit) from sys.configurations where name = N'show advanced options' select @agent_enabled = cast(value_in_use as bit) from sys.configurations where name = N'Agent XPs' if 0 <> @agent_enabled begin if 1 <> @show_advanced begin exec sys.sp_configure @configname = N'show advanced options', @configvalue = 1 reconfigure with override end exec sys.sp_configure @configname = N'Agent XPs', @configvalue = 0 reconfigure with override if 1 <> @show_advanced begin exec sys.sp_configure @configname = N'show advanced options', @configvalue = 0 reconfigure with override end end


If you break out these lines you will notice it is running:
exec sys.sp_configure @configname = N'Agent XPs', @configvalue = 0
which is disabling your agent xps and causing your sql server agent to fail. To correct this issue first run the following:
exec sys.sp_configure @configname = N'Agent XPs', @configvalue = 1
reconfigure with override
Then right click your SQL Server agent in enterprise manager and click properties. From the properties screen you will most likely notice that your Error log File Name is pointing to a place that does not exists or that the user you are trying to run the SQL Server agent under does not have access. Click the ... beside the file name box and point this to a place that your user has full control over. This will allow the SQL Server agent to at least start writing the errors you have in the SQLAGENT.OUT file and should allow you to start the SQL Server Agent.

Hope this helps.
Peace,
Matthew