Knowledge Base

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

Article ID: 320773

Article Last Modified on 10/16/2003

APPLIES TO

Microsoft SQL Server 2000 Standard Edition
Microsoft SQL Server 7.0 Standard Edition

This article was previously published under Q320773

SYMPTOMS

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:

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'

Additionally, you may see the error if the table previously participated in an "immediate updating" subscription.

CAUSE

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.

WORKAROUND

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 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.

MORE INFORMATION

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:

[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'dbname.ownername.sp_MSsync_ins_.....

[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

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)"

Keywords: kbprb KB320773

Microsoft KB Archive/320773

Contents