We have a server running SQL Server 2005. There is a job that one of our
DBA's has set up that fails when manually run. This job is set as himself as
the owner (domain user). When the job runs it fails and gives the following
error:

09/12/2008
11:42:52,POSTDBO_HARTE_HANKS_Test,Error,1,SEATTLE, POSTDBO_HARTE_HANKS_Test,POSTDBO_HARTE_HANKS_PROCE SS,,Executed
as user: ROCHESTER\bcorry. Login failed for user 'NT AUTHORITY\ANONYMOUS
LOGON'. [SQLSTATE 28000] (Error 18456). The step
failed.,00:00:00,14,18456,,,,0

So the job is trying to run as NT AUTHORITY\ANONYMOUS LOGON instead of his
domain account.

When I run the job as myself (domain admin) the job executes as the domain
user set to run the SQL Agent service and succeeds, which is what I want his
job to do. I set myself as the owner and it runs as itexec (SQL Agent Service
user). Here is the log from when I run it:

09/12/2008
11:42:42,POSTDBO_HARTE_HANKS_Test,Unknown,1,SEATTL E,POSTDBO_HARTE_HANKS_Test,POSTDBO_HARTE_HANKS_PRO CESS,,Executed
as user: ROCHESTER\itexec. The step succeeded.,00:00:00,0,0,,,,0

When he schedules the job instead of manually running it, the job executes
under different credentials, and this allows it to successfully run.

So basically my question is, how do I make it so that this users jobs
execute as the SQL Agent domain account?

Hi
1)
I have not played with it, but can you try EXECUTE AS .. and run
sp_start_job stored procedure?
2)
You may need configure a SQL Server Agent Proxy first.

Here are the steps:

1. Create a Credential for your domain user.

To create a credential
In Object Explorer, expand Security, right-click Credentials, and then
click New Credential.

In the New Credentials dialog box, in the Credential Name box, type a name
for the credential.

In the Identity box, type the name of the account used for outgoing
connections (when leaving the context of SQL Server). Typically, this will
be a Windows user account. But the identity can be an account of another
type.

In the Password and Confirm password boxes, type the password of the
account specified in the Identity box. If Identity is a Windows user
account, this is the Windows password. The Password can be blank, if no
password is required.

Click OK.

2. Create a SQL Agent Proxy.

To create a proxy account
In Object Explorer, expand a server.

Expand SQL Server Agent.

Right-click Proxies and select New Proxy.

On the General page of the New Proxy Account dialog, specify the proxy
name, credential name, and description for the new proxy. Note that you
must create a credential first before you create a proxy if one is not
already available.

Check the appropriate subsystem for this proxy.

On the Principals page, add or remove logins or roles to grant or remove
access to the proxy account.

3. Now in the Job Step Dialogbox, you could see the Proxy in the dropdown
list of Run As.

"rclark1850" <rclark1850@discussions.microsoft.com> wrote in message
news:826BAC83-375C-4CF0-A132-C1D0DF1F3377@microsoft.com...
> We have a server running SQL Server 2005. There is a job that one of our
> DBA's has set up that fails when manually run. This job is set as himself
> as
> the owner (domain user). When the job runs it fails and gives the
> following
> error:
>
> 09/12/2008
> 11:42:52,POSTDBO_HARTE_HANKS_Test,Error,1,SEATTLE, POSTDBO_HARTE_HANKS_Test,POSTDBO_HARTE_HANKS_PROCE SS,,Executed
> as user: ROCHESTER\bcorry. Login failed for user 'NT AUTHORITY\ANONYMOUS
> LOGON'. [SQLSTATE 28000] (Error 18456). The step
> failed.,00:00:00,14,18456,,,,0
>
> So the job is trying to run as NT AUTHORITY\ANONYMOUS LOGON instead of his
> domain account.
>
> When I run the job as myself (domain admin) the job executes as the domain
> user set to run the SQL Agent service and succeeds, which is what I want
> his
> job to do. I set myself as the owner and it runs as itexec (SQL Agent
> Service
> user). Here is the log from when I run it:
>
> 09/12/2008
> 11:42:42,POSTDBO_HARTE_HANKS_Test,Unknown,1,SEATTL E,POSTDBO_HARTE_HANKS_Test,POSTDBO_HARTE_HANKS_PRO CESS,,Executed
> as user: ROCHESTER\itexec. The step succeeded.,00:00:00,0,0,,,,0
>
> When he schedules the job instead of manually running it, the job executes
> under different credentials, and this allows it to successfully run.
>
> So basically my question is, how do I make it so that this users jobs
> execute as the SQL Agent domain account?

Since it is a Transact-SQL script, I cannot change the run-as account in the
properties of the job. I do have a proxy set up for other jobs that are
ActiveX scripts. Is there a way to do this for a T-SQL job?

"Uri Dimant" wrote:

> Hi
> 1)
> I have not played with it, but can you try EXECUTE AS .. and run
> sp_start_job stored procedure?
> 2)
> You may need configure a SQL Server Agent Proxy first.
>
> Here are the steps:
>
> 1. Create a Credential for your domain user.
>
> To create a credential
> In Object Explorer, expand Security, right-click Credentials, and then
> click New Credential.
>
> In the New Credentials dialog box, in the Credential Name box, type a name
> for the credential.
>
> In the Identity box, type the name of the account used for outgoing
> connections (when leaving the context of SQL Server). Typically, this will
> be a Windows user account. But the identity can be an account of another
> type.
>
> In the Password and Confirm password boxes, type the password of the
> account specified in the Identity box. If Identity is a Windows user
> account, this is the Windows password. The Password can be blank, if no
> password is required.
>
> Click OK.
>
> 2. Create a SQL Agent Proxy.
>
> To create a proxy account
> In Object Explorer, expand a server.
>
> Expand SQL Server Agent.
>
> Right-click Proxies and select New Proxy.
>
> On the General page of the New Proxy Account dialog, specify the proxy
> name, credential name, and description for the new proxy. Note that you
> must create a credential first before you create a proxy if one is not
> already available.
>
> Check the appropriate subsystem for this proxy.
>
> On the Principals page, add or remove logins or roles to grant or remove
> access to the proxy account.
>
> 3. Now in the Job Step Dialogbox, you could see the Proxy in the dropdown
> list of Run As.
>
> "rclark1850" <rclark1850@discussions.microsoft.com> wrote in message
> news:826BAC83-375C-4CF0-A132-C1D0DF1F3377@microsoft.com...
> > We have a server running SQL Server 2005. There is a job that one of our
> > DBA's has set up that fails when manually run. This job is set as himself
> > as
> > the owner (domain user). When the job runs it fails and gives the
> > following
> > error:
> >
> > 09/12/2008
> > 11:42:52,POSTDBO_HARTE_HANKS_Test,Error,1,SEATTLE, POSTDBO_HARTE_HANKS_Test,POSTDBO_HARTE_HANKS_PROCE SS,,Executed
> > as user: ROCHESTER\bcorry. Login failed for user 'NT AUTHORITY\ANONYMOUS
> > LOGON'. [SQLSTATE 28000] (Error 18456). The step
> > failed.,00:00:00,14,18456,,,,0
> >
> > So the job is trying to run as NT AUTHORITY\ANONYMOUS LOGON instead of his
> > domain account.
> >
> > When I run the job as myself (domain admin) the job executes as the domain
> > user set to run the SQL Agent service and succeeds, which is what I want
> > his
> > job to do. I set myself as the owner and it runs as itexec (SQL Agent
> > Service
> > user). Here is the log from when I run it:
> >
> > 09/12/2008
> > 11:42:42,POSTDBO_HARTE_HANKS_Test,Unknown,1,SEATTL E,POSTDBO_HARTE_HANKS_Test,POSTDBO_HARTE_HANKS_PRO CESS,,Executed
> > as user: ROCHESTER\itexec. The step succeeded.,00:00:00,0,0,,,,0
> >
> > When he schedules the job instead of manually running it, the job executes
> > under different credentials, and this allows it to successfully run.
> >
> > So basically my question is, how do I make it so that this users jobs
> > execute as the SQL Agent domain account?
>
>
>