locked
Sync for Public users RRS feed

  • Question

  • Ok, here is the scenario.

    We have an existing application that is available to the public (for purchase) which uses a SQL Compact database. I just managed to figure out how that whole thing works, pretty excited right now.

    One of the reasons we are looking at using Sync Framework 2.1 is because we are interested in expanding our existing WPF application to have a Silverlight (Online) application that the user can use when they do not have access to the installed WPF application (say a friends house). The WPF application must run in both online and offline environments. Because the Compact database would need to be available both on the users computer and within our datacenter in order for the Silverlight application to work we are planning to use Sync Framework to sync between the local Compact database and a database here which uses SQL Server 2008 R2.

    We will probably look at dumping all the data from the Compact database from all users into a table and seperate the user's data on the server end with filters (User ID column in the server-side table) that identify the user. So only the specific user data is synced and they can't get to other users' data). The question here is, how to expose the credential data securely within the application, but in a way the user cannot access it (Since we will have a single database login for all sync traffic). One idea was to do the sync processing server-side. (WCF upload the compact database file to the server, have the server process the sync and send the newly sync'ed compact database back to the client).

    But that way doesn't seem very efficient. Whats the best way to handle this? I was hoping for a case where the credentials are never handled by the client application but I'm not sure how the Client computer would sync to the database over the internet without the credentials.


    Owner, Quilnet Solutions
    Tuesday, January 25, 2011 1:00 AM

Answers

  •  

    by Ntier, I refer to one of the most common architectures to implement sync solutions. this link has details of it: http://msdn.microsoft.com/en-us/library/bb902829(v=SQL.110).aspx

    also you can refer to the secuirty section on the BOL at http://msdn.microsoft.com/en-us/library/bb726013(v=SQL.110).aspx for further consideration to design a secution sync solutions using sync framework.

     

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Quilnux Thursday, January 27, 2011 10:41 PM
    Thursday, January 27, 2011 10:32 PM
  • how about using Certificate based authentication on WCF coupled with a client GUID?

    the certificate can be used to authenticate that the client is allowed to consume the WCF service, when the client is authenticated, you then use the client GUID to look up the corresponding connection information on a database on the server side.

    • Marked as answer by Quilnux Thursday, January 27, 2011 11:48 PM
    Thursday, January 27, 2011 2:29 PM
  • both JuneT's suggestion and your way to handle this can help here in terms of security. I want to mention one this before we close this thread. security should be taken care of in the sync application ( as in this case ), all the options we discussed here were used at some level, to name a few below:

    1. filter data to security the data

    2. encryption senstitve info such as credentials 

    3. Ntier configuration to hide the server provider at the IIS box and use security features provided by the transport

    4. sync the entire db at the server and push to client the .sdf files.

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Yunwen Bai Thursday, January 27, 2011 8:04 PM
    Thursday, January 27, 2011 8:03 PM

All replies

  • can we clarify one thing : afer the change and moved to sync framework, will the users on the client side still share the SAME compact database which is sync-ed with the backend SqlServer ( has all user data but can be filtered with the ID ) ?

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, January 25, 2011 2:36 AM
  • No,

    Each user has their own compact database. They will only have their own data in their compact database. No other user's data will be downloaded. The database on the backend SQL Server will contain all users data collectively.

    To Expand:

    For example, the following structure.

    Compact Database
     Table
      Column1
      Column2

    SQL Server Database
     Table
      Column1
      Column2
      UserID

    User A does this to their compact database: INSERT INTO Table(Column1,Column2) VALUES=('Hello','World')

    User B does this to their compact database: INSERT INTO Table(Column1,Column2) VALUES=('Foo','Bar')

    So, the compact status is

    User A
     Compact Database
      Table
       Column1=Hello
       Column2=World

    User B
     Compact Database
      Table
       Column1=Foo
       Column2=Bar

    After syncing the SQL Server database is

    SQL Server Database
     Table
      Column1=Hello
      Column2=World
      UserID=UserA

      Column1=Foo
      Column2=Bar
      UserID=UserB

    User A doesn't download UserB information at all and User B doesn't download UserA information at all.


    Owner, Quilnet Solutions
    Tuesday, January 25, 2011 3:17 AM
  • in this case, you can filter the data to each client side database so that each of them will only has the data than belongs to the current user. as the filter is done with different scopes, you can still use the same login to the sql server for syncs. this login is with the server side provider so it can be shield to the users. for details about filtering data you can refer to this link: http://msdn.microsoft.com/en-us/library/ff928701(v=sql.110).aspx thanks Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Yunwen Bai Tuesday, January 25, 2011 8:40 PM
    • Unmarked as answer by Quilnux Tuesday, January 25, 2011 8:56 PM
    Tuesday, January 25, 2011 9:41 AM
  • in this case, you can filter the data to each client side database so that each of them will only has the data than belongs to the current user. as the filter is done with different scopes, you can still use the same login to the sql server for syncs. this login is with the server side provider so it can be shield to the users. for details about filtering data you can refer to this link: http://msdn.microsoft.com/en-us/library/ff928701(v=sql.110).aspx thanks Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.


    Right, I knew about filtering already. I looking for more information that talks about the login being server-side.


    Owner, Quilnet Solutions
    Tuesday, January 25, 2011 1:57 PM
  • Anyone got any suggestions for safeguarding credentials from client-side applications?
    Owner, Quilnet Solutions
    Wednesday, January 26, 2011 4:39 AM
  • I think in this case you only need to provide the scope name for sync. you will need use the login info stuff internall in the APP and refresh the app if the credential changes.

    if you use the N tier configuation where the server provider is on the IIS box. you can dynamicaly load it fro there.

    or any other reason you will need to have the credentials on the clients ? i see some folks use encrypted string for it or other means but this will up to you to deal with it.

    thanks

    Yunwen

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, January 26, 2011 8:17 PM
  • I think in this case you only need to provide the scope name for sync. you will need use the login info stuff internall in the APP and refresh the app if the credential changes.

    ...

    or any other reason you will need to have the credentials on the clients ? i see some folks use encrypted string for it or other means but this will up to you to deal with it.

    thanks

    Yunwen

     


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


    The concern is that someone will use a memory reader in the application to get the credentials from the connectionstring.

    You say use an encrypted string. how does that work?

    The reason we would have credentials on the client is, how would it know where the server is or how to authenticate with it?

     


    Owner, Quilnet Solutions
    Wednesday, January 26, 2011 8:42 PM
  • I'm just starting a similar type project, I need to sync several HP Slates (Win 7) with each other thru SQL Azure but segregate the data by subscriber.

    So each will have the same same login if I go directly. So I plan to use a WCF Service that accepts the Subscriber ID (Guid) that will be stashed in (SilverLight) Isolated Storage on install. The Key is a look up into subscribers with a standard (HttpS.. maybe a login maybe not)

    The right Key authorizes the return results and toggles the concurrent users, This way the connection string stays on the server. Anything client side is discoverable especially in .Net.

    Not sure how to make Sync work as a service but I think that's the point of Nina Hu's PDC 10 session http://player.microsoftpdc.com/Session/28830999-dd97-45c4-9bb4-bb16ba21a6b0

     

    Since we are apparently going down the same road fell free to contact me directly PTormey@4square.net

     


    Pat Tormey PE MVP Client App Dev
    Wednesday, January 26, 2011 9:38 PM
  • I'm just starting a similar type project, I need to sync several HP Slates (Win 7) with each other thru SQL Azure but segregate the data by subscriber.

    So each will have the same same login if I go directly. So I plan to use a WCF Service that accepts the Subscriber ID (Guid) that will be stashed in (SilverLight) Isolated Storage on install. The Key is a look up into subscribers with a standard (HttpS.. maybe a login maybe not)

    The right Key authorizes the return results and toggles the concurrent users, This way the connection string stays on the server. Anything client side is discoverable especially in .Net.

    Not sure how to make Sync work as a service but I think that's the point of Nina Hu's PDC 10 session http://player.microsoftpdc.com/Session/28830999-dd97-45c4-9bb4-bb16ba21a6b0

     

    Since we are apparently going down the same road fell free to contact me directly PTormey@4square.net

     


    Pat Tormey PE MVP Client App Dev


    I'll take a look at this during dinner but I think what you described is how we want it. In our case we don't have a Silverlight application, we have an installed Windows client.

    Not sure if Sync as a service is needed. We can still rely on SQL Server. We just need to protect the authenication. The only other thing we can think of doing (which is very costly) would be to have the client upload the Compact database, do the sync on the server, and have the client download the sync'ed compact database back. Then sync that downloaded compact database with their local database.

    I'll review the video and get back to you guys.


    Owner, Quilnet Solutions
    Wednesday, January 26, 2011 10:22 PM
  • The sync service looks pretty cool. (GoLive license?) But I don't think that's gonna help me in the short term :-P.


    Owner, Quilnet Solutions
    Wednesday, January 26, 2011 11:25 PM
  • how about using Certificate based authentication on WCF coupled with a client GUID?

    the certificate can be used to authenticate that the client is allowed to consume the WCF service, when the client is authenticated, you then use the client GUID to look up the corresponding connection information on a database on the server side.

    • Marked as answer by Quilnux Thursday, January 27, 2011 11:48 PM
    Thursday, January 27, 2011 2:29 PM
  • how about using Certificate based authentication on WCF coupled with a client GUID?

    the certificate can be used to authenticate that the client is allowed to consume the WCF service, when the client is authenticated, you then use the client GUID to look up the corresponding connection information on a database on the server side.


    Huh?

    Does the connection information ever show up in the client's memory?


    Owner, Quilnet Solutions
    Thursday, January 27, 2011 4:55 PM
  • I think what they are going to do at this point is to approve for having the Compact databases of every customer uploaded to the server, the server will handle the sync, then pass the synced compact database back to the client.

    At least until we find another solution.


    Owner, Quilnet Solutions
    Thursday, January 27, 2011 7:17 PM
  • both JuneT's suggestion and your way to handle this can help here in terms of security. I want to mention one this before we close this thread. security should be taken care of in the sync application ( as in this case ), all the options we discussed here were used at some level, to name a few below:

    1. filter data to security the data

    2. encryption senstitve info such as credentials 

    3. Ntier configuration to hide the server provider at the IIS box and use security features provided by the transport

    4. sync the entire db at the server and push to client the .sdf files.

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Yunwen Bai Thursday, January 27, 2011 8:04 PM
    Thursday, January 27, 2011 8:03 PM
  • You guys keep saying NTier through IIS. How do you use IIS with Sync?
    Owner, Quilnet Solutions
    Thursday, January 27, 2011 9:28 PM
  •  

    by Ntier, I refer to one of the most common architectures to implement sync solutions. this link has details of it: http://msdn.microsoft.com/en-us/library/bb902829(v=SQL.110).aspx

    also you can refer to the secuirty section on the BOL at http://msdn.microsoft.com/en-us/library/bb726013(v=SQL.110).aspx for further consideration to design a secution sync solutions using sync framework.

     

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Quilnux Thursday, January 27, 2011 10:41 PM
    Thursday, January 27, 2011 10:32 PM
  • Ok, now that makes since.

    It didn't give me a solution, but one of the links on that page led me to another that says XML can be synced as well. We may be able to use XML to sync between the client and server which would also have a lighter payload when transporting.

    Thanks!


    Owner, Quilnet Solutions
    Thursday, January 27, 2011 10:41 PM
  • huh?

    Does the connection information ever show up in the client's memory?


    Owner, Quilnet Solutions
    No, your server's connection string remains on the WCF service on the server side and you're client doesnt even have any idea where the server is. it only knows about the service.
    Thursday, January 27, 2011 11:40 PM
  • No, your server's connection string remains on the WCF service on the server side and you're client doesnt even have any idea where the server is. it only knows about the service.

    Until Yunwen gave me the link he did, I wasn't making the connection between Sync and IIS/WCF. I was under the impression that Sync only worked with Databases directly which is probably why I wasn't getting what most people were saying here. Now I understand what you are referring to. Yes, this will probably work for us. Thanks!
    Owner, Quilnet Solutions
    Thursday, January 27, 2011 11:47 PM