SQL Server Replication Error

Question

Hi,

I have a SQL Server 2005 box set up for merge replication to SQL Server CE 3.0. The publication, publisher, distributor and IIS have all been set up.

In my application, I attempt to sync the databases using the following code:

//TODO: Change for production
//***************************
string localDBPath = @"C:\Documents and Settings\Robert\Desktop\MyDB.sdf";
//***************************

SqlCeReplication replicator = new SqlCeReplication();
replicator.InternetUrl = "http://myWebServer/sqlcesa30.dll";
replicator.Publisher = "mySqlServer";
replicator.PublisherDatabase = "myDatabase";
replicator.PublisherSecurityMode = SecurityType.NTAuthentication;
replicator.Publication = "myPublication";
replicator.Subscriber = Dns.GetHostName();
replicator.SubscriberConnectionString = @"Data Source=" + localDBPath;

try
{
    // Check if the database file already exists
    if (!System.IO.File.Exists(localDBPath))
    {
        // Add a new subscription and create the local database file
        replicator.AddSubscription(AddOption.CreateDatabase);
    }

    // Transfer the initial snapshot of data if this is the first time this is called.
    // Subsequent calls will transfer only the changes to the data.
    replicator.Synchronize();
}
catch (SqlCeException ex)
{
    // Display any errors in message box
    MessageBox.Show(ex.Message);
}
finally
{
    // Dispose of the SqlCeReplication object, but don't drop the subscription
    replicator.Dispose();
}

Unfortunately, this code fails at the "replicator.Synchronize" line with the following error message:

Failure to connect to SQL Server with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect.

This error message is not very clear to me and I am running out of places to look for the cause of this. Any ideas?

Answer 1

Make sure the agent account is the same user and password on all boxes.

Make sure you are calling the right instances.

Make sure agent is started on all machines involved.

Check the sql event logs on all servers and see which is giving the error, this may also narrow the issue down.

You can also check this to make sure you have setup correctly:

http://msdn.microsoft.com/en-us/library/aa454892.aspx

Same issue here he had to add the instance name correctly:

http://bytes.com/topic/sql-server/answers/611761-merge-replication-error-failure-connect-sql-server-provided-connection

Check these out too:

http://support.microsoft.com/kb/314783

http://support.microsoft.com/kb/319723

http://msdn2.microsoft.com/en-us/library/ms172357.aspx

again make sure you instances are correct: repl.Publisher = "macnine-name\instance-name"

You can also take a look through this blog:

http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx

Answer 2

the message comes from your IIS replication plug-in: //myWebServer/sqlcesa30.dll. When it tries to connect to the publisher, it cannot find it. The publisher is named 'mySqlServer', but apparently it cannot be reached by myWebServer. This can be a name issue (a typo), a firewall issue (SQL port blocked), an IPSEC issue, a configuration issue (SQL is not listenning for remote connections) and so on. Follow normal SQL Server connectivity troubleshooting steps between myWeServer and mySqlServer.