My use case requires me to create an intranet application connected to a SQL Server database. Row level security will be applied to tables on the database - for this to work I need to pass the authenticated user's credentials down to the database layer.
I am having trouble getting this to work. I have created an Angular application connected to a test table in my SQL source database. At this point there is no row level security on the table, I just want to check that I can pass the user credentials to the database. This runs fine on localhost - I am able to add records to the table and have added a default constraint to the table that lets me see who edited it, as below:
ALTER TABLE [dbo].[Employee] ADD CONSTRAINT [df_Employee_ModifyingUser] DEFAULT (system_user) FOR [ModifyingUser]
I then set the security provider to Windows. Now when I try and run the application locally, I get prompted for my credentials repeatedly - is this because I don't have Windows authentication enabled on my local machine? Anyway I decided to deploy to IIS and see if it behaves differently there.
On my server I am running IIS Version 8.5.9600.16384. I have a couple of existing Radzen applications running on there but these all had much simpler security requirements - open to any user on the network and connecting to the source SQL database using a fixed SQL login.
The application pool is running in Classic managed pipeline mode, .NET CLR Version v4.0.30319. The Identity is set to a service account that has access to the underlying database.
In this mode it works to a point - I can view the data and add/edit records. However when I check my database everything is being logged under the service account, not the account of the application user.
I have read through various documentation about Windows authentication and user impersonation, but haven't been able to get it to work. I currently have it set up as follows:
Authentication: Set via IIS Manager, ASP.Net Impersonation and Windows Authentication enabled, everything else including Anonymous Authentication disabled.
.NET Authorization Rules: Deny Anonymous Users, Allow All Users
Connection strings: Confused by what is going on here. In IIS it just shows the defaul LocalSqlServer connection string. In appsettings.json I can see the connection string I think it is using:
{"MyDataSourceConnection":"Server=MYSERVER;Initial Catalog=MYDB;Persist Security Info=False; IntegratedSecurity=true; MultipleActiveResultSets=False; Encrypt=false;TrustServerCertificate=true;Connection Timeout=30"}
web.config: I have left this as it was when I deployed the application but added the following section to try and enable identity impersonation:
I am not sure what else to try - do you have any suggestions?