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

Discussion in 'microsoft.public.sqlserver.ce' started by Ian Ferguson via SQLMonster.com, Feb 3, 2005.

  1. 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
     
    Ian Ferguson via SQLMonster.com, Feb 3, 2005
    #1
    1. Advertisements

  2. 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 N. Yousuf [MS], Feb 4, 2005
    #2
    1. Advertisements

  3. 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
     
    Ian Ferguson via SQLMonster.com, Feb 5, 2005
    #3
  4. Sorry, I didn't answer your question correctly, yes, IIS and MSSQL are
    runnung on the same machine.


    Ian
     
    Ian Ferguson via SQLMonster.com, Feb 5, 2005
    #4
  5. 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 Shaffer, Feb 5, 2005
    #5
  6. 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, Feb 5, 2005
    #6
  7. Ian Ferguson via SQLMonster.com

    Ian Ferguson Guest

    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 Ferguson, Feb 5, 2005
    #7
  8. 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 Shaffer, Feb 6, 2005
    #8
  9. Ian Ferguson via SQLMonster.com

    Ian Ferguson Guest

    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
    ---------------------------------------------------------------
     
    Ian Ferguson, Feb 6, 2005
    #9
  10. Ian Ferguson via SQLMonster.com

    Ian Ferguson Guest

    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 Ferguson, Feb 6, 2005
    #10
  11. 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
     
    Darren Shaffer, Feb 6, 2005
    #11
  12. Ian Ferguson via SQLMonster.com

    Ian Ferguson Guest

    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
    ---------------------------------------------------------------
     
    Ian Ferguson, Feb 6, 2005
    #12
  13. Ian Ferguson via SQLMonster.com

    Ian Ferguson Guest

    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
    ---------------------------------------------------------------
     
    Ian Ferguson, Feb 7, 2005
    #13
  14. 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
     
    Darren Shaffer, Feb 7, 2005
    #14
  15. 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.
     
    Darren Shaffer, Feb 7, 2005
    #15
  16. Ian Ferguson via SQLMonster.com

    Ian Ferguson Guest


    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 Ferguson, Feb 8, 2005
    #16
  17. 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 Shaffer, Feb 8, 2005
    #17
  18. Ian Ferguson via SQLMonster.com

    Ian Ferguson Guest

    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
    ---------------------------------------------------------------
     
    Ian Ferguson, Feb 10, 2005
    #18
  19. Ian Ferguson via SQLMonster.com

    kevinkan Guest

    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
     
    kevinkan, Sep 13, 2005
    #19
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.