Automatic Windows Authentication over IIS, ASP.NET 5 and Microsoft SQL Server

Question

There is a lot of relevant Info, I hope I won't forget anything. We want an automatic Windowslogin into an intranet website in our domain. For testing on a client I'm using a chrome browser that has the website whitelisted. The Server runs IIS Version 10.0.19041 running a ASP.NET 5 website. It connects to a database MS SQL Server in the same domain. The Connection String has the parameter Integrated Security=SSPI and works with Apps running locally on clients.

This is what is happening for now: Normally the website would ask for credentials, but since it's in the white list I assume it now uses the Windows credentials, how it's explained here https://stackoverflow.com/a/24875309/15631999

    using (SqlConnection Verbindung = new SqlConnection(sqlConnectString))
    {
        Verbindung.Open();

the "Login failed for user" error is thrown, because it tries to connect with the active webserver user, not with the automatically logged in client. System.Security.Principal.WindowsIdentity.GetCurrent().Name returns the account identity in the AppPool.

What we did: (might not all be relevant ...)

We googled a lot the last couple of days to find out what to do. The general keywords seem to be Windows Authentication, maybe impersonation, NTLM. So we added the Authentication to the ConfigureServices. Now it looks like this:

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllersWithViews()
                .AddXmlSerializerFormatters()
                .AddXmlDataContractSerializerFormatters();

            services.AddAuthentication(Microsoft.AspNetCore.Server.IISIntegration.IISDefaults.Ntlm);

            services.AddSession();
            services.AddAuthorization(options =>
            {
                options.AddPolicy("AuthorizationMode", (policy) =>
                {
                    //unauthenticated
                    policy.RequireAssertion((e) => true);
                });
            });
            //services.AddMvc();
            services.AddHttpContextAccessor();
        }

We also tried it with IISDefaults.AuthenticationScheme and without Authorization.

Configure Function snippet:

        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            SessionOptions s = new SessionOptions();
            s.IdleTimeout = System.TimeSpan.FromMinutes(30);
            s.Cookie.SameSite = Microsoft.AspNetCore.Http.SameSiteMode.Lax;
            s.Cookie.Name = ".acclaro.de.Session";
            s.Cookie.IsEssential = true;

            app.UseSession(s);
            
            app.UseHttpsRedirection();
            app.UseStaticFiles();

            app.UseRouting();

            app.UseAuthentication();
            app.UseAuthorization();
(...)

In IIS/Sites/[Website]/Authentication we disabled Anonymous Authentication and enabled Windows Authentication. This means, I think, I don't have to edit the web.config.

We tried it both with Impersonation enabled and disabled. Basic Authentication seem to always ask for Username and PW. So this is not what we want.

We read that I have to change the order of the Providers. Putting Negotiate under NTLM, or downright deleting it doesn't change anything.

I also read that I have to add SPNs. So I executed setspn -a http/[url] [domain controller computer name] at our domain controller.

I don't know what I'm missing. Maybe I just haven't tried the right combination yet. If anyone can help, that would be appreciated. ( Also I hope impersonating other users won't produce problems, when I write in a local temp folder on the Server. I had some Access denied Exceptions in the past. )

If some relevant info is missing, please tell me.

If you want the authentication popup to open, disable anonymous authentication for that website and enable one of the other options. — Salman A, Jan 20 '22 at 12:59
I already did that, which I explained. And no, the authentication should be automatic, like the title says. — AcclaroDev, Jan 20 '22 at 13:29
@AlwaysLearning I already did that: "In IIS/Sites/[Website]/Authentication we disabled Anonymous Authentication and enabled Windows Authentication." - I already tested it with only that enabled. The SQL Server still tries to login with the Server User. I'm repeating myself here. — AcclaroDev, Jan 20 '22 at 13:57
Sounds like you need constrained delegation, see https://stackoverflow.com/questions/1847239/sql-server-and-windows-authentication-in-iis7 and others. If you ran IIS and SQL Server on the same server then I think this would be easier — Charlieface, Jan 20 '22 at 14:30
@Charlieface Thank you very much for your answer. It does sound like it to some degree. At the moment the first link seems to assume stronger constraints for Protocol Transition e.g. that the website is accessed through the internet or that the SQL Server is not in the same domain. I'm reading through it and trying to understand if that is what I need. — AcclaroDev, Jan 20 '22 at 15:17
@Charlieface running IIS and SQL Server on the same machine is never a good idea (non paged pool conflicts). — Lex Li, Jan 20 '22 at 17:13
@LexLi Probably, although if you're careful with max memory allocations it might be OK. I guess it's between ease of setup and management, or what's best practice — Charlieface, Jan 20 '22 at 23:45

score 2 · Accepted Answer · answered Jan 20 '22 at 14:49

It's a common misconception that Windows Authentication implies Impersonation. When a user authenticates to a server with Windows Integrated auth, the code on the server doesn't automatically start running as that user.

The normal configuration for an Enterprise web app is for IIS to user Windows Auth to authenticate the users, but to connect to SQL Server using the IIS App Pool Identity.

If you want the users to connect as themselves to SQL Server through the web application, that's called "Impersonation" and you need to enable and configure it. There's a SO question here that shows how to perform impersonation in your middleware. But additional confgiuration, like Kerberos Constrained Delegation configuration may be required.

The downsides of doing this are

It's extra work to configure, and you may need your network admins to help.
If users can connect directly to the database through the app, they can do it through other tools too. So security administration is harder, and riskier.
Users can't reuse connections, so you'll have lots of idle connections.

Automatic Windows Authentication over IIS, ASP.NET 5 and Microsoft SQL Server

1 Answers1