There are two things that you’ll want to check if you’ve developed an ASP.Net solution using SQL Server Compact 4.0 and it doesn’t work when you move it to your production environment (or any server that isn’t your workstation). The first is that the proper assemblies have been included in your project. My fatal mistake initially was adding a reference to the SQL Server CE libraries which worked locally but failed when porting to my production box. How do you find all of the assemblies required and get the web.config updated without a headache? Easy, follow these steps:
The above will copy around 20 files into your bin directory (a combination of assemblies, XML files and a README file). It will also update your web.config with the appropriate items for SQL Server CE. You will want to move all of these assemblies with your site to production (and make sure the Web.config updates are reflected on your server).
Now, onto the next problem. Even though the web page account had access to read/write to the AppData directory (it worked with Access databases for instance) it did not have some permissions needed to work with SQL Server CE 4.0. Honestly, I never actually figured out what those permissions were. My code would fail on the database connection open statement. Positive that my connection string was connect I tried impersonation (here’s my VB.Net library for impersonating inside the code and not for the entire page: http://impersonation.codeplex.com/). Basically, I would start impersonating with my server account when the database connection was opened and end impersonating when it was closed. This allowed me to connect to the database run queries against it. Below is a basic example showing what I did (no error handling, but impersonates then connects to the DB).