>All,
>
>We have a flagship app using C/S backend that now needs stand-alone capability- so that an auditor or other user can be sent an "extract" file on usb stick/download/email to be opened and processed on their machine without access to the customer's firewalled environment or anything else.
>
>The app uses Remote Views and SQL Passthrough so the goal is to re-use the C/S model, ideally with just a different dbc. For the standalone version there's no SP, SQL is quite standard and only one user at a time.
>
>The immediately obvious response is to use local views against dbfs for this purpose but that creates several risks, e.g. expensive support if one or more dbf/fpt/cdx files is inaccessible/missing on the user's PC for whatever reason but more importantly, dbf content has no protection. The app's subject matter is covered by privacy legislation in every market we look at, so dbfs are not viable if they're copied to external machines. Not keen to re-invent wheels or manage encryption ourselves, so we're hoping for a commercial option for what ought to be a fairly common need.
>
>SSCE and SQLite both seem to allow single-file databases with security and ODBC drivers, meaning existing C/S model can be used.
>
>SSCE seems to involve formal installation that experience suggests will be a support burden on random Windows machines of middle-aged auditors professing lack of computer expertise. However, SQLite seems to wrap all processing in the available 3rd party ODBC driver that accesses the database file directly- meaning if you can install ODBC, it should work.
>
>Does anybody have any advice or experience or better options of a lightweight database with ODBC driver, single database file and security? At this stage I'm thinking SQLite based on trawls of mostly several-year-old threads.

At first glance SQLite looks like a good solution for your stated requirements. Whil Hentzen offers a quick start guide at https://www.whilhentzen.com/artpres/201203SQLite-Intro_GettingStarted.pdf .

I'm not sure how encryption is supported. It's not in the base product, instead there are various add-ons i.e. https://stackoverflow.com/questions/5669905/sqlite-with-encryption-password-protection . According to that post the SQLite Encryption Extension (SEE) is the "official" implementation but it's not free, you need to purchase a $2K license (no royalties): https://www.hwaci.com/sw/sqlite/see.html . Some of the alternatives apparently have free options.

On a quick search I could only see 2 SQLite ODBC drivers:

- SQLiteODBC http://www.ch-werner.de/sqliteodbc/ . This is the package WH references (free)
- Devart https://www.devart.com/odbc/sqlite/

The SQLiteODBC documentation page makes one reference to SEE: "added infrastructure for building with SEE". Since SEE needs to be licensed I highly doubt SQLiteODBC is built with it by default. If you license SEE you would need to recompile it with the nominal SEE support. I suspect the SQLiteODBC precompiled distributables don't support database encryption.

The Devart driver includes encryption which is cool, but it's US$150 per license (machine). I didn't see any offer of royalty-free distribution but you could contact them.

Apparently there is encryption support for SQLite in .Net: https://stackoverflow.com/questions/1381264/password-protect-a-sqlite-db-is-it-possible . Maybe use some interop magic from Rick Strahl (?)

***
Some other spitballs:

Excel: if extracts are very simple/flat a password-protected Excel file and an Excel ODBC driver might work

Containers: you could containerize what would normally be a complex installation. I haven't worked with it yet, apparently Docker containers will run on Win10 Pro or higher

RDS: put up the extracts, and the environment needed to view them on an RDS server. You wouldn't have to re-tool your app, just limit the data it can see on a per-user basis. Multiple RDS clients available: Windows, Mac, I believe Android and iOS as well. Main limitations are not true standalone (requires internet connection) and not suitable for working interactively with large graphics files

If you need a local app I haven't heard of any better option than SQLite, I'd be interested in hearing any other ideas.

As for SSCE, from its Wiki page it doesn't look like as good a fit as SQLite: https://en.wikipedia.org/wiki/SQL_Server_Compact . It's been deprecated since 2013 and has a 4GB database size limit. It's also designed to run in-process, I'm not sure how you would access it via ODBC: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/320df64a-60d8-4865-97f2-cc42fef628b4/odbc-driver-for-sql-server-compact-edition?forum=sqlce