SQLserverAgent login failed for user sa

[u***@gmail.com]

I have a SQL server which has been running without problems for
months. Out of the blue, the SQLAgent service now refuses to start.
The message in the error log is "SQLServer Error: 18456, Login failed
for user 'sa'. [SQLSTATE 28000]". SQLAgent has never been configured
to login as sa on this machine. The service startup account is
System, and it is configured to log into the database using SQL server
authentication, using a user called "Agent User". I've confirmed that
using Enterprise Manager. I even changed the "Agent User" password in
the database to something else, and then changed it in the Enterprise
Mgr SQLAgent properties tab, but it still gives me the same message.
Why is it trying to login as "sa" when I haven't specified that
anywhere?
SQL Server 8 SP4, Win2K3 SP2.

[Brett I. Holcomb]

Check the properties of the service and see if it's set to use the Local
System account or sa as it's login.

Post by u***@gmail.com
I have a SQL server which has been running without problems for
months. Out of the blue, the SQLAgent service now refuses to start.
The message in the error log is "SQLServer Error: 18456, Login failed
for user 'sa'. [SQLSTATE 28000]". SQLAgent has never been configured
to login as sa on this machine. The service startup account is
System, and it is configured to log into the database using SQL server
authentication, using a user called "Agent User". I've confirmed that
using Enterprise Manager. I even changed the "Agent User" password in
the database to something else, and then changed it in the Enterprise
Mgr SQLAgent properties tab, but it still gives me the same message.
Why is it trying to login as "sa" when I haven't specified that
anywhere?
SQL Server 8 SP4, Win2K3 SP2.

[u***@gmail.com]

Post by Brett I. Holcomb
Check the properties of the service and see if it's set to use the Local
System account or sa as it's login.

The service is set to use the Local System account. Also, I have
traced the beginning of the problem to when the machine was promoted
to a backup domain controller on our AD domain. Is it related to the
fact that SQL server was originally installed using the local
administrator account, and now the machine is a DC and a local
administrator no longer exists? The local administrator was never
used for authentication (service or sql login). I really hope there
is a solution to this other than having to reinstall SQL server.

[u***@gmail.com]

I was able to track down the following KB article which may apply:

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B320338

It seems to indicate that the problem applies to Win2K server and
SQL2K SP1-SP3, but I am running Win2K3 server and SQL2K SP4.
Anyone have any information on this or any other workarounds?
I really don't want to setup and switch to Windows authentication for
SQLagent.

[Ekrem Önsoy]

I don't understand why you do not want to use Windows Authentication and why
not using a Domain User account for this service?

This is the recommended method to apply.
--
Ekrem Önsoy

Post by u***@gmail.com
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B320338
It seems to indicate that the problem applies to Win2K server and
SQL2K SP1-SP3, but I am running Win2K3 server and SQL2K SP4.
Anyone have any information on this or any other workarounds?
I really don't want to setup and switch to Windows authentication for
SQLagent.

[u***@gmail.com]

Post by Ekrem Önsoy
I don't understand why you do not want to use Windows Authentication and why
not using a Domain User account for this service?
This is the recommended method to apply.

This is an application that sets up its own sql server instance and
the application vendor refuses to provide the password to any admin
user, including sa. I managed to gain sa access through some hacking,
but I am reluctant to make changes to how the application setup the
instance, because it may affect our ability to get support for the
application. Also, I tried changing the sqlagent service account to a
domain account, but that exposes another SQL server bug: Error 22042:
xp_SetSQLSecurity() returned error -2147024890, 'The handle is
invalid.'
I am new to SQL server...bad first impressions with all these bugs in
a supposedly mature product (4 service packs).