I have a SQL Server that's configured to use Windows authentication. The database administrator uses AD users and groups to grant / deny permissions on certain tables and procedures.
I also have web apps that communicate with ASP.NET Core Web APIs. These apps uses ADFS for authentication, so basically all users are registered into AD.
In a few of those APIs, I want to access data from SQL Server on behalf of the authenticated AD user, which means I can't use SQL Server authentication. I also can't use the integrated security mode, as it will then assume the identity of the account that runs the ASP.NET Core service.
My next thought was that I should try punctually impersonating the user, but I couldn't find a single working example of transforming an ADFS token into a WindowsIdentity object.
So how should I go with this? Can anyone point me in the right direction?
Part of the idea of ADFS is that it provides a token that ISN'T your Windows credentials on purpose. Imagine if you federated your domain with another service's so your users could login, but whatever service you allow it to connect to can now impersonate your windows credentials? They could use that to gain access back to your network.
Now, if your Web API runs under an account with sufficient permissions, you could simply have it impersonate the user based on username.
With that said, I can't say that it is the recommended way of securing database access, and you should really just have a SQL account for the Web API to access that database and provide information to the end user.
You can set up what's called Identity Delegation where your web app would request another impersonation token from ADFS on behalf of your end user.