SQL Server Performance

Access Denied error for Snapshot repl

Discussion in 'Performance Tuning for SQL Server Replication' started by vn, Jul 28, 2004.

  1. vn New Member

    hello - I am trying to get a copy of the database on another SQL Server. So, I created a snapshot replication and ran the snapshot agent. When I try to run the distribution agent I get a access denied to Subscriber error and I do not know how to correct it. Can someone give me pointers to solve this issue?
  2. satya Moderator

  3. vn New Member

    Thank you for your help. I guess I could have done it as a backup restore operation, but I am interested in finding out why the replication is not working.
    I have a push subscription.

    The error message says:
    The process could not connect to Subscriber 'Subscriber_Name'.

    The error details say this:
    SQL Server does not exist or access denied.
    (Source: Subscriber_Name (Data source); Error number: 17).

    I checked all the settings as provided by you. The SQL Server agent runs on an account that is a member of the local administrators group on both the publisher and the subscriber. Also, the account is a member of the domain users list.

    Not sure what I am doing wrong.
    Thanks for the help again.
  4. satya Moderator

    Is this an enabled subscriber?
    Go to tools, replication, configure publishers, subscribers, distributor and in the Subscribers tab, ensure your subscriber is there and is checked. Then click on the browse button and make
    sure you are using the impersonate the SQL Server Agent on the Publisher option.

    Also from your subscriber can you ping your publisher by host name and ipaddress.



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. vn New Member

    Yes. It is an enabled subscriber. And, the Impersonate SQL Server Agent on the Publisher Option is also checked. I can also ping the publisher from the subscriber by IP address and by host name. Thank you for your help. But, the replication is not working. Also, I thought of why I cannot backup and restore db. I need only the data. I do not want to overwrite the stored procedures etc.
    Thank you.
  6. satya Moderator

    Then why are you taking overhead of these issues by using Replication, to export DATA only between the source and target databases then you can use DTS export Wizard to accomplish the task and no other objects in the database will be touched.

    Refer to the books online for DTS information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. xpthinker New Member

    This is just a note:

    Try to inspect the versions of SQL Server running on Publisher and Subscirber. I recently find out this issue when I installed the latest SP3 on publisher, security issues has been raised. So I decided to patch the subscriber machine.

    xpthinker
    IT Professional, MCP
  8. satya Moderator

    In general Service packs are meant to apply on all the SQL servers including client machines for bug fixes.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. BruceA New Member

    Did anyone come up with a reason for this??
    I have SP3 on Publisher and Subscriber and need Replication.
    Pull and Push subscriptions show this error, but I am able to run a second replication in the reverse direction (ie the publisher becomes the scriber), and I can run isql both directions and see both the databases in Enterprise manager on both sites.
    Regards
    Bruce
  10. satya Moderator

    Confim the SQLAgent privileges on both the machines.
    Refer to sQL error log for any other information during this behaviour.

    PROFILER trace may help to assess the issue.
    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page