SQL Server Performance

replication security issue

Discussion in 'SQL Server 2005 Replication' started by WingSzeto, Feb 20, 2007.

  1. WingSzeto Member

    I am testing a push transactional replication from SQL 2k5 to SQL 2K. The publishing db and distribution db are on the same computer which has SQL 2k5 installed. The subscriber (SQL 2K) is on a different server.

    I created both publisher and subscriber successfully, the publication and articles are also created successfully. During the publisher and subscriber setup, I use a windows account (as recommended) to connect to the snapshot agent and logreader agent, and publishing/subscrbing db , I make sure the domain account is the dbo for both the publishing db, distribution db and subscriber db, and it belongs to an administrator group so it can write to the local snapshot folder.

    When I start the snapshot agent, it failed and tell me the following:

    "Unable to start execution of step 2 (reason: Error authenticating proxy domainAsqlservice, system error: Logon failure: unknown user name or bad password.). The step failed."

    If I use 'run under the SQL server agent service account' for the authentication instead of windows authentication during the above mentioned setup, the snapshot will start successfully but this setup is recommended based on the security best practice.

    What do I miss while setting up the windows authentication for snapshot agent and logreader agent?

    wingman
  2. MohammedU New Member

    Is there any proxy account setting up for replication agent use?
    Check the following tables for details...

    select * from sys.credentials
    select * from msdb..sysproxies
    select * from msdb..sysproxylogin
    select * from msdb..sysproxysubsystem


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  3. WingSzeto Member

    I didn't set up any proxy account for it. Is it necessary?

    I run your query and found no records on each of the select statements.

    Wingman


    quote:Originally posted by MohammedU

    Is there any proxy account setting up for replication agent use?
    Check the following tables for details...

    select * from sys.credentials
    select * from msdb..sysproxies
    select * from msdb..sysproxylogin
    select * from msdb..sysproxysubsystem


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  4. MohammedU New Member

  5. WingSzeto Member

    I don't want to use a proxy account if not necessary. Is it required for the setup I mentioned in my original email?


    quote:Originally posted by MohammedU

    If you want to use proxy account then you need to setup I believe...

    Replication Proxies, Credentials and Subsystems
    http://www.replicationanswers.com/Security2005.asp


    MohammedU.
    Moderator
    SQL-Server-Performance.com

Share This Page