CODEWITHSUNDEEP
asp.netsql-serveriiswindows-server-2012kerberos-delegation
kaycee
kaycee

Reputation: 911

Implementing ASP.Net impersonation/delegation to connect to remote SQL Server from ASP.Net server not working

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:

Expected behavior:

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:

Upvotes: 0

Views: 3413

Answers (1)

Steve
Steve

Reputation: 4623

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.

Upvotes: 1

Related Questions