Running a published aspnet core app with windows auth and Trusted_Connection connectionString

[ilanc]

Hi there,
What is the best practice for configuring an enterprise asp.net core app which uses windows active directory auth - specifically in relation to DB connectionStrings? What I was hoping for is to use Trusted_Connection (e.g. Server=XXX;Database=YYY;Trusted_Connection=true;) and to configure IIS / dotnet to run the core app using windows user impersonation (or whatever it's called) so that the core app would issue DB queries using the credentials of the user who is browsing the web app.

The core process appears to inherit the user credentials of the IIS website user (as configured in IISM > app pool & site) - which in fairness is in keeping with how IIS apps used to run. So Trusted_Connection doesn't work for application user (pass through auth) so I either have to make the IIS site run as a specific windows user or use sql user auth in the connection string (e.g. Server=XXX;Database=YYY;User ID=myUsername;Password=myPassword;)

Is there a way to do Trusted_Connection as the user browsing the website?

[Tratcher]

You should read up on these threads:
aspnet/IISIntegration#75
https://github.com/dotnet/corefx/issues/9996

[ilanc]

Fantastic that appears to work thanks @Tratcher. I'm using:

var callerIdentity = User.Identity as WindowsIdentity;
Action action = () => { ViewData["Testing"] = ($"I am {WindowsIdentity.GetCurrent().Name}!"); };
WindowsIdentity.RunImpersonated(callerIdentity.AccessToken, action);

plus in .cshtml:

@System.Security.Principal.WindowsIdentity.GetCurrent().Name
<br />
@User.Identity.Name
<br />
@ViewData["Testing"]

and getting:

IIS APPPOOL\test2 
AM\ICopelyn 
I am AM\ICopelyn!

I'll check this works with the SQL connection next.

[ilanc]

Ok it's not quite working - but I expect there's something I've done wrong. If I browse the website on the server (i.e. within a remote desktop session) then it works but not when I browse from another computer (SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.).

I'll look into it further tomorrow - think its the same bug you mentioned in one of the other threads. I'm using .net 4.5.1 btw - I see you had a solution for 4.6.

Code:

            using ((User.Identity as WindowsIdentity).Impersonate())
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand(sql, connection);
                    using (var reader = command.ExecuteReader())
                    {

[Tratcher]

This sounds like the same delegation issue I had with HttpClient. You can use the WindowsIdentity locally but not for outbound requests.

[ilanc]

My reading of https://github.com/dotnet/corefx/issues/9996 was that impersonation is working for .net 4.6. (with Impersonate()) but not .net core. (with RunImpersonated(..)) Is that correct?

I've tried .net 4.6.1 and it doesn't work with Impersonate() - it's still using user NT AUTHORITY\ANONYMOUS LOGON)

[ilanc]

Demo repo here: https://github.com/ilanc/AspNetImpersonate

[Tratcher]

@divega can you have someone try delegated windows auth with SQL client?

[Tratcher]

@ilanc you may also want to try it with WebListener instead of IIS+Kestrel. https://github.com/aspnet/weblistener

[divega]

@saurabh500 @corivera does this thread ring any bells? From https://github.com/dotnet/corefx/issues/2191 it seems that there is something SqlClient on .NET Core still needs to do to support impersonation and delegation?

[saurabh500]

@divega From the issue and the repro code, the issue is related to .Net Framework 4.6.1
@ilanc project.json content
"frameworks": { "net461": { } },

For .Net core on Windows, the WindowsIdentity.GetCurrent() API is used to retrieve the user connecting to the DataBase. If the impersonation was successful, the SqlClient should be able to get the correct user to login to the DB.

[saurabh500]

@ilanc I am running the application using dotnet run
It runs successfully on localhost:5000
I don't see any authentication request. How do I get the authentication to ask for credentials?

[ilanc]

Hi @saurabh500. Did you see the readme.md on https://github.com/ilanc/AspNetImpersonate:

1. You need to publish the code to a server and run it via iis.
2. Setup IIS to have windows auth - i.e.
   • IIS manager > your site > Authentication
   • Anonymous Authentication = Disabled
   • Windows Authentication = Enabled

Now do 2 things:

1. Browse the site from within your remote desktop connection on your server (i.e. http://localhost/home/about). Here the SQL connection will work because you're logged on as a valid windows user. NB you'll see the user in the title bar of the page - e.g. AM\ICopelyn! in my pic below.
2. Now browse the site from your machine (i.e. another machine on the same windows domain as the server - http://servername/home/about/). Now you'll get an error - demonstrating that impersonation is not working.

Working when logged in on server

Not working when remote browsing

[ilanc]

My site is setup to run as the pass-through user

[saurabh500]

@ilanc I hosted this app in IIS and enable authentication the way you have instructed above.

I visited the URL on the localhost

From a different machine

I also tried this from @corivera 's machine. I got an error message stating that the authentication for corivera failed.
Then I added corivera to the users on the SqlServer and the authentication went through and I could see the data from the server.

(I don't have screenshots for this)

For Net Framework 461 the Impersonation seems to be working right.

Let me know if I missed out any steps or if you have any other questions/instructions.

[saurabh500]

My environment
IIS server 8.5 on Windows Server 2012 R2

Client browser Chrome on OS Windows Server 2012 R2, Windows 8.1 and Windows 10