Setup up merge replication - problem with my config of MSSQLServer?

Hi,


I'm a newbie to MSSQLServer and to SQL Server CE, so if the following is a
dumb question, please accept by apologies.


Scenario
========

I'm trying to set up Merge Replication between an application that uses SQL
Server CE running on an IPAQ and MS SQL Server 2000 running under WIN 2K.
The IPAQ is connected to the desktop machine running the MSSQLServer via a
serial port and activesync.

I am programming an application in C# and have been trying to follow the
steps in Chap 17 of "Microsoft .NET Compact Framework" by Andy Wigley and
Stephen Wheelwright

I have used the SQL Server CE Connectivity Manager to configure the use of
anonymous access for HTTP authentication (using the default IUSR_PC-FI5
account).

I have verified the IIS/virtual directory set up by using IE on the IPAQ to
access the sscesa20.dll and I receive the correct "SQL Server CE Server
Agent" message.


Problem
=======

When I attempt to add a subscription (by calling the AddSubscription()
method of an SqlCeReplication object - see code below) a series of three
errors occur:

1) 80045901 - Initialising SQL Server Reconciler has failed: Minor Error
29045
2) 4E74 - The process could not connect to Distributor 'PC-FI5\MSSQLServer'
3) E - Invalid Connection - Source PC-FI5\MSSQLServer



Questions?
=========

1) Any advice please?
2) How can I check that I have configured the Server correctly?
3) How can I check that the IUSR_PC-FI5 account has sufficient
privilage/access to do what it needs to do?
4) What does the error message mean by "the distributor" - the PC, the
instance of the server, something inside the server?
5) Heeeeeeeeeeeeeellllllllllllllllllppppppppp!



Communication between my program on the IPAQ and IIS SEEMS ok, so I suspect
its the process contained in the sscesa20.dll having difficulty talking to
the MSSQLServer. I'm quite happy to set this up with minimum security in
the first instance and progressive add that later on.




Code
====

if (File.Exists ("northwind02.sdf") )
File.Delete ("northwind02.sdf");

SqlCeReplication repl = new SqlCeReplication("http://PC-
FI5/WXAPtest/sscesa20.dll",
"","",
@"PC-FI5\MSSQLServer",
"Northwind",
"NorthwindPublication02",
"Testing",
"Data Source = northwind02.sdf;");

try
{
repl.AddSubscription(AddOption.CreateDatabase);

repl.Synchronize();
}
catch (SqlCeException ex)
{
ShowErrors(ex);
}
}



All advice gratefully received.



Ian

 

The Distributor is the SQL Server.
Are IIS and SQL on the same machine? Check the sscerepl.log in IIS virtual
directory. You may generate a level-3 log by following the steps given in
SQL Server CE Books Online topic: "Troubleshooting/ Connectivity
Troubleshooting/ Enabling Logging by the SQL Server CE Server Agent".

Take a look at this whitepaper and "Multiserver Scenarios" topic:

Security Models and Scenarios for SQL Server 2000 Windows CE Edition 2.0
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlce/htm
l/sqlce_secmodelscen20.asp

Thank you!
Syed N. Yousuf
Microsoft Developer Support Professional

This posting is provided "AS IS" with no warranties, and confers no rights.

 

Syed,


Thanks for that. The MSSQL instance is indeed acting as its own
distributor. I've tried using the level 3 logging - this is the result:


2005/02/05 17:39:53 Thread=BA4 RSCB=11 Command=OPWC Hr=00000000 Total
Compressed bytes in = 234
2005/02/05 17:39:53 Thread=BA4 RSCB=11 Command=OPWC Hr=00000000 Total
Uncompressed bytes in = 332
2005/02/05 17:39:53 Thread=BA4 RSCB=11 Command=OPWC Hr=00000000 Responding
to OpenWrite, total bytes = 234
2005/02/05 17:39:53 Thread=BA4 RSCB=11 Command=OPWC Hr=00000000 C:\Inetpub\
wwwroot\WXAP\B3921100-0BA9-11C5-8000-0EC9B986C88F 0
2005/02/05 17:39:54 Thread=BA0 RSCB=11 Command=SYNC Hr=00000000 Synchronize
entered 0
2005/02/05 17:39:54 Thread=BA0 RSCB=11 Command=SYNC Hr=00000000 Synchronize
responding 0
2005/02/05 17:40:10 Thread=BA0 RSCB=11 Command=SCHK Hr=00000000 The process
could not connect to Distributor 'pc-fi5\MSSQLServer'. 20084
2005/02/05 17:40:10 Thread=BA0 RSCB=11 Command=SCHK Hr=00000000 Invalid
connection. 14
2005/02/05 17:40:13 Thread=BA0 RSCB=11 Command=SCHK Hr=80045901 SyncCheck
responding 0
2005/02/05 17:40:13 Thread=BA0 RSCB=11 Command=SCHK Hr=00000000 Removing
this RSCB 0



I'm afraid this doesn't tell me much more than the original "Invalid
Connection" error. Any suggestions please?



Regards,


Ian

 

Sorry, I didn't answer your question correctly, yes, IIS and MSSQL are
runnung on the same machine.


Ian

 

Please make sure that the default account IUSR_PC-FI5 being used for
anonymous access
is explicitly granted permissions (reader & writer) within SQL Server 2000
on the database that
contains your publications.

You can grant the appropriate permissions to IUSR_PC-FI5 from SQL Enterprise
Manager.

Darren Shaffer
Principal Architect
Connected Innovation

 

Darren,


Thanks for that. The anonymous user is now set up as a login for the
database and I have given the anonymous user SELECT, INSERT, UPDATE and
DELETE permission on the table I am trying to use. In addition, the
anonymous user participates in the public, db_owner, db_datareader,
db_datawriter and MSmerge_large_hex_number roles. Unfortunately, the
problem persists.

I have tried the same with a named login and used both basic and windows
integrated authentication - same result. It is worth noting that if I
(deliberately)use an incorrect password for this, I get the expected
"failed authentication" error. I guess this implies that authentication is
working just fine in all the other cases and something else is causing my
original error?

Is there a "higher level" concept of permission that applies to the whole
database rather than individual tables that I should know about?

Regards,


Ian

 

Ian Ferguson via SQLMonster.com wrote:

Hey! I've just realised I can access this via usenet! Much better!


--
---------------------------------------------------------------
Dr Ian Ferguson University of Strathclyde
Lecturer Department of Computer and Information Science
Livingstone Tower
Room : 10-18 26, Richmond Street
Email: ian.fergusonATcis.strath.ac.uk Glasgow
Web : www.cis.strath.ac.uk/~if G1 1XH
Tel : +44 (0)141 548 3328 UK
Fax : +44 (0)141 552 5330
---------------------------------------------------------------

 

Ian,

It sounds like you're able to hit sscesa20.dll from Pocket IE, you're using
anonymous
access to IIS, and the machine default user has permissions on the database
and tables. The only
other difficulty many people run into is a mismatch between service pack
levels on
the SQL CE Server tools and SQL Server itself. If you're running SP2 of SQL
Server
2000, be sure you are running SP2 of the server tools. If you're running
SQL Server 2000 SP3,
be sure to get the SP3 server tools as well. Merge Repl is picky in this
regard. I helped one
gentleman who had them both matched at SP3 and couldn't get it to work, and
when he reverted
to SP2 & SP2, things started working for him.

Some other things to try to bracket-in on the problem:

Re-initialize your publication, then try to replicate on the device
Drop and re-initialize your SQL CE database (assuming you have the DDL to
recreate it easily)
One obvious question - your machine that's running IIS and SQL
Server....it's NTFS right?

Darren Shaffer
Principal Architect
Connected Innovation

 

Darren,

Thanks for detailed reply.


1) I'll check the service packs, but I *think* everything is SP3

2) No - I'm running on a FAT32 filesystem. The project is intended to
be deployed on a system that uses WIN2K on a FAT32 filesystem. Is this
likley to cause problems? I know there are security issues with this
approach (which we intended to handle through a variety of approaches)
but from reading various documents I thought merge/replication would
work fine on FAT32.

3) I'll try re-initialising and see what happens.


Regards,


Ian

--
---------------------------------------------------------------
Dr Ian Ferguson University of Strathclyde
Lecturer Department of Computer and Information Science
Livingstone Tower
Room : 10-18 26, Richmond Street
Email: Glasgow
Web : www.cis.strath.ac.uk/~if G1 1XH
Tel : +44 (0)141 548 3328 UK
Fax : +44 (0)141 552 5330
---------------------------------------------------------------

 

Yep, everything patched up to date (SP3). I've tried seting up new
copies of the database, new publications, new snapshot. No joy - the
same problem keeps re-appearing.



I'd like to try and track what actually happens on the server when I
execute the code, but I don't know what tools are available to monitor
connections to distributors/ publishers etc. Can you steer me in the
right direction? I feel like I'm asking dumb questions here, but MSSQL
is a new environment for me. Now if it was ORACLE, I'd know where to
start....



Regards,

Ian



--
---------------------------------------------------------------
Dr Ian Ferguson University of Strathclyde
Lecturer Department of Computer and Information Science
Livingstone Tower
Room : 10-18 26, Richmond Street
Email: Glasgow
Web : www.cis.strath.ac.uk/~if G1 1XH
Tel : +44 (0)141 548 3328 UK
Fax : +44 (0)141 552 5330
---------------------------------------------------------------

 

Ian - the bad NTFS is required for Merge Replication for two reasons:

1. to secure the virtual directory
2. to share and secure the snapshot folder.

You can convert a FAT32 filesystem to NTFS easily using the built-in utility
that comes with
Windows XP.

Darren Shaffer
Principal Architect
Connected Innovation

 

I'll change over to NTFS and get back to you!




Ian

--
---------------------------------------------------------------
Dr Ian Ferguson University of Strathclyde
Lecturer Department of Computer and Information Science
Livingstone Tower
Room : 10-18 26, Richmond Street
Email: Glasgow
Web : www.cis.strath.ac.uk/~if G1 1XH
Tel : +44 (0)141 548 3328 UK
Fax : +44 (0)141 552 5330
---------------------------------------------------------------

 

Darren,

NTFS has made no difference I'm afraid. THe conversion wenrt faultlesly
and securing and set up of directories was no problem.

I'm afraid I'm out of ideas. I can implement something using XML but
that would be a shame as merge replication is exactly what I needed.



Ian

--
---------------------------------------------------------------
Dr Ian Ferguson University of Strathclyde
Lecturer Department of Computer and Information Science
Livingstone Tower
Room : 10-18 26, Richmond Street
Email: Glasgow
Web : www.cis.strath.ac.uk/~if G1 1XH
Tel : +44 (0)141 548 3328 UK
Fax : +44 (0)141 552 5330
---------------------------------------------------------------

 

Well Ian, I'm not going to leave you hanging after all this ;-) merge
really does work well, it's
just a lot of little things to get it working that first time.

Since you are on SP3 of SQL Server 2000 and the SQL CE Server Tools, this
hotfix may
apply:

http://support.microsoft.com/default.aspx?scid=kb;en-us;811188

Darren Shaffer
Principal Architect
Connected Innovation

 

I've also seen people revert to SP2 on both the SQL Server and the server
tools and that worked,
although I'm not sure it's the SP level as much as they went back through
the entire install again and
got it right that time.

 

Much appreciated! I'll try the hotfix. If that doesn't work, I'm going
to uninstall on both the server and the IPAQ and start again with a
clean installation. This might take a day or so - I'll let you know how
I get on.


Thanks again,


Ian







--
---------------------------------------------------------------
Dr Ian Ferguson University of Strathclyde
Lecturer Department of Computer and Information Science
Livingstone Tower
Room : 10-18 26, Richmond Street
Email: Glasgow
Web : www.cis.strath.ac.uk/~if G1 1XH
Tel : +44 (0)141 548 3328 UK
Fax : +44 (0)141 552 5330
---------------------------------------------------------------

 

Ian,

If you do uninstall and reinstall everything, stop at service pack 2 and see
how things
work at that level before applying sp3/3a.

Thanks,

Darren Shaffer
Principal Architect
Connected Innovation

 

Darren,



You're not going to like this...



The result after reinstallation is exactly the same at SP2 level, SP3
and SP3a. I've missed something fundamental here and I just can't see it.



Ian




--
---------------------------------------------------------------
Dr Ian Ferguson University of Strathclyde
Lecturer Department of Computer and Information Science
Livingstone Tower
Room : 10-18 26, Richmond Street
Email: Glasgow
Web : www.cis.strath.ac.uk/~if G1 1XH
Tel : +44 (0)141 548 3328 UK
Fax : +44 (0)141 552 5330
---------------------------------------------------------------

 

Hi Ian

I'm juz wondering if u've managed to resolve yor problem?

Coz if u do, is it possible to share the resolution as I'm having th
exact same errors as u do... Tried every solutions as per discusse
in this thread, yet to no avail...

Thanks!

MS SQL SERVER CE is driving me nuts!! :shock