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

[ilanc]

I'm not at work at present. I'm pretty sure my server is win 2008 r2. I'll check tomorrow. Client is win7 & Chrome

[ilanc]

Could you try a high numbered port too just out of curiosity say 4000

[saurabh500]

@ilanc With port 5191

With Port 4000

[saurabh500]

@ilanc I am going to try to host the application on Windows Server 2008 R2 and see what happens there.

Meanwhile do you have Office installed on your IIS server machine? Or do you have Microsoft Online Services Sign-In Assistant installed on the server hosting the IIS app?

Do you have Microsoft Online Services Sign-In Assistant installed on the server hosting the SqlServer?
I thought that this could be related to another issue that I was dealing with. However after thinking this through, your integrated auth succeeds in some scenarios, so disregard the above struck out questions

[saurabh500]

I ran the app on Windows Server 2008 R2 in IIS 7.5 and the Sql server was installed on Windows Server 2012 R2. I could repro the issue mentioned above.

When I moved the Sql server to the same machine which as IIS hosted on it, the issue doesn't repro.

I hosted the app on Windows Server 2012 R2 and the Sql Db on windows server 2008 R2. I could repro the issue again. I think this has somethign to do with cross machine Integrated Authentication.

[saurabh500]

@ilanc are you IIS server and Sql Database located on separate instances of operating systems?

[ilanc]

Hi @saurabh500. Yes they are separate machines. I'll find out what OS is on each server in a few hours. Thanks that was well tracked down

[ilanc]

The sqlserver is windows server 2012 (see below) and the IIS server is Win 2008 R2 Enterprise w sp 1

SELECT windows_release, windows_service_pack_level, windows_sku, os_language_version FROM sys.dm_os_windows_info
windows_release windows_service_pack_level  windows_sku os_language_version
6.2     7   1033

and windows_release 6.2 = windows server 2012 or windows 8 according to https://msdn.microsoft.com/library/ms724832(vs.85).aspx

[ilanc]

Of course this is just to help track down the problem. In the long run we will mix and match windows types depending on the application - there are many other databases and webservers on our network.

[saurabh500]

@ilanc There are a bunch of questions and posts online which describe this same issue and there are different solutions which talk about either modifying the application or establishing Trusted delegation between machines. Can you explore such posts?
I found a blog https://blogs.technet.microsoft.com/taraj/2009/01/29/checklist-for-double-hop-issues-iis-and-sql-server/ which talks about solving this issue in detail. The blog however is targeted at Windows Server 2003. This has been cross referenced in some SO posts as well. This issue is called the Double Hop issue between IIS and Sql Server and I suspect it doesn't have to do with the .Net framework.

If you don't find a solution, I recommend you reach out to MS Support or MS connect and report the issue there to get a resolution.

[ilanc]

Thanks @saurabh500. I'll look into those posts. I tried MVC5 and confirmed that it has the same behavior. The impersonation stuff is new to me and seems quite useful (provided I can get it to work). It's a nice-to-have though - I have workarounds.

[ilanc]

Hi @saurabh500. Apologies for reviving this. I now have a windows 2012 R2 web server. From your comments above I thought that impersonation may "just work" if the OS was the same on the 2 servers (SQLSERVER and IIS server). However running the same github app above I still see that the SQL connection is still made as the app pool user (i.e. I get SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.).

Can I confirm did you get it working with 2 x servers? (i.e. computer 1 = browser -> server 1 = iis -> server 2 = sqlserver - and the connection from server 1 to server 2 was made as the user on computer 1 not the user which iis runs as) or did it only work with 1 server? (i.e. when IIS and SQLSERVER were hosted on the same server).

My workaround was to run iis as a fixed user account and grant this account access to the relevant tables - but my company would prefer an impersonation solution.

[DickvdBrink]

@ilanc, did you ever figure this one out? I'm having the same issue here currently.

[ilanc]

Hi there. One of our infrastructure staff enabled the impersonation stuff on our domain and it's now working. I'll chase him for detailed step by step instructions. There are a number of posts (admittedly all quite old and out of date) on setting it up. You need various admin permissions on your domain hence I couldn't do it on our network. use the github project I posted above as a test case till you have impersonation working and change the sql code to return the username of the current connection. I'll update the github project tomorrow with that code.

[ilanc]

@DickvdBrink I updated the demo app here: https://github.com/ilanc/AspNetImpersonate

I'm missing the exact steps to enable double-hop/impersonation on the domain. If I hear back from IT I'll post them here. I include a bunch of links in the readme at the above repo.

[RobertMcCoy]

@ilanc Thanks so much for this. I spent ridiculously too long trying to figure out how to impersonate double-hop calls from MVC -> Web API with integrated Windows Authentication. Definitely a solution to this issue, it's working great for my entire web application.

[ilanc]

@RobertMcCoy no problemo. Have you got a list of setspn etc.. commands that you used to setup the impersonation between MVC and WebAPI? I assume these were running on 2 separate web servers?

[RobertMcCoy]

@ilanc Both are running on the same server. I'm not familiar with the setspn command. My company has a large AD service setup, and I mostly follow what already exists in that for AD Group authorization/authentication.

[daddydrac]

How about MSFT starts ditching MVC examples, and focuses on REST based ones w/ LDAP/WinADGroups/WinAuth + JWTs: Where front end is 100% decoupled from backend. I mean, its better design for scalability and security purposes. MVC is old and outdated - take heed that aint nobody got time fo' dat! #Rx #Reactive #Ng #FullyDecoupledUI

[madrianr]

Hello,

I'm not sure but is "aspnet core app with windows auth and Trusted_Connection connectionString" running now with ASP.NET Core 2.0 or not?

I use a ASP.NET Core 2.0 (targeting NET 4.6.1) and the following Code (Middleware) to have the current user logon to sqlserver but always the 'NT AUTHORITY\ANONYMOUS LOGON' connects to the sqlserver...

public class Impersonate
    {
        private readonly RequestDelegate next;

        public Impersonate(RequestDelegate next)
        {
            this.next = next;
        }

        public async Task Invoke(HttpContext context)
        {

           var winIdent = context.User.Identity as WindowsIdentity;
            if (winIdent == null)
            {
                await next.Invoke(context);
            }
            else
            {
                await WindowsIdentity.RunImpersonated(winIdent.AccessToken, async () =>
                {
                    await next.Invoke(context);
                });

            }
        }
    }

Please help - I need to have the current user connect to sqlserver...

robert

[Tratcher]

RunImpersonated is not async so that middleware does not work. Where did you get that?
No, there have been no improvements here for 2.0.

[madrianr]

Hi, after some investigation the code in my last post works (app.UseMiddleware();)
It was necessary to set the SPN on the Domain Controller for the Webserver like:

setspn -a HTTP/web  webserver
setspn -a HTTP/web fully qualified webserver

ie.

setspn -a HTTP/Test testwebserver
setspn -a HTTP/Test.domain1.intern testwebserver

and in IIS only Windows Authentication with Negotiate and NTLM...

robert

[Tratcher]

ASYNC IMPERSONATION IS A TRAP. It will only work if your application pipeline runs synchronously. I'm going to start removing comments / samples that propose it so others don't make the same mistake.

Impersonation should only be done at the call site where it's required, and that action must be completed synchronously within the RunImpersonated call.

[madrianr]

Hi,

Does this mean it is not possible to make a database call "async" like this?

public async Task<ActionResult> Read(int mitgliedid, string textfilter, [DataSourceRequest]DataSourceRequest request)
        {
            var callerIdentity = User.Identity as WindowsIdentity;
            WindowsIdentity.RunImpersonated(callerIdentity.AccessToken, () =>
            {
            IList<MitgliedkontaktViewModel> result = await Db.QueryAsync<MitgliedkontaktViewModel>("SELECT_Mitgliedkontakt", new { Mitglied_ID = mitgliedid }, commandType: System.Data.CommandType.StoredProcedure);
            return Json(await result.ToDataSourceResultAsync(request));
            });
        }

there are so many posts which make it not clear how to use it the right way...

in my application it is important that every database call is made by the current logged on user, so it would be nice if Microsoft can clarify, how to do this in an ASP.NET MVC Core 2 Applicatuion the right way!

robert

[Tratcher]

Correct, async operations cannot be combine with impersonation.

@divega accessing the DB as the authenticated user is the most requested use of impersonation. Does EF have other ways to control the authentication process? Or other recommendations for authorizing individual user access to the database?

[madrianr]

Ok - to really clarify that:

• it is only possible to use impersonation in Sync Actions?
• I have to add the code for impersonation in every action where I want to make a database call?

if this is true, this is a great disadvantage of ASP.NET Core and contradict with the Middleware logic of ASP.NET Core - isn't it (so many repeated code)?

so this Action should work?

public IActionResult Read(int mitgliedid, string textfilter, [DataSourceRequest]DataSourceRequest request)
        {
var callerIdentity = User.Identity as WindowsIdentity;
            WindowsIdentity.RunImpersonated(callerIdentity.AccessToken, () =>
            {
            IList<MitgliedkontaktViewModel> result = Db.Query<MitgliedkontaktViewModel>("SELECT_Mitgliedkontakt", new { Mitglied_ID = mitgliedid }, commandType: System.Data.CommandType.StoredProcedure);
            return Json(result.ToDataSourceResult(request));
}             
    }

[Tratcher]

Yes, that action looks correct.

Yes middleware would be a nice way to deal with this in a central location, but we need new async impersonation APIs from .NET Core to make it work correctly. See https://github.com/dotnet/corefx/issues/24977

[divega]

@Tratcher sorry for the delayed response.

Does EF have other ways to control the authentication process? Or other recommendations for authorizing individual user access to the database?

No, EF Core (as well as previous versions) is agnostic to this concern, i.e. it assumes that the underlying database connection will be capable of accessing the database.

Do you know that the minimal thing is that needs to run in the delegate passed to RunImpersonated? Is it just opening the connection or also any command execution?

Also, I think the code snippets from @madrianr are using Dapper, not EF Core 😸.

[Tratcher]

I don't know how the SQL auth protocol works. I assume it's per connection rather than per command. It would be pretty obvious if we could look at the src for the client.

[madrianr]

We use Insight.Database (https://github.com/jonwagner/Insight.Database) not Dapper but it is simmilar...
robert

[Eilon]

Closing this issue because it isn't clear to me if there is an issue remaining. If you feel there's still an issue, please re-open this issue or log a new issue with complete details of the problem. Thanks!