PRB: Changes to a Table That Is Part of an "Immediate Updating" Subscription Generates an Error Message

If you try to use an INSERT, UPDATE, or DELETE statement on data in a table that is part of an "immediate updating" subscription, you may receive the following error message: Additionally, you may see the error if the table previously participated in an "immediate updating" subscription.

You receive the error message because the System Administrator (SA) logon account does not have a blank password.

When you configure an immediate updating subscriber, the replication setup process tries to connect to the publisher with a dynamic remote procedure call (RPC) to create the synchronization triggers on the subscribing table. The dynamic RPC connection defaults to the System Administrator (SA) logon, which is blank. A blank password is used so that passwords and logons are not sent over the network.

If you remove the original publication, the synchronization triggers may be left intact on the subscriber.

To work around the issue, use one of the following methods based on your circumstances:

Scenario 1: The Publication Still Exists

• If the publishing computer on which SQL Server is running is set to Microsoft Windows NT only security mode:
  
  
  1. On the subscriber, run the sp_helpserver stored procedure in the master database.
     
     NOTE: This requires that the linked server or a remote server is configured for the publisher at the subscriber.
  2. If the returned list contains an entry for the publisher, run the following statement at the subscriber in the master database:

     sp_addlinkedsrvlogin @rmtsrvname = 'Publishing Server' , @useself =  TRUE  , @locallogin =  NULL  
     								

     
     -Or-
     
     If the sp_helpserver stored procedure does not return any rows, run the following statements at the subscriber in the master database:

     sp_addlinkedserver  @server =  'Publishing Server'

     sp_addlinkedsrvlogin @rmtsrvname =  'Publishing Server' , @useself = TRUE  , @locallogin = NULL
     							

  3. Run the following statement in the subscribing database after you configure the immediate updating subscription:

     sp_link_publication @publisher = 'publisher', @publisher_db = 'publication database', @publication = 'publication name', @security_mode = 2, @login = NULL, @password = NULL,@distributor = 'distributor'
     							

  -Or-
• If the publishing computer on which SQL Server is running is set to Mixed-Mode security mode, and you want to use SQL Server Authentication mode:
  1. Run the sp_link_publication stored procedure on the subscribing database to specify the SA password for the publisher.
     

     sp_link_publication @publisher = 'publisher', @publisher_db = 'publication database', @publication = 'publication name', @security_mode = 0, @login = 'sa', @password = 'yoursapassword'  @distributor = 'distributor'

     NOTE: You must set an SA password to avoid exposing a potential security hole.For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
     313418  (http://support.microsoft.com/kb/313418/EN-US/ ) PRB: Unsecured SQL Server with Blank (NULL) SA Password Leaves Vulnerability to a Worm

Scenario 2: The Publication No Longer Exists

In SQL Server Enterprise Manager, right-click the table, and then click Manage triggers to drop the triggers that were created by replication for INSERT, UPDATE, and DELETE.

These triggers start with trg_MSsync_del, trg_MSsync_upd, and trg_MSsync_ins for the INSERT, UPDATE, and DELETE triggers respectively, followed by the name of the table. Additionally, you can drop the triggers with ALTER TABLE statements on the subscriber tables.

If you ran the sp_link_publication stored procedure from the subscriber, and the removal process was incomplete, the inserts at the subscriber do not succeed and you receive the following error message: You receive similar error messages for updates and deletes. To work around this problem, delete the triggers manually in SQL Server Enterprise Manager or by using ALTER TABLE statements on the subscriber table.

For more information, see the following topics in SQL Server Books Online:

SQL Server 2000

• "Immediate Updating Considerations"
• "sp_link_publication"
• "Security and Replication Options"

SQL Server 7.0

• "Administration of Immediate-updating Subscribers"
• "Security for Immediate-updating Subscribers"
• "sp_link_publication (T-SQL)"