Unanswered: SnapshotAgent cant be executed, not sufficient rights
i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful
2. Creating the publication: executed as User2(db_owner)
2.1 publication.Create(); ... successful
2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with
agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by
And at this point,i got an error message, because the snapshot agent cant be executed ...
2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --------------------------------------
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --------------------------------------
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSq lException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role
This scenario is workin completely fine when i execute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!
Does anybody has any resolutions for this problem?
I appreciate any support.
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.
I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!
and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.
So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.
What do you think? Am i on the right way?
I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.
Microsoft has a hotfix for it and its exactly for my error message ... i didnt try it ... but for sure im going to do it on monday http://support.microsoft.com/kb/938086
A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005