none
Sync syntax for named instance Publisher parameters. SQL CE 3.5

    Question

  • We've got an application that we use for syncing with SQL 2008 R2. If you have done anything with SQL Mobile 3.5 it's all pretty much the same. This has been working faultlessly for many years.

    However we've set up some new testing and development environments and decided to use NAMED INSTANCES for SQL server.

    This is where things have gone wrong.

    We have a variable in the connection parameters for "Publisher". Normally the way we configure our services is the Publisher is it's own Distributor. 

    In the case of Named Instance we presume this needs to be <SERVERNAME>\<InstanceName> however we are met with the following on sync.

    ---------------------------
    Synchronization failed
    ---------------------------
    E R R O R   1  of  1
    DESCRIPTION: 'Failure to connect to SQL Server with provided connection information. SQL Server does not exist, access is denied because the SQL user is not a valid user on the SQL Server, or the password is incorrect.[,,,,,]'
    NUMBER: 80004005
    NATIVE_ERROR: 29060
    SOURCE: 'Microsoft SQL Server Compact Edition'
    ---------------------------

    If we revert back to using just the Server name and the default instance all is well.

    I've tried \\ to escape the slash, I've tried a <SERVERNAME>{space}\<InstanceName> suggested in another solution to no avail.

    I may even be misunderstanding this parameter and in fact the Publisher may not be <SERVERNAME>\<InstanceName>.

    Any clues or guidance appreciated.


    • Edited by andyew Monday, February 06, 2017 6:33 AM Typo
    Monday, February 06, 2017 6:32 AM

Answers

  • Can you manually connect to the named instance from the web server, where the SQL CE agent is installed?
    (ie are you 100% sure the credentials and connectivity works)

    Try setting a fixed TCP port for the named instance, and use COMPUTERNAME,portnumber as parameter.


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    • Marked as answer by andyew Tuesday, February 07, 2017 3:39 AM
    Monday, February 06, 2017 11:49 AM
    Moderator
  • Thanks for the reply Erik.

    I was hoping this was more of a syntax issue than a network issue.

    I was indeed able to connect to the SQL instance from the web server but now I'll explain more about what we've got configured.

    I'll explain more of what we've done then I'll explain why. This is not a standard "many instance" SQL install.

    In our case we've assigned many IP addresses to the same server. Each instance having it's own IP address and consequently able to have it's own "default port 1433" per IP address. (see these instructions for configuration)

    We do this to support "GeoServer" open source web mapping applications as it seems does not understand the concept of a SQL named instance syntax <SERVERNAME>\<InstanceName> but does support IP:1433. 

    After following your diagnostic process a lot of things became evident and a few things changed.

    During diagnosis it became clear that the web server connection to the SQL server via SSMS was intermittent to the point of failure. After  a bit of googling the issue was narrowed down to "loose source mapping". What this appears to be was when a remote machine attempted to access a specific instance on the target SQL server and the <SERVERNAME> was used, SQL Server with it's gang of IP addresses ALL RESPONDED on their respective UDP port 1434 (since all the IP addresses are mapped to the same physical server). The remote calling application (SSMS) would drop the response as only one server SHOULD have responded to that request. 

    Pretty obvious one you understand the mechanism.

    Weighing all the needs (and reading a bit more about the abilities of the various packages) we discovered that they all supported at least one of the following connection methods  

    • <SERVERNAME>\<InstanceName>
    • and_or
    • ip.ip.ip.ip,port (note the comma. This is part of the supported syntax)

    To satisfy the requirement we slightly modified our IP per instance and simply moved each instance to it's own fixed port.

    After that normal named instance and ip,port worked for CE sync, SSMS, ASP.Net, Geoserver etc.

    I do realise the whole "ip per instance, default port 1433" is actually redundant based on what we've actually done but I thought I would spell it out in case anyone else finds themself in a similar predicament. 

    It wasn't that the "ip per instance, default port 1433" was wrong but rather the combination of technologies(ASP.Net, Java, SQLCE) we are using needed the fixed port approach.
    • Marked as answer by andyew Tuesday, February 07, 2017 3:39 AM
    • Edited by andyew Tuesday, February 07, 2017 3:43 AM
    Tuesday, February 07, 2017 3:39 AM

All replies

  • Can you manually connect to the named instance from the web server, where the SQL CE agent is installed?
    (ie are you 100% sure the credentials and connectivity works)

    Try setting a fixed TCP port for the named instance, and use COMPUTERNAME,portnumber as parameter.


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    • Marked as answer by andyew Tuesday, February 07, 2017 3:39 AM
    Monday, February 06, 2017 11:49 AM
    Moderator
  • Thanks for the reply Erik.

    I was hoping this was more of a syntax issue than a network issue.

    I was indeed able to connect to the SQL instance from the web server but now I'll explain more about what we've got configured.

    I'll explain more of what we've done then I'll explain why. This is not a standard "many instance" SQL install.

    In our case we've assigned many IP addresses to the same server. Each instance having it's own IP address and consequently able to have it's own "default port 1433" per IP address. (see these instructions for configuration)

    We do this to support "GeoServer" open source web mapping applications as it seems does not understand the concept of a SQL named instance syntax <SERVERNAME>\<InstanceName> but does support IP:1433. 

    After following your diagnostic process a lot of things became evident and a few things changed.

    During diagnosis it became clear that the web server connection to the SQL server via SSMS was intermittent to the point of failure. After  a bit of googling the issue was narrowed down to "loose source mapping". What this appears to be was when a remote machine attempted to access a specific instance on the target SQL server and the <SERVERNAME> was used, SQL Server with it's gang of IP addresses ALL RESPONDED on their respective UDP port 1434 (since all the IP addresses are mapped to the same physical server). The remote calling application (SSMS) would drop the response as only one server SHOULD have responded to that request. 

    Pretty obvious one you understand the mechanism.

    Weighing all the needs (and reading a bit more about the abilities of the various packages) we discovered that they all supported at least one of the following connection methods  

    • <SERVERNAME>\<InstanceName>
    • and_or
    • ip.ip.ip.ip,port (note the comma. This is part of the supported syntax)

    To satisfy the requirement we slightly modified our IP per instance and simply moved each instance to it's own fixed port.

    After that normal named instance and ip,port worked for CE sync, SSMS, ASP.Net, Geoserver etc.

    I do realise the whole "ip per instance, default port 1433" is actually redundant based on what we've actually done but I thought I would spell it out in case anyone else finds themself in a similar predicament. 

    It wasn't that the "ip per instance, default port 1433" was wrong but rather the combination of technologies(ASP.Net, Java, SQLCE) we are using needed the fixed port approach.
    • Marked as answer by andyew Tuesday, February 07, 2017 3:39 AM
    • Edited by andyew Tuesday, February 07, 2017 3:43 AM
    Tuesday, February 07, 2017 3:39 AM