Securing a simple synchronization application

Question

Hello,

I am wanting to secure a simple synchronization application during transit, however, the code examples seem to be much more complex than I need. My scenario is as follows:

• Local SQL database with firewall enabled, no external access.
• SQL Azure with firewall enabled, port forward for my current public IP.
• Changes are one-way, originating from local and uploading all changes to Azure.

The code I am currently utilizing is much simpler than the code demos listed for Microsoft Sync Framework, and is listed below (Setup applies scope to local/azure, Sync performs one-way synchronization). This is currently unsecured during transit. What I wish to add is some security for this process. I have read that WCF/HTTPS/IPSec/VPN can be used for transport level security. Is this is simple as just adding a app.config to my web forms application with the appropriate settings, and Sync Framework picks up the security? I really don't think I need to roll out multiple web/worker roles on Azure just to get this working, as some of the code samples suggest.

To note: This synchronization process will only be used once in a while, perhaps once or twice a month. What is the security risk of in-transit synchronization utilizing the below method?

       private string localConn = #MyLocalConnHere#;
        private string azureConn = #MyAzureConnHere#;
        private string scopeName = "TestScopeName";

        public Form1()
        {
            InitializeComponent();
        }

        private void btnSetup_Click(object sender, EventArgs e)
        {
            using (SqlConnection sqlServerConn = new SqlConnection(localConn))
            {
                using (SqlConnection sqlAzureConn = new SqlConnection(azureConn))
                {
                    DbSyncScopeDescription myScope = new DbSyncScopeDescription(scopeName);
                    DbSyncTableDescription testTable = SqlSyncDescriptionBuilder.GetDescriptionForTable("testtable", sqlServerConn);
                    myScope.Tables.Add(testTable);

                    SqlSyncScopeProvisioning sqlServerProv = new SqlSyncScopeProvisioning(sqlServerConn, myScope);
                    if (!sqlServerProv.ScopeExists(scopeName)) sqlServerProv.Apply();

                    SqlSyncScopeProvisioning sqlAzureProv = new SqlSyncScopeProvisioning(sqlAzureConn, myScope);
                    if (!sqlAzureProv.ScopeExists(scopeName)) sqlAzureProv.Apply();
                }
            }
        }

        private void btnSync_Click(object sender, EventArgs e)
        {
            using (SqlConnection sqlServerConn = new SqlConnection(localConn))
            {
                using (SqlConnection sqlAzureConn = new SqlConnection(azureConn))
                {
                    SyncOrchestrator syncOrchestrator = new SyncOrchestrator
                    {
                        LocalProvider = new SqlSyncProvider(scopeName, sqlServerConn),
                        RemoteProvider = new SqlSyncProvider(scopeName, sqlAzureConn),
                        Direction = SyncDirectionOrder.Upload
                    };
                    syncOrchestrator.Synchronize();
                }
            }
        }

Code adapted from post by Wayne Walter Berry, who adapted it from Liam at Sync Team Blogs.

Answer 1

i can see that you are connecting directly to SQL Azure from your sync app. Doesnt the built-in SQL Azure encryption address you "man-in-the-middle" concerns while data is in transit?

see: http://social.technet.microsoft.com/wiki/contents/articles/2763.sql-azure-connection-encryption.aspx

Answer 2

I was unaware of this functionality! Thank you for the post/link, it definitely saves on complexity/development time.