SQL2005 - Snapshot agent won't start

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John F. - 22 Feb 2006 16:18 GMT

I have tried both SQL logins with sa permissions and then windows logins also
with sa permissions to both servers as well and I still get this message and
the Snapshot Agent won't start - any ideas?

Replication-Replication Snapshot Subsystem: agent NPRPROD-NPR-NPR-1 failed.
Unable to start execution of step 2 (reason: JobOwner flowersj doesn't have
permissions to use proxy 1 for subsystem Snapshot). The step failed.

Signature

John F.

Reply to this Message

Raymond Mak [MSFT] - 22 Feb 2006 16:46 GMT

Hi John,

You may want to try the following to grant login 'flowersj' and the Snapshot
subsystem access to proxy id = 1(?):

use msdb
exec sp_grant_login_to_proxy @login_name = N'flowersj', @proxy_id = 1
exec sp_grant_proxy_to_subsystem @proxy_id = 1, @subsystem_name =
N'Snapshot'

If that works, I am sort of curious to know how your system got to the state
that it is currently in. It would be great if you can post some details on
the nature of the flowersj login, whether you are using a remote distributor
(snapshot jobs should be owned by distributor_admin), and which edition of
SQL Server you are using.

Thanks much.

-Raymond

>I have tried both SQL logins with sa permissions and then windows logins
>also
[quoted text clipped - 7 lines]
> have
> permissions to use proxy 1 for subsystem Snapshot). The step failed.

Reply to this Message

John F. - 22 Feb 2006 18:25 GMT

Thanks Raymond - I tried what you suggest above and get this -

'flowersj' is a member of sysadmin server role and cannot be granted to or
revoked from the proxy. Members of sysadmin server role are allowed to use
any proxy.
Msg 14531, Level 16, State 1, Procedure sp_grant_proxy_to_subsystem, Line 48
Permission to access proxy already granted. Verify current permissions
assignments.

all logins Ive used are 'sa' rights - as far as the replication goes its
just a simple snapshot scenario, pull subscription, distributor running on
the publisher, & Im trying to start the snapshot agent to get the initial
sync. We are on the RTM of SQL2005. Both servers in the same domain. Is
there a folder I should share or something? Any help is greatly appreciated
- thanks!

Signature

John F.

> Hi John,
>
[quoted text clipped - 27 lines]
> > have
> > permissions to use proxy 1 for subsystem Snapshot). The step failed.

Reply to this Message

Raymond Mak [MSFT] - 22 Feb 2006 18:41 GMT

This looks really strange, the original error message came from
SQLServerAgent which basically said 'flowersj' doesn't have access to the
proxy account that you have configured for the snapshot agent, yet
subsequent attempts to grant 'flowersj' access to the proxy account resulted
in errors which basically say 'flowersj' already has access (which is as it
should be). Is SQLServerAgent running under a low-privileged account (e.g.
NetworkService)? And are you running Win2K3 or Win2k(which may require
additional OS privilege to be granted to the proxy account)? Of course, if
nothing works, you can always run the snapshot agent executable outside of
SQL Server Agent but I am guessing that you want to have integrated
scheduling support provided by SQLServerAgent.

-Raymond

> Thanks Raymond - I tried what you suggest above and get this -
>
[quoted text clipped - 51 lines]
>> > have
>> > permissions to use proxy 1 for subsystem Snapshot). The step failed.

Reply to this Message

John F. - 22 Feb 2006 21:08 GMT

Raymond - FYI all SQL services are running under the Local System account on
the publisher box. The subscription creates but the snapshot agent still
wont start now with the 'cant get proxy info' error.

Signature

John F.

> This looks really strange, the original error message came from
> SQLServerAgent which basically said 'flowersj' doesn't have access to the
[quoted text clipped - 64 lines]
> >> > have
> >> > permissions to use proxy 1 for subsystem Snapshot). The step failed.

Reply to this Message

Raymond Mak [MSFT] - 22 Feb 2006 22:47 GMT

If you have configured the snapshot agent to run under an explicit domain
proxy account, SQL Server Agent can have many different ways of failing to
get the proxy account info if it is running as LocalSystem. So in the
interest of narrowing down problem to whether this is a proxy account issue,
you may want to try configuring the snapshot agent to run under the same
account as SQLServerAgent (right click publication->properties->Agent
Security->Security Settings... button for snapshot agent->Run under the SQL
ServerAgent service account (This is not a recommended security best
practice) radio button and see if you can start the snapshot agent at all.

-Raymond

> Raymond - FYI all SQL services are running under the Local System account
> on
[quoted text clipped - 86 lines]
>> >> > permissions to use proxy 1 for subsystem Snapshot). The step
>> >> > failed.

Reply to this Message

John F. - 23 Feb 2006 15:40 GMT

still struggling this a.m. with this, Raymond, and I certainly apprecaite
your help. All services on the box in question run under the LocalSystem
account, so I tried to change the Snapshot Agent to use this acct as well.
it still wont start and in looking at all replication job histories I see
this error now - its really strange as the box is both Publisher and
Distributor and the snapshot agent is trying to start on this same box as
well - any ideas? Does the snapshot agent have to run on the publisher? I
wonder if I could do a pull subscription from the subscriber as that box
seems to run smoother with fewer issues. any info or doc on how to setup
Replication in 2005 is greatly appreciated.

2006-02-23 15:22:53.98 Connecting to Distributor 'NPRPROD'
2006-02-23 15:22:54.09 The replication agent had encountered an exception.
2006-02-23 15:22:54.09 Source: Replication
2006-02-23 15:22:54.09 Exception Type:
Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2006-02-23 15:22:54.09 Exception Message: The remote server "NPRPROD" does
not exist, or has not been designated as a valid Publisher, or you may not
have permission to see available Publishers.
2006-02-23 15:22:54.09 Message Code: 14080

Signature

John F.

> If you have configured the snapshot agent to run under an explicit domain
> proxy account, SQL Server Agent can have many different ways of failing to
[quoted text clipped - 97 lines]
> >> >> > permissions to use proxy 1 for subsystem Snapshot). The step
> >> >> > failed.

Reply to this Message

Raymond Mak [MSFT] - 23 Feb 2006 17:41 GMT

The following message certainly looks a lot better as the snapshot agent was
able to run whereas the previous messages you got indicated that
SQLServerAgent wasn't able to start the job at all so progress is being made
albeit in a painfully slow manner. I now suspect that the LocalSystem
account probably doesn't have quite enough privilege to query information
from system catalogue and to verify whether this is the case, you may want
to try using the *SQL Server Configuration Manager (Start->All
Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server
Configuration Manager)* to configure SQLServerAgent to run under a domain
account that you know have relatively high privilege on the
Publisher\Distributor SQL Server instance (can see all rows in sys.servers
for example) and then restart the snapshot agent job again. You can even try
a bit of validation upfront by starting a cmd.exe window under the domain
account that you intend for running SQLServerAgent and then run the snapshot
agent executable (%ProgramFiles%\Microsoft SQL Server\90\COM\snapshot.exe)
manually from there using the command line obtained from msdb..sysjobsteps
(you should be able to find detail instructions on how to run the
replication agent executable by searching this newsgroup). Once you are
satisified that you can run the snapshot agent executable under a specific
account, you can go back and tackle the problem of starting the snapshot
agent job from SQLServerAgent.

If this is indeed an access permission configuration issue as suggested by
the evidence we have so far, running the snapshot agent anywhere else other
than the distributor machine won't help not to mention that this is an
unsupported configuration that will likely result in suboptimal network
bandwidth utilization.

HTH

-Raymond

> still struggling this a.m. with this, Raymond, and I certainly apprecaite
> your help. All services on the box in question run under the LocalSystem
[quoted text clipped - 137 lines]
>> >> >> > permissions to use proxy 1 for subsystem Snapshot). The step
>> >> >> > failed.

Reply to this Message

Wanwen Qi [MSFT] - 23 Feb 2006 22:26 GMT

Hi John,

In case you still stuck with
Replication-Replication Snapshot Subsystem: agent NPRPROD-NPR-NPR-1 failed.
Unable to start execution of step 2 (reason: JobOwner flowersj doesn't have
permissions to use proxy 1 for subsystem Snapshot).

Could you please run the following query and post back the results?
select * from sys.credentials

select * from msdb..sysproxies

select * from msdb..sysproxylogin

select * from msdb..sysproxysubsystem

Thanks

Signature

[ This posting is provided "as is" with no warranties and confers no
rights. ]

Reply to this Message

John F. - 24 Feb 2006 20:06 GMT

I appreciate all the help guys - the agent still wont start, I get the
'Error: 14151, Severity: 18, State: 1.' msg - here is the results of the
query you asked me to run - (only one credential record)
credential_id name

credential_identity

create_date modify_date
-------------
--------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- -----------------------
65536 8A8957B6-6111-4268-B2A5-6E1D8B356162
flowersj

2005-11-23 08:53:22.033 2006-02-10 09:37:20.133

(1 row(s) affected)

proxy_id name
credential_id
enabled description

user_sid

credential_date_created
-----------
--------------------------------------------------------------------------------------------------------------------------------
------------- -------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------

(0 row(s) affected)

proxy_id sid

flags
-----------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------

(0 row(s) affected)

subsystem_id proxy_id
------------ -----------

(0 row(s) affected)

Signature

John F.

> Hi John,
>
[quoted text clipped - 155 lines]
> >> >> >> > permissions to use proxy 1 for subsystem Snapshot). The step
> >> >> >> > failed.

Reply to this Message

Wanwen Qi [MSFT] - 27 Feb 2006 22:11 GMT

John,

The results you sent indicates there are no proxy setting up for replication
agent use. This could because your previous change to configuring the
snapshot agent to run under the same account as SQLServerAgent or something
not set up correctly from the begining.

I would suggest you remove replication, and create replication from
begining, refer to Books online: How to: Create a Publication and Define
Articles (SQL Server Management Studio)

Please post back if you still encounter problems.

Thanks

Wanwen

Signature

[ This posting is provided "as is" with no warranties and confers no
rights. ]

>I appreciate all the help guys - the agent still wont start, I get the
> 'Error: 14151, Severity: 18, State: 1.' msg - here is the results of the
[quoted text clipped - 236 lines]
>> >> >> >> > permissions to use proxy 1 for subsystem Snapshot). The step
>> >> >> >> > failed.

Reply to this Message

John F. - 22 Feb 2006 18:36 GMT

now I'm getting this message, Raymond - I changed it back to the SQL login
adn re-ran your grant permissions -

Unable to start execution of step 2 (reason: Could not get proxy data for
proxy_id = 1). The step failed.

Signature

John F.

> Hi John,
>
[quoted text clipped - 27 lines]
> > have
> > permissions to use proxy 1 for subsystem Snapshot). The step failed.

Reply to this Message