4

I am trying to run a django website which connects to a SQL Server, using IIS with Windows authentication.

IIS server and SQL server are on different machines under the same domain i.e., iis_machine.example.com and sql_machine.example.com

What I'm trying to achieve:

  1. Impersonate the remote user accessing the django website hosted on IIS, so his Windows credentials are passed to SQL Server for authentication.

What I tried so far:

  1. Added this to my web.config file for impersonation

    <system.web> <identity impersonate="true" /> </system.web>

  2. Enabled Windows authentication and set up Application Pool (django_web) for django website as shown below

    This is what my Authentication for django website looks like in IIS

    This is what my Application Pool for django website looks like in IIS

  3. Added IIS APPPOOL\django_web to SQL Server Security\Logins

Result:

  1. When I access the django website, IIS prompts the user for his Windows credentials and I can successfully login

  2. If user tries to access SQL Server after logging in

    Case a. IIS, SQL Server are on the different machines: SQL server authentication fails with "Login failed for user 'domain\MACHINENAME$'

    Case b. IIS, SQL Server are on the same machine, SQL server authentication is successful but authentication credentials used are IIS APPPOOL\django_web not domain\remote_user

I understand that there is some Kerberos trickery involved with this setup. Unfortunately, I'm only a programmer and not very familiar with how Kerberos works. If someone experienced in this domain, could shed some light on how this setup would work, that'd be really appreciated. Thanks!

I am using:

  • IIS 10
  • SQL Server 2014
  • django 2.0.7
  • Python 3.6.5
  • Windows 8/10
notarobot
  • 41
  • 6

1 Answers1

1

You have to configure the django_web account to be allowed to delegate to the SQL server. I guess since this is IIS APPPOOL\django_web you actually need to configure it on the machine account in Active Directory. Alternatively you can create a custom service account for it instead.

Steve
  • 392
  • 2
  • 7
  • Thanks, so I managed to setup a custom service account in the AD as domain\svc_account and added it to SQL server logins. So any user who accesses sql server from django website is authorized as domain\svc_account now. But how can I achieve impersonation? Is there something I need to do inside django app itself? – notarobot Aug 27 '18 at 19:18
  • I couldn't say about Django specifically. If the SQL library understands Windows auth it probably understands delegation (big assumption though). You need to explicitly configure delegation on the service account. Maybe this is helpful: https://blogs.msdn.microsoft.com/sqlupdates/2014/12/05/sql-server-kerberos-and-spn-quick-reference/ – Steve Aug 27 '18 at 20:26
  • I posted a follow up question [here](https://serverfault.com/questions/928911/kerberos-double-hop-delegation-from-iis-to-sql-server-using-django) after taking your suggestions into acccount? Could you please take a look? – notarobot Aug 31 '18 at 19:00