Re: Cannot get agent jobs to run as NT AUTHORITY SYSTEM user

On Apr 2, 12:49 pm, Tonagon <tony.robe...@xxxxxxxxxxxx> wrote:
Hey everyone, I have a rather unique set of security rules to follow
here and cannot seem to enforce them and keep my SQL Agent jobs
running on the SQL 2005 system (I have it working fine on SQL 2000).
I am no DBA, but I am the best my particular group has so I am the one
working this.

We are removing BUITLIN\Administrators from the logins, so local
Administrators do not have access to SQL.
We are also changing the sa password every 90 days (via a compiled
script).
I am trying to get the SQL agent jobs (backups and optimizations) to
run as the local NT_AUTHORITY\System account.

The event viewer tells me that is does not have server access:
"The owner [NT AUTHORITY\SYSTEM] of job
TransactionLogMaintenance.Subplan_1 does not have server access."

I have given it all the access I can figure out how to give it, but no
luck.  It seems that once I remove BUILTIN\Administrators the SYSTEM
user loses access along with it, even though I specifically put it in
as a LOGIN with sysadmin role and I also ran this:

EXEC sp_grantlogin [NT Authority\System]
EXEC sp_addsrvrolemember @loginame = [NT Authority\System], @rolename
=  'sysadmin'

I could make the owner of the jobs sa, but then I would have to update
the properties and put in the new password every 90 days(right?).  I
don't even know the sa password, it is locked up elsewhere.  In order
for me to connect I have to have my bosses boss come over and log me
in.
However, for the time being I added my domain group to give me access
until I get this straightened out.

So, is it possible to have the jobs owned by SYSTEM the local Admin
group removed and still have the jobs run?
The SQL Agent service is running and it is connecting using the SYSTEM
account.
The backups are just to the local drive, so domain privileges are not
needed.

Thanks in advance!

I am no expect with the settings necessary on Windows but have hit an
issue or two including having to change the sqlagent service user back
to local to get it to run after upgrading to 2005 when it had been
working fine under 2000 with a user id. Our issues were due to a bug
and our being clustered.

I am thinking your issue might not be a SQL Server setting issue but
rather might be an OS setting issue. That is after you verify the
password case make sure that all necessary privileges are assigned at
the OS level. Someone may have removed one of the necessary OS
privileges/memberships that are expected to be there.

HTH -- Mark D Powell --

.

Relevant Pages

  • [NT] Microsoft SQL Server Agent Jobs Vulnerabilities
    ... The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com ... As the public role can submit jobs to the SQL ... This effectively drops its high level of privileges so no low privileged ...
    (Securiteam)
  • Re: procedure & function inside packages
    ... SQL> create table jobs ( ... SQL> CREATE OR REPLACE PACKAGE job_pkg IS ... SQL> CREATE OR REPLACE PACKAGE BODY job_pkg IS ... 'Chicken tickler') ...
    (comp.databases.oracle.server)
  • Re: SQL performance - multiple rows for SELECT (SQL0811)
    ... straightforward with SQL. ... SELECT aaa into:var from filea ... UPDATE fileb set xxx = 123 ... some high-volume batch jobs that have lots of embedded ...
    (comp.sys.ibm.as400.misc)
  • Re: procedure & function inside packages
    ... INSERT INTO jobs ... SQL> CREATE OR REPLACE PACKAGE job_pkg IS ... SQL> CREATE OR REPLACE PACKAGE BODY job_pkg IS ... 'Chicken tickler') ...
    (comp.databases.oracle.server)
  • Re: SQL 2005 Trying to run jobs owned by the NT AUTHORITY SYSTEM user.
    ... running on the SQL 2005 system. ... I am trying to get the SQL agent jobs to ... run as the local NT_AUTHORITY\System account. ... TransactionLogMaintenance.Subplan_1 does not have server access." ...
    (microsoft.public.sqlserver.server)