To help secure database applications that are based on Sync Framework, we recommend that you take the following steps. For more information about database security, see SQL Server Compact Books Online and SQL Server Books Online.
Use the principle of least permission. Do not grant more permissions than are required to perform a specific task. For example, do not grant INSERT permissions for server database tables that are involved in download-only synchronization. Synchronization operations require the following permissions:
EXECUTE permissions on all the stored procedures that Sync Framework uses to read and write to metadata tables and base tables.
SELECT, INSERT, UPDATE, and DELETE permissions for metadata tables and any base tables that will be updated during a synchronization session.
When you provision SQL Server databases that use SqlSyncProvider, be aware of the following permissions requirements for provisioning:
CREATE TABLE permissions to enable creation of the metadata tables: scope_info, scope_config, scope_parameters, and scope_templates, and the tracking tables that are created for each base table.
ALTER TABLE to add triggers to the base tables.
CREATE PROCEDURE permissions to create the procedures that Sync Framework requires.
SELECT and INSERT permissions for scope_info, scope_config, scope_parameters, and scope_templates tables.
SELECT permissions for base tables.
When you provision a database from a component that is running outside of the Windows Azure environment or use a 2-tier architecture to synchronize with a database, be aware of the following additional permission requirement for provisioning and synchronization:
- Add a rule to your Firewall Settings on the Windows Azure Development Portal to allow your IP address access to the database.
Sync Framework does not validate the contents of the scopes that define the set of tables to synchronize (with optional filters). Include logic in your applications to verify that the scope being sent corresponds with the tables expected to be in that scope and that the expected filters have been applied.
Configure servers and server databases to expose the least surface area for attack. For example, if you use an Internet Information Services (IIS) server as part of an N-tier architecture, do not enable the file transfer protocol (FTP) service unless it is required by another application that uses the server.
Encrypt or password-protect sensitive data on disk and in transit. Sync Framework does not provide encryption for connections. Encryption is available at the transport level with several technologies. These include the following industry standard technologies: Windows Communication Foundation (WCF), Virtual Private Networks (VPN), Secure Sockets Layer (SSL), and Internet Protocol security (IPsec). We recommend that you use one of these encryption methods for the connections that are made during synchronization. For more information about encryption, see the documentation for Windows and SQL Server Compact, and the documentation for the server or peer database that you are using.
Use stored procedures instead of inline SQL to query server databases. Stored procedures help to secure an application in the following ways:
By using stored-procedures, administrators can define a well-known set of entry points to the database. Users can be given access to stored procedures and not to the underlying tables.
The use of stored procedures also encourages the use of parameters instead of dynamically built queries. This makes it more difficult to perform SQL injection attacks.
If an application is compromised, the data access logic is not necessarily compromised.
Validate the data sent during synchronization. Use the events that fire during synchronization to validate changes before you apply those changes on the destination database. For more information about events, see How to: Work with Events and Program Business Logic.
Establish trust between local assemblies and remote assemblies in N-tier architectures. In N-tier scenarios, local assemblies and remote assemblies should build trust between each other. The mechanism to build the trust relationship is outside the scope of the Sync Framework API and must be handled by your application.
Follow these guidelines if you use Windows Communication Foundation (WCF) for N-tier applications:
Set the appropriate permissions on the WCF namespace that your service uses. Create a new namespace or be sure that the default namespace has the appropriate permissions set.
Run the WCF service under a dedicated account with only the privileges necessary to access databases involved in synchronization and any spooling files that are used when synchronization changes are batched.
Use a dedicated application pool for the WCF service to isolate synchronization processes from any other services that are running on the middle-tier server.
Use WCF throttling to help prevent denial of service attacks (DOS) against the Web service. Throttling controls the resources that a user of the service can consume.
Replace the default WCF URL reservation with a restricted reservation. For more information, see this Microsoft Article.
For more information, see the WCF documentation.
If you use tracing, be aware that trace files can include information about server and client computers, application data, and logins. (Passwords are not written to the trace file.) If verbose tracing is enabled, each changed row from the database is written to the trace file. Help protect the trace file by using the appropriate access control lists. For more information about tracing, see Tracing the Synchronization Process.
For client and server synchronization, explicitly set the ClientId property in the client application if you can. If the property is not set, an ID is assigned by Sync Services. In this case, the client application must be able to access the following registry hive on the server: HK_CURRENT_USER\Software\Microsoft\Microsoft SQL Server Compact Edition\v3.5. Accessing the registry hive requires administrator rights. This is not recommended.
Keep these issues in mind if you define filters for tables that are synchronized:
Sync Framework does not validate the SQL strings that are specified for scope filters. Developers should adhere to best practices specific to protecting against malicious strings. For more information, see this MSDN article.
Do not rely on filtering for security. The ability to filter data based on a client or user ID is not a security feature. In other words, this approach cannot be used to prevent one client or peer from reading data that belongs to another client or peer. This type of filtering is useful only for partitioning data and reducing the amount of data that is synchronized.