We help IT Professionals succeed at work.

SQL Server Job permissions query

conorbrophy
conorbrophy asked
on

I have a issue. The SQL Agent job to backup a transaction log fails with the error:

Job outcome:
"The job failed.  The Job was invoked by User USERGROUP\sqlservice.  The last step to run was step 1 (Backup)."

Backup task:
"Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029).  The step failed."

The backup task log output is as follows:

"Logged on to SQL Server 'XYZ' as 'TESTGROUP\srsqlservice' (trusted)
Login '' is not the DBO of database 'TestDB' so cannot run SQLMAINT.EXE."


The SQL Agent service is running as Local System Acccount.

The SQL Server database service is running as 'TESTGROUP\srsqlservice'.

All jobs and databases are owned by SA. When I add the 'TESTGROUP\srsqlservice' user to the database TestDB with db_owner privs, the backup jobs works fine. I just can't understand why the SQl agent job is trying to log onto the database as TESTGROUP\srsqlservice when the SQL Agent is running as the Local System Account?

Thanks,

  Conor
Comment
Watch Question

Hi Conor,
What exactly is in the backup job which SQL Agent is running?


Author

Commented:
Hi there,
Backs up a transaction log. i.e.
EXECUTE master.dbo.xp_sqlmaint '-D TestDB -Rpt "D:\MSSQL7\LOG\TestDB Transaction Log.txt" -DelTxtRpt 1WEEKS -WriteHistory  -VrfyBackup -BkUpMedia DISK -BkUpLog "D:\MSSQL7\BACKUP" -DelBkUps 3DAYS -CrBkSubDir -BkExt "TRN"'
Hi Conor,
One of the paramters for xp_sqlmaint is -U loginid and if not sepcifed sqlmaint attempts to connect using Windows Authentication

Author

Commented:
But why is it trying to connect using TESTGROUP\srsqlservice? The SQL Agent service s running as the Local System Account.
The job does not always necessarily adopt the security context of SQL Agent.
Depending on job ownrs etc. it may adopt another security context - obviously in your case there is some combination that is causing it to adopt the SQL Server Service account security context
Is the job and database definitely owned by sa?

Author

Commented:
Yes. Both are owned by SA.
Hi Conor,
Is there a proxy account set up on your SQL Server Agent?
Right-click SQL Server Agent in SSMS, choose Properties and select the Job System page
Hi Conor
Also in your initial post there are 2 accounts mentioned
USERGROUP\sqlservice
and
TESTGROUP\srsqlservice

Are these definitely correct?
Hi Conor
Also in the Job Step properties what are the entries in the Type and Run As fields?

Author

Commented:
Hi there,
In answer to your questions:
1. There is no proxy account set up on the SQL Server agent.
2. There are two accounts. USERGROUP\sqlservice is the account I am logged onto the SSMS (sysadmin account) and it was the account under which I ran the job as a test (but the scheduled job fails also). TESTGROUP\srsqlservice is the account under which the SQL Server service runs.
3. The job does not have a "Run As" entry.
Thanks for the really good suggestions. I look forward to your reply.
Regards,
  Conor
Hi Conor,
In the Job Step properties, is the Type set to T-SQL?

Author

Commented:
Yes it is T-SQL.

The error is saying:

"Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029).  The step failed."

So it seems to be executing it as NT AUTHORITY\SYSTEM but that seems to being translated into trying to log onto the database under the TESTGROUP\srsqlservice account for some reason?

Regards.
Hi Conor,
I set up my SQL Server environment as identical as possible to yours last night, a sqlmaint back job owned by sa, database owned by sa etc.
The sqlmaint utility logged on to SQL Server using the windows profile under which the main MSSQLSERVER service is running under - which I think is exactly what occurs in your case.

My back job did complete successfully - it must be because the MSSQLSERVER service user profile by default had all the necessary permissions.

So I am now thinking that it is just the fact of the matter that the sqlmaint utility will log on to SQL Server with the MSSQLSERVER service  user profile if a user is not explicitly sepcified in the parameters supplied to sqlmaint.
I could not find anything definitive in the sqlmaint documentation, other than if a User is not supplied to SQLMAINT then Windows Authentication is used,

So the Windows Authentication that it appears to use is the Windows user under which the MSSQLSERVER service runs.