PRB: Distribution Agent Fails After You Apply Replication Scripts (312106)
The information in this article applies to:
- Microsoft SQL Server 2000 (all editions)
This article was previously published under Q312106 SYMPTOMS
After you apply a SQL Server replication script that was generated from an existing replication setup, the Distribution Agent cannot connect to the Subscriber. The Distribution Agent indicates the following error message in Enterprise Manager:
The process could not connect to subscriber.
If you view the Agent History Details you may see the following error message:
Error Message:
The process could not connect to Subscriber <subscriber>
Error Details:
Login failed for user 'sa'.
(Source: <subscriber> (Data source); Error number: 18456)
CAUSE
For security reasons, the SQL Server Replication Script Wizard does not write the passwords for SQL logins to the save script, which includes the login information for the distributor and subscribers.
WORKAROUND
When you use Microsoft Windows NT authentication, passwords are not needed and therefore @password = N'' is not included in the SQL scripts.
Before you run a Replication script, edit the Replication script to include the passwords for the distributor and the subscriber logins. Search for "@password =" and then edit the N'' to include the proper password.
For example, change:
@password = N''
-to-
@password = N'mypassword' NOTE: The user name in the example script is "sa"; however, in your script you may use other SQL Server logins.
Sample Replication Script
/****** Begin: Script to be run at Distributor: SKOR ******/<BR/>
/****** Installing the server SKOR as a Distributor. Script Date: 11/5/2001 4:58:00 PM ******/
use master
GO
exec sp_adddistributor @distributor = N'SKOR', @password = N''
/****** Begin: Script to be run at Publisher: SKOR ******/
-- Adding the registered subscriber
exec sp_addsubscriber @subscriber = N'MYSUBSCRIBER', @type = 0, @login = N'sa', @password = N'', @security_mode = 0,
| Modification Type: | Major | Last Reviewed: | 10/3/2003 |
|---|
| Keywords: | kbprb KB312106 |
|---|
|