SQL Server Compact 4.0 under ASP.NET Hosting– common issues

This blog post covers some of the issues that can be encountered when using SQL Server Compact under ASP.NET, in particular in a hosted environment, where the environment can be restricted in unexpected ways.

In order to be able to troubleshoot issues with ASP.NET hosting of web sites using SQL Server Compact 4.0, it can be useful to understand how Medium Trust works.

Medium Trust only works under .NET 4.0 (ASP.NET 4.0), as it depends on a configuration in the global web.config.

The global web.config is located at C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config or C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config, and contains the following setting, that was configured when .NET 4.0 was installed:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <location allowOverride="true">
    <system.web>
      <fullTrustAssemblies>
        <add
            assemblyName="System.Data.SqlServerCe"
            version="4.0.0.0"
            publicKey="0024000004800000940000000602000000240000525341310004000001000100272736ad6e5f9586bac2d531eabc3acc666c2f8ec879fa94f8f7b0327d2ff2ed523448f83c3d5c5dd2dfc7bc99c5286b2c125117bf5cbe242b9d41750732b2bdffe649c6efb8e5526d526fdd130095ecdb7bf210809c6cdad8824faa9ac0310ac3cba2aa0523567b2dfa7fe250b30facbd62d4ec99b94ac47c7d3b28f1f6e4c8"
                />
      </fullTrustAssemblies>
      <partialTrustVisibleAssemblies />
    </system.web>
  </location>

So this was added during installation of .NET 4.0, long before SQL Server Compact 4.0 was released in January 2011. The fullTrustAssemblies element is new in .NET 4.0: http://weblogs.asp.net/asptest/archive/2010/04/23/what-is-new-in-asp-net-4-0-code-access-security.aspx

If this section has been removed from the global web.config file, SQL Server Compact 4.0 will not run under medium trust.

It is possible to configure similar functionality under .NET 3.5 SP1, as described here: http://msdn.microsoft.com/en-us/library/ms174612(v=SQL.110).aspx

Notice that the SQL Server Compact binaries are delivered with two Assembly version numbers, 4.0.0.0 and 4.0.0.1. The 4.0.0.1 Assembly is for Private deployment, but on desktop only, as described here: http://msdn.microsoft.com/en-us/library/gg213826.aspx – it will not work with Medium Trust, as the assembly version registered in the machine.config file is 4.0.0.0, as shown above.

Other issues you may encounter when running ASP.NET and SQL Server Compact 4.0 can be:

- The process identity running your web application must have write access to the App_Data folder or the folder where your database file resides:

http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/6c1c8798-85d7-4d8f-9908-e9f7dc1c17e4

- SQL Server Compact database files must be located on a local drive, not shared folders:

http://stackoverflow.com/questions/7791345/sqlce-4-ef4-1-internal-error-cannot-open-the-shared-memory-region/7804049#7804049

- If SQL Server Compact is not installed locally, a number of files must be included in the bin folder of your web site:

http://stackoverflow.com/questions/3223359/cant-get-sql-server-compact-3-5-4-to-work-with-asp-net-mvc-2/3223450#3223450

http://msdn.microsoft.com/en-us/library/gg286946.aspx

- If only .NET 4.0 is present on the server, you may be missing the Visual C++ 2008 SP1 Redistributable Package (which is installed only with .NET 3.5 SP1)

I will update this blog post if I notice more “common issues”.