2

I'm working on a desktop app which is installed along with a database, and then connects to the database using SQL Server LocalDB when a user runs the app. The workflow is:

  1. When the app starts, it creates and starts a named, user-specific SQL LocalDB instance.
  2. When the user chooses a database, the corresponding MDF and LDF files get attached to the instance.
  3. The app connects to the database to run queries and get data.

A key paradigm in the app is that "a database is just a set of files on disk", much like SQLite or MS Access. So, one should be able to change the files in the database folder, start the app again, and it would work the same, but would now be getting data from the new database.

Some database files may be in a central location, so multiple users on the same computer could in theory all have the app open at the same time, pointing to the same database.

Luckily, the app does not need to write to the database and there is a "read-only" setting in SQL Server which happens to allow multiple SQL LocalDB instances to all attach and connect to the same database at the same time, as long as they don't write to it. The app can set this by running ALTER DATABASE MyDatabase SET READ_ONLY.

The problem is that by default, "auto-close" is on, meaning that when the app disconnects from the instance and reconnects, the database gets set back to read-write mode.

A further complication is that when a user logs out and back in again, the SQL LocalDB instance is stopped, and when the app starts it up again, the database still gets set back to read-write, even if auto-close is off.

Is there any way to keep the database in read-only mode and not have SQL Server "forget"?

Notes:

  • I doubt using a shared LocalDB instance would be feasible because of how LocalDB permissions are tied to file system permissions.
  • The drive to use LocalDB is strong, given that the installation and configuration needs to be as automated and hassle-free as possible.

Update: I am going to make the app set the read-only attribute on the MDF and LDF files before it tries to attach them. I think this solves the issue.

2
  • 3
    Have you tried making the database file read-only? Commented Jan 25, 2021 at 16:59
  • There's another complication - when I log out (so the LocalDB instance stops), log in and run my app again (so it starts and connects to the LocalDB instance again), then the database still gets reset to read-write mode, even if auto-close is off. I'll update the question to reflect this. All things considered, I may go with @DavidBrowne-Microsoft's solution of making the database file read-only, I just need to check a few things.
    – A Kubiesa
    Commented Jan 27, 2021 at 16:54

0

Browse other questions tagged or ask your own question.