Things to check when your SQL Server Compact Database won’t work on a production server.

Posted: 07/12/2011

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:

  1. Open your web site solution in Visual Studio 2010.
  2. Right click on the solution in the Solution Explorer (it’s very important that you right click on the solution and not the Bin directory to get the meny items you need). You’ll choose “Add Deployable Dependencies…” from this menu.
  3. A dialog appear which will probably have two items (ASP.Net Web Pages with Razor Syntax and SQL Server Compact). Choose “SQL Server Compact” and click ok.

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).