Unable to run fresh Umbraco project out of the box when Development database type is SQLite or SQL Server express LocalDB

[miguelcrpinto]

Which Umbraco version are you using? (Please write the exact version, example: 10.1.0)

12.0.1+ and 10.6.1+

Bug summary

Creating a new project via Umbraco.Templates and with the "Development database type" option set to SQLite or SQL Server express LocalDB and with no Unattended user details fails in all versions since the release of the security patch released on the 13th of July.

Specifics

Whenever you create a new Umbraco project with an Umbraco version released after 12.0.1 or 10.6.1, if you select SQLite or SQL Server express LocalDB as "Development database type" (instead of the None option) and do not provide Unattended user details, whenever you try to run the project it first throws an exception related with missing the media folder (the issue mentioned in issue 14877). If you create the media folder manually and run the project again, it throws an exception saying it could not open the database file.

From the behavior it seems that the Umbraco install process is skipped because there's a connection string set in the appsettings.Development.json and therefore Umbraco tries to start from the database that does not yet exist.

Screenshot of the missing media folder exception:

Screenshot of the exception displayed after creating the media folder

Log file: UmbracoTraceLog.MIGUEL-LAPTOP.20231010.json

List of affected versions

• 10.6.1
• 10.7.0
• 12.0.1
• 12.1.0
• 12.1.1
• 12.1.2
• 12.2.0

Versions 11.4.2 and 11.5 are probably also affected but I did not test them as they are not supported anymore.

Steps to reproduce

1. Install the Umbraco.Templates of any Umbraco version listed in the specifics
2. Create a new Umbraco project (based on the templates from Umbraco.Templates) in Visual Studio with the same options as in the image below
3. Run the project (without any modifications) in Visual Studio (pressing F5)

Visual Studio project options selected:

Expected result / actual result

A project created based on a template from the Umbraco.Templates should run out of the box with the selected options. It's not a nice experience, specially for people just starting with Umbraco.

Some options to consider:

1. Whenever the "Development database type" option set to SQLite or SQL Server express LocalDB, make the unattended user details mandatory
2. Whenever the "Development database type" option set to SQLite or SQL Server express LocalDB, if no unattended user details are provided, do not add the ConnectionStrings to the appsettings.Development.json

[github-actions[bot]]

Hi there @miguelcrpinto!

Firstly, a big thank you for raising this issue. Every piece of feedback we receive helps us to make Umbraco better.

We really appreciate your patience while we wait for our team to have a look at this but we wanted to let you know that we see this and share with you the plan for what comes next.

• We'll assess whether this issue relates to something that has already been fixed in a later version of the release that it has been raised for.
• If it's a bug, is it related to a release that we are actively supporting or is it related to a release that's in the end-of-life or security-only phase?
• We'll replicate the issue to ensure that the problem is as described.
• We'll decide whether the behavior is an issue or if the behavior is intended.

We wish we could work with everyone directly and assess your issue immediately but we're in the fortunate position of having lots of contributions to work with and only a few humans who are able to do it. We are making progress though and in the meantime, we will keep you in the loop and let you know when we have any questions.

Thanks, from your friendly Umbraco GitHub bot :robot: :slightly_smiling_face:

[vsilvar]

This issue was introduced with the following security fix: https://github.com/advisories/GHSA-h8wc-r4jh-mg7m

The change also made it so this is effectively dead code: https://github.com/umbraco/Umbraco-CMS/blob/contrib/src/Umbraco.Infrastructure/Runtime/RuntimeState.cs#L218-L224 And so is the InstallMissingDatabase appsetting, which now does nothing.

The code and appsetting should be removed, the documentation updated and the template updated as Miguel suggests. Or alternatively simply update the logic to something like:

if (hostingEnvironment.IsDevelopment() && (_globalSettings.Value.InstallMissingDatabase || _databaseProviderMetadata.CanForceCreateDatabase(_databaseFactory)))
{
    // ok to install on a configured but missing database,
    // but only allow it for local development as it is inherently insecure
    Level = RuntimeLevel.Install;
    Reason = RuntimeLevelReason.InstallMissingDatabase;
    return;
}

Which should still address the security vulnerability but retain the functionality for local development.

[andr317c]

Hey! Thanks for posting this issue with a lot of good information 💪 I was able to reproduce it, I tested it on versions 12.2.0, 11.5.0, and 10.7.0. I will bring this issue up with the team to discuss how we want to handle this. I'll come back when we have come up with a solution! 😄

[ronaldbarendse]

This issue was introduced with the following security fix: GHSA-h8wc-r4jh-mg7m

Enabling ForceCreateDatabase for SQL Server connection strings started a cascading effect (see https://github.com/umbraco/Umbraco-CMS/pull/14234#issuecomment-1625477875), which indeed introduced changes to how the unattended install worked and further changes to fix the side-effects... I like the suggestion of only allowing the creation of a new database on specific environments, but that's partly why the InstallMissingDatabase exists (which can already be configured per environment). The only thing it couldn't do, is explicitly disable this behaviour for database providers that have (correctly) enabled ForceCreateDatabase (like the embedded SQL LocalDB and SQLite databases), but that could be fixed by making the InstallMissingDatabase nullable, so false completely disables creating the database, null makes it dependent on the CanForceCreateDatabase() check of the current database provider and true always enables it.

[miguelcrpinto]

@ronaldbarendse will HQ fix this or will it be tagged as an up for grabs?

[andr317c]

Hello again!

Apologies for the delay in getting back to you. We're going to mark this issue as up-for-grabs. The solution for this issue should align with the suggestions mentioned in the comment: https://github.com/umbraco/Umbraco-CMS/issues/14956#issuecomment-1782533652

[github-actions[bot]]

Hi @miguelcrpinto,

We're writing to let you know that we would love some help with this issue. We feel that this issue is ideal to flag for a community member to work on it. Once flagged here, folk looking for issues to work on will know to look at yours. Of course, please feel free work on this yourself ;-). If there are any changes to this status, we'll be sure to let you know.

For more information about issues and states, have a look at this blog post.

Thanks muchly, from your friendly Umbraco GitHub bot :-)

[nagolucky18]

I can implement the solution from the earlier comment.

[nagolucky18]

I created a draft pull request for this. The pull request contains changes that make the InstallMissingDatabase nullable. The changes successfully resolved the issue but I could not find a way to actually set InstallMissingDatabase to null via the AppSettings config files. This is because when the JSON is parsed, each setting pair is converted to a string. The null ToString becomes the empty string in this case. I verified the solution itself works by setting the default to null, but a null default would not be ideal here.

Any suggestions for this? Maybe null can be passed in via string value of "null"?

This does not resolve the secondary issue of installing without unattended user details. From my testing with this solution, if the user installs unattended, with no user info, now instead of crashing, the app will be installed but the user cannot log in because no credentials were ever set up.

[nagolucky18]

It's been a while since I've looked at this, but to address above comment while keeping changes as minimal as possible, I changed the default value of InstallMissingDatabase to null. Because null is not a valid static constant, the static constant for InstallMissingDatabase was removed.

Because of the way that this change works, if a setting is not specified in the appconfig, the default is null, however if the user supplies a value in appconfig, only true or false are acceptable values due to aforementioned issue with toString. I'm still open to other ideas to solve this.

[frederiktoft]

Just want to chime in that I just got this issue today, fresh 13.2.0 install using the following guide: https://docs.umbraco.com/umbraco-cms/fundamentals/setup/install/visual-studio Also with installing the latest templates. Adding the media folder manually does not fix the error and instead produces this:

Umbraco.Cms.Core.Exceptions.BootFailedException: Boot failed: Umbraco cannot run. See Umbraco's log file for more details.
   at Umbraco.Cms.Core.Exceptions.BootFailedException.Rethrow(BootFailedException bootFailedException)
   at Umbraco.Cms.Web.Common.Middleware.BootFailedMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.InterfaceMiddlewareBinder.<>c__DisplayClass2_0.<<CreateMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at SixLabors.ImageSharp.Web.Middleware.ImageSharpMiddleware.Invoke(HttpContext httpContext, Boolean retry)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)

I got this from the log that could be the culprit:

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 14: 'unable to open database file'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteConnectionInternal..ctor(SqliteConnectionStringBuilder connectionOptions, SqliteConnectionPool pool)
   at Microsoft.Data.Sqlite.SqliteConnectionPool.GetConnection()
   at Microsoft.Data.Sqlite.SqliteConnectionFactory.GetConnection(SqliteConnection outerConnection)
   at Microsoft.Data.Sqlite.SqliteConnection.Open()
   at Umbraco.Extensions.DbConnectionExtensions.IsAvailable(IDbConnection connection)

I made a new project with version 13.1.1 and still the same issues. Quite annoying really not being able to create Umbraco projects that works out of the box.

[WiseyD]

I'm having this issue as well. While attempting to create a new Umbraco CMS Project via Umbraco Templates in Visual Studio 2022 with SQL Lite database type selected. New project, template version 13.2.0. The build completed, hit F5 in visual studio to run the installation wizard getting the same directory not found exception for '\wwwroot\media'.

EDIT ~ Update: I was able to resolve the issue by leaving the connection string provider name blank and setting the development database type to none while setting up the initial project in visual studio.

[JasonElkin]

The problem here is not what you might think it is...

The OoTB behaviour is actually a logical problem with the template and how the choices made build the appsettings.Development.json.

And it's made worse by an interesting quirk of VS that we'll get back to...

When "Development database type" is set to anything other than "None", a connection string gets added to appsettings.Development.json. Here's the JSON that controls that.

Now, here's the important bit:

This on its own does not mean the unattended install will actually run - that's also controlled by the "Unnatended..." options. If nothing is set in these, unattended will not run.

UnattendedUserName != '' 
&& UnattendedUserEmail != '' 
&& UnattendedUserPassword != '' 
&& (HasConnectionString || HasDevelopmentConnectionString)

So, when Umbraco boots it sees the connection string and skips the install process entirely. Umbraco thinks it's already been installed but, of course, there's no database there... 💥

The initial fix is simple, just make sure you don't set the development database to anything, and Umbraco will work as expected:

It's even the default... except, here's the thing...

Visual Studio remembers your choices here!

If you've ever changed the value of that dropdown from None to SQLite (and gone through with the installation) then Visual Studio will remember your choice for next time 🤯

The fix..

We just need to use the same logic that we use for determining if we're going to run an unattended install to decide whether or not to actually include the connection string.

I'll see if I can get a PR in a bit later.