1

The pymssql module claims to support Kerberos Authentication (and delegation) and yet I can't seem to enable it.

The client I am running is on Windows. I need to connect with a double-hop through a reverse database proxy. The client, the proxy, and the database are all part of the domain. And when I try to connect with SQL Server Manager I am successful. But when I try to connect with the pymssql module in Python it doesn't work. If I connect directly from the client to the database I am able to get the Kerberos Authentication to work. But again, when I try to go through the proxy it fails.

This leads me to believe that the Kerberos Authentication works, but that the Delegation (double-hop) does not.

According to the section on FreeTDS I should be able to create a file at C:/freetds.conf and it should read connection information from there. I don't seem to be able to verify this in any meaningful way. Additionally, according to the freetds schema I should be able add a parameter enable gssapi delegation which when enabled (off by default) allows Kerberos Delegation.

Bottom Line: I am looking to enable Kerberos Delegation (so that the double-hop will work) for pymssql on windows.

At the moment I have created a file at C:/freetds.conf and have tried a few ways to configure it.

[global]
enable gssapi delegation = on

and 

[global]
enable gssapi delegation = true
Inbar Rose
  • 41,843
  • 24
  • 85
  • 131
  • Did you have a change to evaluate my answer? – Michael-O Apr 15 '15 at 10:08
  • A chance, yes. I have been looking for alternative solutions. I have found some, and it is unfortunate I can't use Pymssql. I will accept your answer, and award the bounty. Because you saved me one hell of a long headache if I would try to make it work anyway, in futility. Thanks for your effort, I wish there was a better answer. – Inbar Rose Apr 15 '15 at 11:23
  • (by better I mean that would let me work with pymssql) – Inbar Rose Apr 15 '15 at 12:11
  • Are you able to compile that software under Windows? The patch for that is very easy. – Michael-O Apr 15 '15 at 12:46
  • Custom compilation is beyond the scope of what I am looking for. I need this solution to be easily portable. – Inbar Rose Apr 15 '15 at 14:52
  • this is not my proposal. You make a patch, pull request and FreeTDS gets updated with the next version. That is it. There is portabilitiy issue here in case of the former. – Michael-O Apr 15 '15 at 15:17

1 Answers1

3

This is pretty easy to answer and is rooted in a shortcoming in FreeTDS. You did nothing wrong.

If we take a look at the GSS-API C code of FreeTDS, we see in lines 307 to 308

if (tds->login->gssapi_use_delegation)
  gssapi_flags |= GSS_C_DELEG_FLAG;

that your config parameter is read in the delegation flag is set.

Since you are on Windows and Windows uses its own flavor of GSS-API, namely SSPI, we have a look at that C code: lines 273 to 278 do 

status = sec_fn->InitializeSecurityContext(&auth->cred, NULL, auth->sname,
  ISC_REQ_CONFIDENTIALITY | ISC_REQ_REPLAY_DETECT 
    | ISC_REQ_CONNECTION | ISC_REQ_ALLOCATE_MEMORY,
  0, SECURITY_NETWORK_DREP,NULL, 0, &auth->cred_ctx, &desc, &attrs, &ts);

as you can see, the context flags are not in a variable but passed directly. Neither the config param is evaluated nor ISC_REQ_DELEGATE is passed.

This is the problem you are seeing. You have two options now:

  1. Raise a bug and wait for a fix.
  2. Clone from GitHub, fix yourself and issue a pull request.

Side note: there is several stuff I do not like about both code parts at all:

  1. SSPI does not perform mutual auth as GSS-API does but should.
  2. Context flags are passed pointlessly but features are never used in that C file, e.g., ISC_REQ_CONFIDENTIALITY | ISC_REQ_REPLAY_DETECT and GSS_C_REPLAY_FLAG | GSS_C_INTEG_FLAG. There are only necessary if you require further transport security which is not employed here.
  3. There are probably more stuff to fix but I did not code review it.

I highly recommend to raise some issues here too.

Michael-O
  • 18,123
  • 6
  • 55
  • 121