Can I use SQL Server CE in a medium trust environment by just deploying the DLLs

Question

I've written an ASP.NET 4.0 application that is using a SQL Server CE 4.0 database. This is all working on my development environment using Medium Trust (same trust config as the hoster). On this machine, I've gone through the install process (downloaded and installed the MSI). I'm now trying to deploy this solution to my web hoster.

The code that is running is (this is a straight out copy of the Altairis Membership Provider:

using (HostingEnvironment.Impersonate())
using (var db = this.connectionString.CreateDbConnection())
using (var cmd = this.CreateDbCommand("SELECT PasswordHash, PasswordSalt FROM $Users WHERE UserName = @UserName AND IsApproved = 1", db)) 
{
     cmd.AddParameterWithValue("@UserName", username);
     db.Open();

     // Validate password
     using (var r = cmd.ExecuteReader()) { .. snip.. }
 }

In the instance above, db is DbConnection, and cmd is DbCommand

I'm getting the following error:

[InvalidOperationException: Cannot perform CAS Asserts in Security Transparent methods] System.Security.CodeAccessSecurityEngine.CheckNReturnSO(PermissionToken permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 create) +0
System.Security.CodeAccessSecurityEngine.Assert(CodeAccessPermission cap, StackCrawlMark& stackMark) +61
System.Security.CodeAccessPermission.Assert() +28
System.Data.SqlServerCe.KillBitHelper.GetKillBit() +231
System.Data.SqlServerCe.KillBitHelper..cctor() +56

[TypeInitializationException: The type initializer for 'System.Data.SqlServerCe.KillBitHelper' threw an exception.]
System.Data.SqlServerCe.KillBitHelper.ThrowIfKillBitIsSet() +0
System.Data.SqlServerCe.SqlCeProviderFactory..cctor() +41

[TypeInitializationException: The type initializer for 'System.Data.SqlServerCe.SqlCeProviderFactory' threw an exception.]

[TargetInvocationException: Exception has been thrown by the target of an invocation.]
System.RuntimeFieldHandle.GetValue(RtFieldInfo field, Object instance, RuntimeType fieldType, RuntimeType declaringType, Boolean& domainInitialized) +0
System.Reflection.RtFieldInfo.InternalGetValue(Object obj, Boolean doVisibilityCheck, Boolean doCheckConsistency) +180
System.Reflection.RtFieldInfo.GetValue(Object obj) +8
System.Data.Common.DbProviderFactories.GetFactory(DataRow providerRow) +232
System.Data.Common.DbProviderFactories.GetFactory(String providerInvariantName) +88
Altairis.Web.Security.DatabaseExtensionMethods.CreateDbConnection(ConnectionStringSettings settings) in DatabaseExtensionMethods.cs:19
Altairis.Web.Security.TableMembershipProvider.ValidateUser(String username, String password) in TableMembershipProvider.cs:361

I've used a decompiler to look through the code, and it seems that the internals of SQL Server CE are looking for a registry entry and then trying to do a code Assert().

Is it possible to use SQL Server CE 4.0 in a partial trust environment, by just \bin deploying the DLL ??

Answer 1

Yes, provided your hoster allows it - it is allowed by default in ASP.NET 4.0. Make sure you use assembly version 4.0.0.0 (and not 4.0.0.1). Read more here: http://erikej.blogspot.com/2011/10/sql-server-compact-40-under-aspnet.html