As we approach the final stages of our third sprint here at Intilecta I took the opportunity this evening to look back at some of the issues we have faced using SQL Server Everywhere for replicating and caching offline data. Following any of the numerous online samples it is easy to get basic synchronisation working. However, when the architectural issues associated with an enterprise application kick in there are a number of issues that SQL Server Everywhere doesn’t really cater for.
One these issues is how security is managed. From the local database point of view there are some things that you can do to protect the local database:
- Have a strong password on the database (as we are creating the local database as an offline store we randomly create this password – the user should never have to key in a password)
- Enable encryption on the database when it is created (again this is enabled as part of the connection string you specify when creating the local database)
- Put the database in a user specific directory (ie under “documents and settings” (or “Users” in the case of Vista) – this will ensure you have filesystem protection from other users accessing the raw data file, unless of course they are a local admin)
Ok, but I’m writing my application in .NET which means that any password I use can be easily decoded from the assembly. This is where you can use the managed wrapper for the Windows DPAPI (see the ProtectedData class in the .NET Framework v2) to encrypt/decrypt the password, placing the resulting value in the registry. Again the registry key you select should be specific to the current user.
Now that we have secured the local database, how do we connect to the server to synchronise the data down to the local database. In simple terms there are three main parties involved in the synchronisation process (well there are actually more, but these are the main security concerns). At the back end you have the database itself which supports SQL Server or Windows Authentication. You then have IIS, as SQL Server Everywhere only supports synchronising using a Merge Publication accessed via a virtual directory, which supports Anonymous, Windows and Basic Authentication. Lastly you have the local database, which we have just covered.
Where this gets difficult is when you consider that it is essentially IIS that is pulling data from the database. Which ever user IIS is running as (or impersonating) will be the user that accesses the database. In order to configure a subscription you start by defining what type of security you are going to use to authenticate against IIS. You do this by either providing a username and password, or not (in which case anonymous access is used). This will determine which authenticate mechanism is used against IIS, with the following interesting cases:
- If you have anonymous access enabled on the virtual directory, this will always be the preferred authentication method
- If you have Windows Authentication enabled the user that you are running the application as will be used to authenticate against IIS – is this what you want?
Once you have authenticated with IIS it is time to determine what user will be used to access the database. Again, when you set up the subscription you can either specify a username/pasword combination, or not (in which case pass through authentication is used). If you don’t provide a username the user that IIS is running as will be used to authenticate against sql server. This user will either be the impersonated account (if using anonymous access), the application account (if Windows Authentication), or a specified account (in the case of basic authentication).
You will note here that we have been including Windows Authentication in this discussion, despite the fact that the documentation clearly states that it is not supported. The reason for this is that it half works. In fact it fully works, with the exception that IIS and SQL Server have to reside on the same machine. As soon as you put them on different boxes you can only use basic authentication with IIS. The main reason for this (I guessing) is that SQL Everywhere is based on all the work done to build SQL CE and since Windows Mobile never supported Windows Authentication, we can assume it was never in the original product spec.
The upshot of all this discussion is that only Basic Authentication is supported (well anonymous is, but not an ideal solution for securing your data), which means that you need to store a username/password with your application. Alternatively the user could provide a username/password – this option is clearly not great as they have to enter it everytime you want to sync.
Workaround – The workaround that we came up with makes a compromise in both directions with the net effect of improving usability of the product. Only as a last resort could we afford to prompt the user even once to enter a username/password combination. The application user has already authenticated with the machine, so we should be able to use this information to authenticate against IIS. Well we can, in so far as we can authenticate against a webservice. This webservice will provide us with the necessary username/password combination that we will use to authenticate against IIS.
The compromise here is that it is not great that a username/password combination is being passed across the wire. However, the data is being protected since the user has to authenticate using Windows Authentication and the channel itself is encrypted using SSL. Lastly, the username/password is NEVER cached locally so it is actually harder for anyone to steal the username/password. Oh, the other plus is that if we ever want to change the username/password, we can.
The end result is that we have a system that authenticates using basic authentication, but without either the poor usability that prompting for a username/password results in or the negative security issues with hardcoding a username/password into the application.
I hope you find this useful and if you have any comments on how you have addressed similar issues I would love to hear from you.