SQL Server Performance

Merge w/ 2000 user (null) error

Discussion in 'Performance Tuning for SQL Server Replication' started by dchorton, Jun 12, 2007.

  1. dchorton New Member

    We have a customer that has 2 sights, both running SQL 2000 using merge replication. Everything was working fine until the subscriber had internet issues. The server was then moved to a different office and connected - but couldn't connect to the main server due to firewall issues. Now, the subscriber is back at the original office and when the replication is started on the distributor, the error:

    Login failed for user '(null)'. Reason: not associated with a trusted
    SQL Server connection.

    I want to change the connection to the other server to be using SQL security and not windows security. I need to change this without effecting the current data.

    They have been working without the systems replicating for a few weeks, so all of the data needs to be replicated, so I need to make it change the security without having to re-setup replication.

    Please help me - this is quite important as it has drug on for so long.

    Thanks

    Darin
  2. satya Moderator

    Confirm that the account that you are logging into the publisher is in the Publication Access List and that the account you use to login to the distributor is in the dbo_role in the distribution database.

    KBAhttp://support.microsoft.com/?id=327825 for informatin.


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. dchorton New Member

    Currently, the connection is using Windows Authentication, so it is administrator which does exist. But, since it isn't working (I can't connect from the distributors Query Analyzer to the subscriber server using windows, but I can using SQL Security), that is why I want to change.

    The KB you sent was:

    New resolution for problems with Kerberos authentication when users belong to many groups

    Is that what you wanted to send?

    Darin
  4. satya Moderator

    Yes this is related (to some extent) for the login failures when multiple servers & replication is involved.

    You said they had problems and revoked the server from there to another place, ensure the network settings including DNS, routers & hubs have not changed to the previous place.

    If the changes are many then you can take help database backup to update the sites, or if few tables involved in the replication then DTS for data transfer.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  5. dchorton New Member

    so you are saying there is no way after initial push setup of the merge replication subscriber to change the connection from windows security to SQL security?

    Darin
  6. dchorton New Member

    satya, I can connect to the subscriber using query analyzer when using the SQL security, but not when using windows security, so I just want to change the existing replication to connect using SQL sercurity and not windows security.

    THanks

    Darin
  7. MohammedU New Member

    You can easily via EM...

    Connect to server/Tools/Replication/Configure Publishing, Subscriber and Distributor/
    Then Click Subscriber tab/You see subscribers list/click "..." against the subscriber you want to change the authentication then modify the authentication...

    I believe it can be done using tsql....


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  8. satya Moderator

    I believe they are not documented and you might get information by running PROFILER at the same time when using via Enterprise Manager.


    http://technet.microsoft.com/en-us/library/ms147827(SQL.90).aspx in SQL 2005 (Don't have any reference for 2000 though) &http://www.codeproject.com/database/MergeReplication.asp talks step by step details.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  9. MohammedU New Member

    Microsoft uses sp_MSupdate_subscriber_info procedure to change the password...
    It updates MSsubscriber_info table in distribution db...

    The following script can be used... I have tested on my test server...

    declare @password sysname, @publisher sysname, @subscriber sysname, @security_mode int, @login sysname
    select @subscriber = 'Sub_mohammedu', @publisher = 'Pub_mohammedu',
    @login = 'test', @password = 'testpwd', @security_mode = 1

    -- select * from MSsubscriber_info

    EXEC master.dbo.xp_repl_encrypt @password OUTPUT

    update MSsubscriber_info
    set login = @login ,
    password = @password ,
    security_mode = @security_mode
    where UPPER(publisher) = UPPER(@publisher)
    and UPPER(subscriber) = UPPER(@subscriber)


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page