Windows authentication for remote user in ASP.NET Core application with Entity Framework

Background

I have an ASP.NET Core application running in Http.sys to provide Windows authentication.

The client side of my application works with Windows authentication and users are prompted for their Windows login credentials when first accessing the site. All good with that.

I am using a SQL Server database (hosted on the same server as the app) which is built by Entity Framework using the code first approach. In the startup.cs file I am running a dbContext.migrate() to ensure the database is up to date and then the app continues as normal.

The Issue

When a remote user is interacting with the site and performs an action that requires database access, I would like that database transaction to be performed under their username.

For instance, if I run the Asp.net core application on my server as User1 and then access the webapp from a user's machine who is logged in as User2 the database interactions are still being performed by User1 as that is the user the main application is running under.

I would like for it to be possible to make the database interactions be performed by User2 instead of User1 in this instance. Mainly for the purpose of logging and analysis.

When retrieving an instance of the dbContext I am supplying it with a connection string that states to use SSPI for it's authentication. Is there a way to include the remote user's username into this connection in order for them to connect to the database as themselves? I can easily retrieve the remote user's credentials/username from my controller. I believe I can use something called Impersonation but I am unsure of the best practices of implementing that or if it is a recommended solution.

Any assistance will be greatly appreciated.

Update

Thanks to some useful feedback I have tried to implement the following:

My connection string is set as so in the startup.cs:

services.AddDbContext<MyDbContext>(options => options.UseSqlServer(@"Server=MyServer\MSSQLSERVER;Database=MyDatabase;Trusted_Connection=True;"));

Then I perform an action such as this in my controller/class:

        var values = new List<Values>();

        WindowsIdentity.RunImpersonated(user.AccessToken, () =>
        {
            var optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();
            optionsBuilder.UseSqlServer(@"Server=MyServer\MSSQLSERVER;Database=MyDatabase;Trusted_Connection=True;");

            using (var ctx = new MyDbContext(optionsBuilder.Options))
            {
                values = (from row in ctx.Table select row).OrderBy(x => x.Id).ToList();
            }
        });

The user variable is passed into the function and is of type WindowsIdentity.

Executing the code from localhost works fine but then when a remote user tries it returns the following error: System.Data.SqlClient.SqlException: 'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)

Update 2

I managed to resolve this error by using an IP address for the connection string instead of the server/machine name. So now my connection string looks like this: @"Server=<IP address>\MSSQLSERVER;Database=ConfigDb;Trusted_Connection=True

Now I am facing another issue where the SQL Server is stating that No connection could be made because the target machine actively refused it. I can connect to the SQL Server using this user through SQL Management Studio but not through my app.

I found in EventViewer that the actual error is related to SSPI: SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The operating system error code indicates the cause of failure. The logon attempt failed [CLIENT: <named pipe>]. Not really sure why this would be failing.

ASP.NET Core Docs cover this exact topic.

Simply put, ASP.NET Core does not implement impersonation. It has an example of a workaround using WindowsIdentity but it has its limitations and should only be used for relatively simple operations.

There is also a NuGet package which may help you get some more functionality, but still is limited.