"Unable to open the database file" (warning: Wide error)

Question

I cannot seem to get anything out of SQLite other than "Unable to open the database file" on IIS. I'm convinced SQLite's error messages are as brusque as Oracle's.

• Pre-deployment in Visual Studio 2010/IIS Express I can both read and write to the file.
• When I tried to read/write it with the same VS2010 project deployed to IIS7.5, all "create", "read" and "write" commands fail.
• The same occurs when I deploy the database file through the project and try to read it.
• I've given Full control access to App_Data and the database file to the following users: IIS_IUSRS, IUSRS, DefaultAppPool, and Everyone.

I've looked at: SQLite problem "unable to open the database file" (The problem automatically went away for the user) and a number of other similar questions, most of which were solved by changing permissions, changing to a writable directory (App_Data should be writable, no?) or changing a relative path to an absolute one (which |DataDirectory| should resolve to).

<connectionStrings>
    <add name="sqlite" connectionString="Data Source=|DataDirectory|\datatables.sqlite;Version=3;" />
</connectionStrings>

Have I missed anything?

<Error>
<Message>An error has occurred.</Message>
<ExceptionMessage>Unable to open the database file</ExceptionMessage>
<ExceptionType>System.Data.SQLite.SQLiteException</ExceptionType>
<StackTrace>
at System.Data.SQLite.SQLite3.Open(String strFilename, SQLiteConnectionFlags connectionFlags, SQLiteOpenFlagsEnum openFlags, Int32 maxPoolSize, Boolean usePool) at System.Data.SQLite.SQLiteConnection.Open() at AjaxSource.Models.Database.query(String sql, Dictionary`2 parameters) in D:\Tools\Dropbox\Projects\myprojects\AjaxSource\AjaxSource\Models\Database.cs:line 48 at AjaxSource.Models.aaDataModel..ctor() in D:\Tools\Dropbox\Projects\myprojects\AjaxSource\AjaxSource\Models\aaDataModel.cs:line 18 at AjaxSource.Controllers.API.TableDataController.Get() in D:\Tools\Dropbox\Projects\myprojects\AjaxSource\AjaxSource\Controllers\API\TableDataController.cs:line 15 at lambda_method(Closure , Object , Object[] ) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass13.<GetExecutor>b__c(Object instance, Object[] methodParameters) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments) at System.Threading.Tasks.TaskHelpers.RunSynchronously[TResult](Func`1 func, CancellationToken cancellationToken)
</StackTrace>
</Error>

Answer 1

I had a problem opening Sqlite database in IIS

(0x80004005): unable to open database file unable to open database file

what helped me to solve the problem is changing the "Identity" of the application pool to "LocalSystem"

Application Pools -> DefaultAppPool (or another pool you are working with) -> Advanced Settings -> Identity -> LocalSystem

Hopefully it will save someone time...

Answer 2

We used to encounter this problem on the machines of our customers most often, and I have made a lot of investigation upon it and finally we've solved it.

First of all, you need to make sure that your application has read/write access to the database file as well as the folder containing the database file. In most situations, the check will fix the issue; but we are not among those fortunate ones.

In our case, where the application makes very highly concurrent access to the database, it's most probably related to the journal mode of the database, which usually is DELETE by default. That is to say, the rollback journal will be deleted once the corresponding transaction is committed or rolled back. On **nix systems, it's no error; but on Windows systems, it's another story, as can be seen below. On Windows systems, the problem could take place in the following scenario:

1. A journal file (say A) is created by a SQLite thread.
2. Another thread P tries to open A.
3. SQLite has finished its transaction and so deletes the journal file A.
4. But now thread P has handle for A, so SQLite enters the “Delete Pending” state.
5. SQLite starts another transaction and has to recreate the journal file (with the same name).
6. Windows reports ERROR_DELETE_PENDING error (see here for more information: http://blogs.msdn.com/b/oldnewthing/archive/2007/11/09/6001644.aspx)
7. SQLite report SQLITE_CANTOPEN error (that is, "Unable to open the database file").
8. P finally releases the file handle and A is deleted.

One solution is to use PERSIST or TRUNCATE journal mode instead of DELETE. See here for more details: http://www.sqlite.org/pragma.html#pragma_journal_mode

In this way, the rollback journal (.-journal) is not deleted at all, and thus we can get rid of the problem you said. I hope this helps.

Answer 3

Well I don't know if this will answer your question, but in my case I had a dummy error which is that I gave a wrong relatif URL to the database file when I copied my code from a commandline projet to the mvc one. Hope someone will find this useful.

Answer 4

It wasn't a permissions problem. The problem is that I didn't check that |DataDirectory| actually resolved to the AppData directory like it's supposed to.

On the IIS 7.5 server, |DataDirectory| resolves to C:\inetpub\wwwroot\AjaxSource\App_Data, but the actual AppData is directory is C:\inetpub\wwwroot\AjaxSource\bin\App_Data.

The errors I caught using Fiddler never mention any paths, but imply a permissions problem. I had one of my views display the resolved directory with:

<h1>@AppDomain.CurrentDomain.GetData("DataDirectory")</h1>

(Oddly, this isn't documented).