Pass Windows credentials to SQL Server

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?

Hi @kieran,

I am afraid I don't know how to get the current windows user to be available at SQL server level. It would probably involve impersonation but we haven't tested it. I recommend checking for solutions online.