0

I'm trying to set up impersonation/delegation for a web application using ASP.NET 4.5/SQL Server 2016. The goal is to use the Windows authentication on the web application and the SQL Server.

I reproduced on Azure a setup similar to the one which will be used for production, but I can't seem to find what is making the impersonation not working.

Azure VM #1 [machine name: test-iis-server]: Windows Server 2012 running IIS 8.5 and acting as Active Directory Domain Controller

Azure VM #2 [machine name: test-sql-server]: Windows Server 2016 running SQL Server 2016

Azure VM #3 [machine name: test-client]: Windows 10 machine for simulating a user connecting to the website

I created an Active Directory domain named TEST. It is possible to connect to the 3 machines with users created in Active Directory.

IIS Web server configuration:

  • In the web.config file:

    • Authentication mode = Windows
    • Identity impersonate = True
    • validation validateIntegratedModeConfiguration = False
    • Integrated security = SSPI

  • In IIS Manager:

    • Windows authentication = Enabled (Kernel-mode authentication = Disabled, Providers = Negotiate:Kerberos)
    • ASP.NET Impersonation = Enabled
    • Application pool = Integrated Managed Pipeline (Identity = Custom Identity: test\my-svc-account)

  • In Active Directory Users & Computers

    • For each computers (web server, sql server and user computer), I went into Properties and checked in the Delegation tab Trust this computer for delegation to any service (Kerberos only).

SQL Server Configuration

  • I did not setup anything here. I assumed that ASP.NET will use the credentials of the user logged in the web application to access the SQL Server database.
  • Edit: SQL Server service account: test\my-svc-account

Results:

  • If I don't use impersonation in the web application and use a defined user/pwd login created in SQL Server, my application works normally and I can get the Windows user credential if I want.

  • Using impersonation, I get a SQL Server connection error when I open the web application page: Login failed for user 'TEST\test-iis-server$'.

Expected behavior:

  • The web application will log into SQL Server using the credentials used to log into the "test-client" machine.

I've read a lot on how to implement the impersonation/delegation for my solution, but can't seem to find what's wrong. Anyone has any idea where the proble might come from and how I can resolve it?

Edit #1:

  • From what I've read, it seems like I need to setup SPNs. I'm confused about how to set them up correctly for my double-hop scenario.
  • I have created a user account in Active Directory to act as a service account. I've set this account to be trusted for delegation.
  • I use this account as the identity for my application pool in IIS and as the service account of the SQL Server instance.
kaycee
  • 901
  • 1
  • 9
  • 35
  • In IIS, 1) check to make sure you disabled Anonymous access to your site. – tgolisch Mar 27 '18 at 13:56
  • @tgolisch already done. Only Windows Authentication and ASP.NET Impersonation are enabled. – kaycee Mar 27 '18 at 14:14
  • 1
    MS has a very thorough and concise guide to troubleshooting Kerberos and permissions. I know S/O doesn't like links, but it is too much to copy/past and too dense to summarize. The support article is titled: "Things to check when Kerberos authentication fails using IIS/IE" https://blogs.msdn.microsoft.com/friis/2009/12/31/things-to-check-when-kerberos-authentication-fails-using-iisie/ – tgolisch Mar 27 '18 at 14:29

1 Answers1

1

Yes, you do need to configure SPNs for both the ASP.NET app pool identity, and the SQL Server service account.

It's relatively straightforward, but you need to make sure you get the right values.

In AD Users and Computers find the 'my-svc-account' account and open the properties. Navigate to the attribute editor tab (if you don't see it, enable advanced features through the ADUC 'View' menu). Find the servicePrincipalName attribute and edit it. Add the following:

http/servicename.foo.com

http/servername <== optional

Where service.foo.com matches your DNS name. If this is a CNAME, you need to also include the underlying A record name as well. So if servicename.foo.com maps to whatever.cloudapp.net, you need to add an SPN for whatever.cloudapp.net. This is for IE, because IE is ...dumb... and trying to be smart (it resolves the DNS down to lowest named record and requests an SPN for that).

Then do the same for the SQL Server service account.

MSSQLSvc/sqlserver.foo.com

MSSQLSvc/sqlserver <== optional

This needs to be the FQDN of the SQL Server host.

Lastly, you need to enable Constrained Delegation between the App Pool identity and the SQL Server service account. This is the 3rd radio button in the delegation control. Add the SQL Server SPN as a delegated target.

Restart IIS and SQL. Try browsing to the app. You should now see it connect to SQL as your named user.

Community
  • 1
  • 1
Steve
  • 4,463
  • 1
  • 19
  • 24