_SQL Server Express_ is just a free (as in beer) limited version of _SQL Server_. It's still a full-blown traditional server like MySQL, Oracle and other that require services and administration tools to be installed.
Mirosoft has 2 equivalents to SQLite: The Jet database engine is pre-installed in all versions of Windows and allows creation of .mdb databases (those used by older versions of MSAccess). You don't need to include any dll file to use it.
The other is _SQL Server Compact_ which, like SQLite, is an embedded engine that you can bundle with your application by including a library.
All of these embedded databases are able to do multi-user writes to some extent. Jet is actually quite good if careful with locking (emphasis on careful). SQLIte is a de-facto standard because it's simple, performant, cross-platform and flexible.
While it can replace full-blown databases in some cases, it's far from being always true. There are still many cases where using something like SQL Server Express may make more sense, for instance if you want to offer a path to your customer for drop-in replacement of the database based on the growth of their needs over time. Not saying it's not a costly lock-in, but it's an easy one to sell.
I also don't find the admin side much of a faff. Provide your users a backup/restore option within your app and ensure you install your own instance.
It's really not that hard to imagine. There is an embedded library version of MySQL called libmysqld which is made for this very purpose. Of course as you point out it's quite overkill for something that would work perfectly well with Berkeley DBs.