How to solve [FireDAC][Phys][SQLite] ERROR: unable to open database file, When app is installed in programFiles?

Question

I developed a tool using Firedac with the database as SQLite.

after finishing the project and making an installer (InnoSetup) I get an error

[FireDAC][Phys][SQLite] ERROR: unable to open database file

when I launch the app (double click).

This is the connection parameters I use

constructor TDbInteract.Create(const aDatabasePath: string; const aOnNeedCredentials: TOnNeedCredentials);
var
  aParams: array of string;
begin
  if not TFile.Exists(aDatabasePath) then
    raise Exception.Create('Database file not found');

  aParams := ['DriverID=SQLite',
              'Database=' + aDatabasePath,
              'OpenMode=CreateUTF16',
              'LockingMode=Normal',
              'JournalMode=WAL',
              'StringFormat=Unicode',
              'Synchronous=Full',
              'UpdateOptions.LockWait=True',
              'BusyTimeout=30000',
              'SQLiteAdvanced=temp_store=MEMORY;page_size=4096;auto_vacuum=FULL'];
  InitiateResource(aParams, aOnNeedCredentials);
end;

procedure TDbInteract.InitiateResource(const aParams: array of string; const aOnNeedCredentials: TOnNeedCredentials);
var
  I: Integer;
  Credentials: TStringDynArray;
begin
  FRowsAffected := 0;
  FIsForeignKeyHonored := True;
  FOwnsResultDataSets := True;
  FDataSetContainer := TDataSetContainer.Create(nil);

  FConnection := TFDConnection.Create(nil);
  try
    for I := Low(aParams) to High(aParams) do
    begin
      FConnection.Params.Add(aParams[I]);
    end;

    if Assigned(aOnNeedCredentials) then
    begin
      aOnNeedCredentials(Self, Credentials);

      for I := Low(Credentials) to High(Credentials) do
      begin
        FConnection.Params.Add(Credentials[I]);
      end;
    end;

    FConnection.Open;
  except
    raise;
  end;
end;

**Identified problems:

I read somewhere (do not remember the page I was in) that SQLite engine requires full lock on the directory that it wants to write to. and this is the problem. How ever I run the tool as invoker and my account is an admin so that is not a problem. Also I have the same tool written in c# and this problem never occurs.

Solutions I found:

Run the tool as administrator
Do not Install the tool in ProgramFiles directory

I really don't like these solutions. and would like to run my tool from program Files directory as it is part of a bigger project.

Note: The database file is in programdata directory. It is created by the tool (this works).

Edit: I just tried putting the DB file in C:\Users\Nacereddine\AppData\Roaming\MyTool And I still have the same problem when the tool is installed in C:\Program Files (x86)\MyTool

This how I create the DB file

class procedure TDbInteract.CreateSQLiteDb(const aDatabasePath: string; const aTables: TStringDynArray);
var
  I: Integer;
  aParams: array of string;
  aConnection: TFDConnection;
begin
  aParams := ['DriverID=SQLite',
              'Database=' + aDatabasePath,
              'OpenMode=CreateUTF16',
              'LockingMode=Normal',
              'JournalMode=WAL',
              'StringFormat=Unicode',
              'Synchronous=Full',
              'UpdateOptions.LockWait=True',
              'BusyTimeout=30000',
              'SQLiteAdvanced=temp_store=MEMORY;page_size=4096;auto_vacuum=FULL'];

  aConnection := TFDConnection.Create(nil);
  try
    for I := Low(aParams) to High(aParams) do
    begin
      aConnection.Params.Add(aParams[I]);
    end;

    aConnection.Open();

    for I := Low(aTables) to High(aTables) do
    begin
      aConnection.ExecSQL(aTables[I]);
    end;
  finally
    aConnection.Close;
    aConnection.Free;
  end;
end;

Note: I do not know if this makes any difference but the Db file is encrypted.

Non-admin accounts cannot write to either Program Files or ProgramData. Put your DB in a writable location under C:\Users. You're fighting with the OS by attempting to violate security rules. The OS will win. — Ken White
There can be reasons for having the database together the program, even in Program Files directory. The solution to that is to create a directory underneath your program code directory and - during installation - make this directory (and only this directory) Read/Write for standard users. That way you achieve the same level of security as if you had placed the database in the Users directory structure, but maintain the location of the database together with the program code. — HeartWare
@HeartWare Thank you for the Idea. It is just not the case, we had the habit to put our software in ProgramFiles and Its data in ProgramData. We never had a problem with this when developing with c#. Only Delphi so I wonder what is going on. Or have we got it by chance? — Nasreddine Galfout
@NasreddineGalfout Have you read this ? stackoverflow.com/questions/26798832/… It looks like your problem could be that you don't run the installer with administrator privileges so it can't grant permissions to the ProgramData folder. — Marc Guillot

Nasreddine Galfout Nasreddine Galfout · Accepted Answer · 2019-06-17T12:53:02

Sorry for the trouble folks.

The problem was that we had a Localization db file installed with the tool in ProgramFiles.

What made me exclude that from my investigation is that, when opening this file I set the OpenMode to ReadOnly

FConnection.Params.Add('OpenMode=ReadOnly');

but as I said before in my question SQLite engine requires full access to the folder containing the db file so it preforms a lock on it (still did not find the page I read this on).

I checked this by playing around with open modes and debugging the tool each time. once I changed the permissions of both the file and the directory the error was gone.

at the end I decided to move the localization file to the programData directory with the main db file and all is well.

I realized (Thank you for this @Ken and @David) that the programData directory also requires admin permissions to write to, and therefore I will move the db files to a more appropriate dir (i.e Users).

What is useful from this problem is that even if you connect to the Sqlite db file with OpenMode=ReadOnly, you still need write access for the path to that file.

How to solve [FireDAC][Phys][SQLite] ERROR: unable to open database file, When app is installed in programFiles?

1 Answers