sqlmaint failed due to permissions on remote directory 22029 error

7 Nov 2007 6:09 PM

Blue Sky

I have a job (owned by "cod\SqlAgent" which fails running the following
statement:

EXECUTE master.dbo.xp_sqlmaint N'-D msdb -WriteHistory -CkDB
-BkUpOnlyIfClean -VrfyBackup -BkUpMedia DISK -BkUpDB
\\itdsdgprd01\Backup\SQLCA -DelBkUps 23HOURS -CrBkSubDir -BkExt "BAK"'

The job succeeds when i assign appropriate read/write perms to the account
running the SQL Server (cod\changer).

The SqlService runs under account cod\changer
The Sql Agent runs under account cod\SqlAgent

When i run the sql via QueryAnalyzer (logged in as 'cod\steve', the 3rd &
4th Result lines say:
Logged on to SQL Server 'SQLCA' as 'COD\changer' (trusted)

My question is this:
" Is it correct to say that SQLMAINT executes with the permissions of the
user account running the SQL SERVICE?"

The correlated question is, "Why doesn't this job run with the
authority/permissions of the owner of the job ('cod\sqlAgent') or the
authority/permissions of the account running the Sql Agent... (also
cod\SqlAgent)?

Thanks for any insight.

--
The Spirit gives life; the flesh counts for nothing! (Jn 6:63)

7 Nov 2007 6:26 PM

Tibor Karaszi

> " Is it correct to say that SQLMAINT executes with the permissions of the
> user account running the SQL SERVICE?"

Correct.

> The correlated question is, "Why doesn't this job run with the
> authority/permissions of the owner of the job ('cod\sqlAgent') or the
> authority/permissions of the account running the Sql Agent... (also
> cod\SqlAgent)?

It is all about architecture. Agent logs on to SQL Server (remember the job step is TSQL) and ask
SQL Server to execute the xp_sqlmaint command. So, SQL Server executes xp_sqlmaint (and not Agent).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Show quote

"Blue Sky" <Blue***@discussions.microsoft.com> wrote in message
news:8BF8B86D-A4DD-4838-9D41-E6443ECB37D4@microsoft.com...
>I have a job (owned by "cod\SqlAgent" which fails running the following
> statement:
>
> EXECUTE master.dbo.xp_sqlmaint N'-D msdb -WriteHistory -CkDB
> -BkUpOnlyIfClean -VrfyBackup -BkUpMedia DISK -BkUpDB
> \\itdsdgprd01\Backup\SQLCA -DelBkUps 23HOURS -CrBkSubDir -BkExt "BAK"'
>
> The job succeeds when i assign appropriate read/write perms to the account
> running the SQL Server (cod\changer).
>
> The SqlService runs under account cod\changer
> The Sql Agent runs under account cod\SqlAgent
>
> When i run the sql via QueryAnalyzer (logged in as 'cod\steve', the 3rd &
> 4th Result lines say:
> Logged on to SQL Server 'SQLCA' as 'COD\changer' (trusted)
>
>
> My question is this:
> " Is it correct to say that SQLMAINT executes with the permissions of the
> user account running the SQL SERVICE?"
>
> The correlated question is, "Why doesn't this job run with the
> authority/permissions of the owner of the job ('cod\sqlAgent') or the
> authority/permissions of the account running the Sql Agent... (also
> cod\SqlAgent)?
>
> Thanks for any insight.
> --
> The Spirit gives life; the flesh counts for nothing! (Jn 6:63)

7 Nov 2007 7:50 PM

Geoff N. Hiten

Regardless of who issues the command, all backups run from the context of
the SQL Engine, thus the SQL Server Service account's permissions are what
counts.

--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP

Show quote

"Blue Sky" <Blue***@discussions.microsoft.com> wrote in message
news:8BF8B86D-A4DD-4838-9D41-E6443ECB37D4@microsoft.com...
>I have a job (owned by "cod\SqlAgent" which fails running the following
> statement:
>
> EXECUTE master.dbo.xp_sqlmaint N'-D msdb -WriteHistory -CkDB
> -BkUpOnlyIfClean -VrfyBackup -BkUpMedia DISK -BkUpDB
> \\itdsdgprd01\Backup\SQLCA -DelBkUps 23HOURS -CrBkSubDir -BkExt "BAK"'
>
> The job succeeds when i assign appropriate read/write perms to the account
> running the SQL Server (cod\changer).
>
> The SqlService runs under account cod\changer
> The Sql Agent runs under account cod\SqlAgent
>
> When i run the sql via QueryAnalyzer (logged in as 'cod\steve', the 3rd &
> 4th Result lines say:
> Logged on to SQL Server 'SQLCA' as 'COD\changer' (trusted)
>
>
> My question is this:
> " Is it correct to say that SQLMAINT executes with the permissions of the
> user account running the SQL SERVICE?"
>
> The correlated question is, "Why doesn't this job run with the
> authority/permissions of the owner of the job ('cod\sqlAgent') or the
> authority/permissions of the account running the Sql Agent... (also
> cod\SqlAgent)?
>
> Thanks for any insight.
> --
> The Spirit gives life; the flesh counts for nothing! (Jn 6:63)