Resolving the dreaded "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance"

So you're a developer using Visual Studio 2019 Community Edition and want to demonstrate a a basic n-tier website that round trips to a database.

To keep things simple you decide on an ASP.NET MVC web app with small variations from the defaults:

1. Individual User Account authentication instead of None.
2. IIS instead instead of IIS Express
3. SQL Server Express instead of localdb.

Things get much more complicated from this scenario but in production it'll be IIS not IIS Express. The database layer will be SQL Server but SQL Server Express is a closer match to running SQL Server in production than the user-mode localdb.

Visual Studio project wizard for an ASP.NET MVC  web app with Individual User Accounts

SQL Server Management Studio provides a GUI for administering SQL Server - just remember to turn on named pipes and TCP (illustrated below) and enable remote access.

SQL Server Management Studio

So the database is installed, a named instance (SQLExpress) is running and clients can connect remotely. Next problem:

SqlException! "failed to generate a user instance of sql server due to a failure in starting the process for the user instance..."

This can be fixed by updating the connection string (either set User Instance=True to False or remove it) in web.config.

Next problem

IIS' default web site uses the default application pool (a pool of processes to answer requests). For security reasons DefaultAppPool is a low privileged identity. Formally it's identity is "IIS APPPOOL\DefaultAppPool" (without the quotes). This post covers how to add IIS APPPOOL\DefaultAppPool to SQL Server.

Creating a SQL Server login for DefaultAppPool (I simplified the user name in the database)

Next problem

The login couldn't create database.

Error - we were unable to create a database on this instance of SQL Server

This is fixed by giving it permission to create a database

use [master]

GO

GRANT CREATE ANY DATABASE TO [IIS APPPOOL\DefaultAppPool]

GO

In production this will typically be a windows identity that's tied to an application or a user, not the default app pool because it's a powerful grant that can take down an entire SQL Server (not just a single database).

The login we created gets mapped to a user in each database. So it needs to be able to create tables, read data and write data. These can be added from SQL Server Management Studio:

Once our application pool identity has been granted these privileges it can create the ASP.NET database and tables used to support Individual Accounts.