Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006
    Posts
    8

    Unanswered: SnapshotAgent cant be executed, not sufficient rights

    Hi all,

    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.

    Following Scenario:
    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
    agent.GenerateSnapshot();

    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
    2007-09-12 12:05:46.96

    Configurations:
    -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.
    dbhoop

  2. #2
    Join Date
    Sep 2006
    Posts
    8
    i found sth more out ...

    Following:
    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.

  3. #3
    Join Date
    Sep 2006
    Posts
    8

    Microsoft has a hotfix

    hi,

    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
    Hotfix:
    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

    Will let you know if it works ...

    MariJo

  4. #4
    Join Date
    Sep 2006
    Posts
    8

    Hotfix is solving the problem

    Its working with this hotifx!!!!
    So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official by MS.

    Regards,
    dbhoop

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •