Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Unanswered: Push replication between SQL2k and SQL2k

    Hi,

    I am wanting to set up replication between two SQLServer 2000 databases. DB1 has the data, and DB2 is where I would like to replicate to. I have set up the publisher and distributor on DB1, and have set up DB2 as the subscriber. The replication is set up as transactional.

    When the agent runs, it fails with the following error details:
    Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

    Where do I set the user that the agent should be using?

    I think that it is also worth noting that the two computers are not on the same domain. There are no firewalls involved either.

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    I Think it uses the SQL Server Agent Sevice Startup Account security

    Right click on the Agent in EM & Make sure that the account has access to both Servers.

    or

    Go into the server Services and look at the SQL Agent Startup Account

    There are other areas but try this till someone explains it better than I can
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Aha! I found the answer!

    The agent at the distributor is trying to start the agent at the subscriber. The correct permissions must be given on the agent at the subscriber so that the agent at the distributor can execute the app. I am not exactly sure what it requires yet, but this is what I did:

    1) Run the dcomcnfg application from the Run box.
    2) Select the 'Microsoft SQL Server Replication Remote Dist Agent 8.0'
    3) Open the properties of the agent.
    4) Select the Security tab.
    5) Use custom configurations for all settings in this tab. Add the NETWORK group to each configuration group and give the same level of access as the other accounts in the configuration.

    6) Select the Identity tab.
    7) Select 'This user' and provide a local machine user name that has permissions to run this app.

    8) Click OK to save the changes.

    The next problem is that if you are running 2000 with SP3, then the user provided by the agent will not be trusted and will give you another error. Error number 18452 or something similar.

    To make the user trusted, you must execute the xp_sqlagent_proxy_account stored procedure on the subscriber server.

    xp_sqlagent_proxy_account

    {N'GET' | N'SET' | N'DEL ,

    N'agent_domain_name', N'agent_username', N'agent_password'

    }

    You can find more documentation on this stored procedure in MSDN under SQL Server 2000 and Replication Security.

Posting Permissions

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