none
sp_addsubscription: "sync_type" issue on SQL Server 2005

    Question

  • Hello,

    I am seeing some unexpected malfunctioning of the sp_addsubscription procedure when I try to setup the bidirectional replication between 2 servers running SQL Server 2005:

    1. When I generate the subscription through the wizards the sp_addsubscription call is issued with sync_type = 'automatic' which implies a snapshot to be taken before the replication can start. The subscription seems to be running ok in such case, just that it waits for the initial snapshot.

    2. If I try to change the sync_type to 'none' (which is the same as 'replication support only') which actually means that only data updates are being sent and no initial snapshot is required, the sp_addsubscription procedure fails with:

    The process could not connect to Subscriber 'LL-FFF-DDD'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20084)
    Get help: http://help/MSSQL_REPL20084

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Source: MSSQLServer, Error number: 18456)
    Get help: http://help/18456

    Am I missing something in this parameter configuration [change] or is a hotfix that needs to be applied?

    Many thanks,

    Dan

     


    Friday, April 01, 2011 2:11 PM

Answers

  • Hi,

    It is a login failed error from the error message, could you please check whther you grant appropriate permissions to each Replication Agent according to http://msdn.microsoft.com/en-us/library/ms151868(v=SQL.90).aspx.

    For further investigation, please provide the following information which would be helpful to troubleshoot the issue:

    • How you configure Replication, Publisher, Distributor and Subscriber, and SELECT @@VERSION of each server.
    • How your create publication as well as suscriptions in steps (T-SQL scripts).

    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Dan Sichitiu Tuesday, April 12, 2011 1:41 PM
    Tuesday, April 05, 2011 7:12 AM

All replies

  • Hi,

    It is a login failed error from the error message, could you please check whther you grant appropriate permissions to each Replication Agent according to http://msdn.microsoft.com/en-us/library/ms151868(v=SQL.90).aspx.

    For further investigation, please provide the following information which would be helpful to troubleshoot the issue:

    • How you configure Replication, Publisher, Distributor and Subscriber, and SELECT @@VERSION of each server.
    • How your create publication as well as suscriptions in steps (T-SQL scripts).

    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Dan Sichitiu Tuesday, April 12, 2011 1:41 PM
    Tuesday, April 05, 2011 7:12 AM
  • Hi,

    The SELECT @@VERSION returns (on both servers):

    Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb  9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    The whole setup is based on this document:

    http://msdn.microsoft.com/en-US/library/ms147929(v=SQL.90).aspx

    Detailed T-SQL scripting is posted below (commands are commented out because it was a gradual step-by-step testing process):

    -- *** detect if there is any distribution db installed

    -- use master;

    -- exec sp_get_distributor

    -- Is the current server a Distributor?

    -- select is_distributor from sys.servers where name='repl_distributor' and data_source=@@servername

    -- Which databases on the Distributor are distribution databases?

    -- select name from sys.databases where is_distributor = 1

    -- detect the distributor and distribution db parameters:

    -- What are the Distributor and distribution database properties?

    -- exec sp_helpdistributor

    -- *** detect the distribution db

    -- exec sp_helpdistributiondb

    -- exec sp_helpdistpublisher

    -- detect all publications on each target database (to be replicated):

    -- use itest

    -- exec sp_helppublication -- @publication = '%'

    -- set replication db option:

    -- exec sp_replicationdboption @dbname = 'itest', @optname = publish, @value = true

    -- add the publication for itest

    -- use itest

    -- exec sp_helppublication -- @publication = '%'

    -- use itest

    -- exec sp_addpublication @publication = 'itest'

    -- Which databases are published for replication and what type of replication?

    -- exec sp_helpreplicationdboption

     

    -- use itest

    -- exec sp_addarticle @publication = 'itest', @article = 'table1',  @source_object = 'table1', @destination_table = 'table1', @schema_option = 0x34E88, @ins_cmd = 'SQL', @upd_cmd = 'SQL', @del_cmd = 'SQL'

    -- exec sp_addarticle @publication = 'itest', @article = 'table2', @source_object = 'table2', @destination_table = 'table2',  @schema_option = 0x34E88, @ins_cmd = 'SQL', @upd_cmd = 'SQL', @del_cmd = 'SQL'

    -- ...

    -- exec sp_addarticle @publication = 'itest', @article = 'tableN', @source_object = 'tableN', @destination_table = 'tableN',  @schema_option = 0x34E88, @ins_cmd = 'SQL', @upd_cmd = 'SQL', @del_cmd = 'SQL'

    -- *** List all subscriptions to a specific publication

    -- use isoft

    -- exec sp_helpsubscription

    -- *** Adding subscriptions require making the publication active

    -- use itest

    -- exec sp_changepublication @publication = 'itest', @property = 'status', @value = 'active'

    -- *** remove "Enforce pwd policy" for SQL user 'itest'  -- !!! TBD, as may not be required!

    -- *** Granting publication access for selected users

     

    use itest
     

    -- exec sp_grant_publication_access @publication = N'itest', @login = N'sa'

    -- exec sp_grant_publication_access @publication = N'itest', @login = N'NT AUTHORITY\SYSTEM'

    -- //NO! exec sp_grant_publication_access @publication = N'itest', @login = N'NT AUTHORITY\ANONYMOUS LOGON'

    -- exec sp_grant_publication_access @publication = N'itest', @login = N'BUILTIN\Administrators'

    -- exec sp_grant_publication_access @publication = N'itest', @login = N'distributor_admin'

    -- exec sp_grant_publication_access @publication = N'itest', @login = N'itest'

    -- use itest

    -- !!! THIS IS WHERE THE ISSUE APPEARS

    -- first statement is how it needs to work, and it doesn't (sync_type is not 'accepted')

     

    -- exec sp_addsubscription @publication = 'itest', @subscriber = 'LL-FFF-DDD', @destination_db = 'itest', @sync_type = 'replication support only', @status = ACTIVE, @loopback_detection = 'TRUE'

    -- second statement is how the wizard generates it, and is not erroring out (sync_type is the only change)!

     

    -- exec sp_addsubscription @publication = N'itest', @subscriber = N'LL-FFF-DDD', @destination_db = N'itest', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0

    -- *** use dropsubscription if the subscription gets disabled

    -- re-issue addsubscription after that

    -- exec sp_dropsubscription @publication = 'itest', @article = 'all', @subscriber = 'LL-FFF-DDD'

    -- *** enable replication

    -- use itest

    -- exec sp_addpushsubscription_agent @publication = 'itest', @subscriber = 'LL-FFF-DDD', @subscriber_db = 'itest'

    -- the below call is generated by the wizard, practicaly identical to the above

    -- exec sp_addpushsubscription_agent @publication = N'itest', @subscriber = N'LL-FFF-DDD', @subscriber_db = N'itest', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20110331, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'

    -- !!! END OF T-SQL SCRIPT !!!

    I'll have a look at the permissions for each Replication Agent and post my findings.

    Regards,

    Dan

     

    Tuesday, April 05, 2011 5:30 PM
  • The sync_type by itseld should not have any impact on the authentication.

    Are you sure you creating a push subscriber in both cases ?

     

    The scripts provided were not the scripts generated by SSMS and are not so easy to follow since all values are defaults.

     

    If you do not want to work with snapshot agent you may also want to assure that sp_addpublication has a value of false for @immediate_sync.

    Be sure to mark all objects as NFR (NOT FOR REPLICATION) at all nodes.


    -- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi
    Tuesday, April 05, 2011 6:47 PM
  • For some reason, changing the SQL Agent running account has fixed the issue.

    Thank you for feedback.

    Regards,

    Dan

    Wednesday, April 13, 2011 1:57 PM
  • If you change the passwords,u need to restart those agents
    Wednesday, April 13, 2011 4:09 PM