Unable to load the native components of SQL Server Compact

Question

I've installed SQL Server Compact Edition 4.0 on Win7 x64 and it runs both for Asp.Net and Desktop Applications. This PC also have Visual Studio 2010 SP1 installed. But my Server 2008 R2 produces following error for Asp.Net applications, although it can run Desktop Applications:

Unable to load the native components of SQL Server Compact corresponding to the
ADO.NET provider of version 8482. Install the correct version of SQL Server
Compact. Refer to KB article 974247 for more details.

I've tried both with a SqlDataSource and SqlCeConnection. Same error. My web.config is like below:

<?xml version="1.0"?>
<configuration>
    <connectionStrings>
       <add name="SQLCE" connectionString="Data Source=|DataDirectory|\a.sdf"
        providerName="System.Data.SqlServerCe.4.0" />
    </connectionStrings>
    <system.web>
        <compilation debug="true" targetFramework="4.0">
            <assemblies>
                <add assembly="System.Data.SqlServerCe, Version=4.0.0.0,
                Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
            </assemblies>
        </compilation>
    </system.web>
</configuration>

Also tried to copy dll's as sugested here but no effect.

Answer 1

Finally got SQL Server Compact Edition 4.0 working under IIS 7.5. The problem was permission issue. Current Application Pool's identity IWAM_plesk(default) didn't have access to SQL Server Compact 4.0 folders:

C:\Program Files\Microsoft SQL Server Compact Edition\v4.0

C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0

I just granted Read & Execute and List Folder Contents permissions and now it works like a charm.

Answer 2

There are two ways to deploy SQL Server CE 4.0 in ASP.net.

1. Medium trust, or

2. private deployments.

Your configuration file uses version 4.0.0.0 -

<add assembly="System.Data.SqlServerCe, Version=4.0.0.0,
            Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>

this requires Medium Trust, and requires the App Pool user having permission to access the SQL Server Compact COM dlls in C:\Program Files\Microsoft SQL Server Compact Edition.

Where medium trust fails, you could attempt private deployment. This is my preference and there's less chance something will go wrong with your system sometime in the future.

If you private deploy the SQL Server CE DLLs, by copying all the files in v4.0\Private to bin\, make sure your Web.config declare a dependency on version 4.0.0.1. This version is not deployed in the GAC and this will ensure your private copy gets loaded.

<system.web>
 <compilation>
  <assemblies>
    <add assembly="System.Data.SqlServerCe, Version=4.0.0.1,
            Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
  </assemblies>
 </compilation>
</system.web>

You will also require a corresponding change in the DbProvider section in Web.config

<system.data>
    <DbProviderFactories>
        <remove invariant="System.Data.SqlServerCe.4.0" />
        <!-- Use private copy 4.0.0.1 -->
        <add invariant="System.Data.SqlServerCe.4.0" 
             name="Microsoft SQL Server Compact 4.0"
             description=".NET Framework Data Provider for Microsoft SQL Server Compact"
             type="System.Data.SqlServerCe.SqlCeProviderFactory,
                   System.Data.SqlServerCe,
                   Version=4.0.0.1,
                   Culture=neutral,
                   PublicKeyToken=89845dcd8080cc91" />
    </DbProviderFactories>
</system.data>