Thursday, October 27, 2011

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

5 comments:

Rod Mac said...

I'm just about to decide upon a first, SQL Compact 4 app for shared server space. I'm always looking to do new things which have some chance of surviving as a 'version next'. Is there any news confirming that there will be such a thing as SQL Compact 5 as part of 'Denali' in 2012?
Thank you in advance.

ErikEJ said...

Rod: SQL Server Compact is no longer part of the SQL Server releases, but tied to Visual Studio only. In VS 11 Preview, version 4.0 is included, I am guessing a service pack for 4.0 could apprear before VS 11 appears.

Unknown said...

Hi Erik, can you tell us what is new in 4.0.0.1 as opposed to 4.0.0.0? We're thinking of downgrading to 4.0.0.0 to support medium trust again, but unsure what we'd be losing.

Also, will there be a future version that does support medium trust?

ErikEJ said...

Hi Sebastian, assembly version 4.0.0.0 and 4.0.0.1 is the same code, but 4.0.0.1 is isolated for desktop deployment and does not Work with medium trust. Version 4.0 and 4.0 SP1 both support medium trust, as long as you use assembly version 4.0.0.0. Please feel free to contact me by mail if you need further info. See my blog post about 4.0 SP1 for a feature "overview".

Unknown said...

Thank you so much for outlining the problems associated with SQL server compact 4.0.The usage of virtual memory for each connection of SQL Server Compact has been reduced in SQL Server Compact 4.0. The visible difference is that if an application tries to open 40 to 50 simultaneous connections in SQL Server Compact 3.5 SP2, an ‘out of virtual memory’ exception will be hit. But with SQL Server Compact 4.0, the application can easily open as many connections as needed to the database file, up to the full extent of 256 connections without running out of virtual memory.