Kerberos Double Hop Delegation from IIS to SQL server (using django)

Question

I need to pass the credentials (Integrated Windows Authentication) from a django website on IIS onto a backend SQL server so that it runs under the proper user context.

This is how my setup looks so far:

1. Running SQL Server on sql_sever.domain.com under a service account domain\svc_sqlserver
2. Running Django website on app_server.domain.com using IIS under a service account domain\svc_appserver with Windows authentication and ASP.Net Impersonation (Providers is set to Negotiate:Kerberos -> Negotiate -> NTLM ) with useAppPoolCredentials=True
3. Connecting to SQL server from django using Windows authentication by setting Trusted_Connection=yes in the connection

4. Configured SPNs for Kerberos authentication both for domain\svc_sqlserver and domain\svc_appserver as follows:

   setspn -a HTTP/app_server                          domain\svc_appserver
   setspn -a HTTP/app_server.domain.com               domain\svc_appserver
   setspn -a MSSQLSvc/sql_server.domain.com:PORT      domain\svc_sqlserver
   setspn -a MSSQLSvc/sql_server.domain.com:INSTANCE  domain\svc_sqlserver
   setspn -a MSSQLSvc/sql_server.domain.com           domain\svc_sqlserver
   

5. Trusted both svc_sqlserver and svc_appserver for delegation to MSSQLSvc services and additionally for domain\svc_appserver I added HTTP services too (from the above list)

Result:

1. Kerberos authentication works on SQL Server. Confirmed by looking at auth scheme of connected users
2. Kerberos authentication works on Django website. Confirmed by inspecting WWW-Authenticate response header and Authorization request header (Negotiate is being correctly used)
3. Sql server runs only under the context of domain\svc_appserver when it should be running under domain\remote_user

I've been working on this for more than a week now but for the life of me, I can't figure out how to pass authenticated user's context from IIS to SQL Server. I went through hundreds of links I found online and I'm not sure what to do at this point.

Is there anything else that I'm missing? Is there any way in Django to set the user's context before establishing connection to database? If anyone can help, I'd really appreciate. Thanks!

I'm using:

• django 2.0.7
• django-pyodbc-azure
• Python 3.6.5
• IIS 10, SQL Server 2014

Answer 1

I don't believe that impersonation has been implemented for anything that runs in FastCGI or Kestrel worker processes. See eg for ASP.NET core:

https://docs.microsoft.com/en-us/aspnet/core/security/authentication/windowsauth?view=aspnetcore-2.1&tabs=aspnetcore2x#impersonation

If you use HTTP Basic auth, which requires the user to fork over a password, you could perform a Logon using Win32 and do the impersonation yourself. But for NTLM and Kerberos there's no simple way to impersonate the calling user from inside your python process.

Or, if this is a custom app, you could use SQL Impersonation, or pass the end user's identity to SESSION_CONTEXT where it could be seen by server-side code. It also might be possible to enhance django-pyodbc-azure to do this.

The only other thing I can think is to roll your own Django hosting by implementing an ASP.NET HttpHandler that impersonates the user and manages python processes running under the end user's identity.