Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2010
    Posts
    3

    Unanswered: SQL Server 2005 can't remove the publication - error: (Microsoft SQL Server, Error: 1

    Hi all,
    I have a problem with the replication in place in my company.
    the context:
    1. 2 servers in a private dmz accessible from the Internet, no domain, so no windows authentification, only SQL authentification.
    2. SQL Server 2005 SP3 - build 9.00.4053.00 - on win 2003 SP2 running on both server with transactional replication.
    the problem:
    1. for some reason, the replication didn't work anymore, here was the error:
    connection failed for user <MyUser>
    the user was correct and we didn't change the password.
    2. So I change the security context and the user in the linked server repl_distributor properties in both server. Now, the problem is that the replication got the error:
    The process could not execute 'sp_repldone/sp_replcounters' on <Distributor/subscriber>.
    3. so, I tried to remove the publication, but I it came with this error:
    Could not connect to server <distributor/subscriber> because <MyUser> is not defined as a remote login at the server. Verify that you have specified the correct login name. .

    I can't remove the linked server either:
    Cannot drop server 'repl_distributor' because it is used as a Distributor in replication. (Microsoft SQL Server, Error: 20581)

    has anyone encountered this problem? any suggestion on how to resolve this issue would be much appreiate.

    thanks for your help

    Nandini

  2. #2
    Join Date
    Dec 2010
    Posts
    3

    SQL Server 2005 can't remove the publication - error: (Microsoft SQL Server, Error: 1

    Hi,

    this can be useful to someone, I finally fixed my problem by changing the replication password by right clicking in replication folder and then update Replication password and then sorted it out by disabling the dustributor, and then the publisher.

    cheers
    Nandini

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You could have done a similar thing in the first place, when you changed the user, and consequently the password. In your original post you said the password was not changed, but you didn't mention if that was confirmed. You should have looked for Error: 18456, Severity: 14, State: 8, which means wrong password.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Dec 2010
    Posts
    3

    SQL Server 2005 can't remove the publication - error: (Microsoft SQL Server, Error: 1

    hi rdjabarov,

    I am not sure I got your point...

    I am sorry I didn't make it clear, but effectively the password of user used in the replication didn't changed. what have changed is the property of the repl_distributor server linked object. this object is created when the distributor and the publisher are configured and you shouldn't change it manually, otherwise it means big troubles...
    so what I did is that I change the replication password by right clicking in replication folder in SSMS and then update Replication password. this doesn't change the user password but reset it in the linked server

    hope this makes sense to you...

    cheers
    Nandini

Posting Permissions

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