Deploying your site with SQL CE 4.0 to shared webhosting (Part 2)

In the previous post I described how to create your website with ASP.NET and SQL Server Compact 4 as database. The next step is to deploy your site. If you have a VPS or dedicated server, you got full access to it and normally shouldn’t run into any problems. But when you’re using shared webhosting, your actions are very limited. This post describes the errors you’ll most likely run into the first time you try this, and how to solve them.

You can either deploy from within Visual Studio or FTP the files to your host. If you hit the URL to your website, you’ll most likely get following configuration error with the according web.config fragment below:
Parser Error Message: Could not load file or assembly ‘System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.

<compilation debug="true" targetFramework="4.0">
  <assemblies>
    <add assembly="System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>
    <add assembly="System.Data.Linq, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
    <add assembly="System.Security, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>

You get this error because most providers don’t have SQL CE 4.0 assemblies installed (yet?). This means you’ll have to ‘bin deploy’ the assemblies together with your site. Visual Studio 2010 provides a “Add Deployable Dependencies…” context menu item, which let you choose to add the required assemblies for Razor or SQL Server Compact to the Bin folder. (picture source)

BindeployAddDependencies

With some ’luck’ you hit the next error once you solved the problem above and refreshed the page:

Parser Error Message: Access to the database file is not allowed. [ 1884,File name = somepath\App_Data\YourDatabase.sdf,SeCreateFile ]

For this error you have to give read + write access to the App_Data folder, usually done in the File Manager of your hosting control panel. If you got this error on your private server, check if the service running the Application Pool (e.g. Network Service) has these rights on the folder. You will also have to add following line of code (e.g. in the global.asax file):

AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);

This is because, by default, SQL CE blocks access when the connection is opened under the ASP.NET networking process. Once I ran through these steps, I got everything up and running without any hiccups.