Kerberos and SQL Server

  • 6 August 2013
  • 5 replies
  • 9 views
R
Badge +3

Hello


We have developed a custom SmartObject Service and constructed a couple of SmartObjects on top of it.


Within the SmartObject service, c# Linq-to-SQL code is used to make a connection to a separate SQL Server database that resides on the same SQL Server instance as the K2 database. The connection string used there looks something like this:
Initial Catalog=SomeDatabase;Data Source=K2HostServerK2;Integrated Security=SSPI


When we debug and test our code using the SmartObject Service tester on the K2 Host Server, it works as expected. The code executes in the security context of the K2 Service account, the connection to SQL Server is established and the operations run successfully.


When we debug and test the code using the SmartObject Service tester on another client, the connection to the SQL Server fails. In this scenario, where there is a double-hop (SmartObject Server Tester Client > K2 Host Server > SQL Server), the following Exception is produced:
Debug   28053 Marshalling: OpenWorklist K2:HALLAMSTREETk2svc
Debug   8037 Sending 127.0.0.1:11 Bytes to 10081
Debug   8045 Disconnected From 127.0.0.1:11
Error   10702 An error occurred in the WorklistService service instance. Login f
ailed for user 'NT AUTHORITYANONYMOUS LOGON'.
Debug   10046 SmartObject execution event raised successfully.



And in the SQL Server logs, it is clear that the Security Context under which the code is attempting to establish the SQL Server connection, is not as we would expect:



Message
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.



At the moment I am trying to establish definitively whether this problem is caused by a Kerberos issue, or whether there is something else coming in to play.


We have Kerberos Event logging on the destination SQL Server, which reports:


ErrorCode 0xd
  ErrorMessage KDC_ERR_BADOPTION
  ExtendedError 0xc00000bb KLIN(0)


My research of the above suggests that it is usually associated with failing to specify SPN details for Delegation in Active Directory for the relevant account(s). However, we are not using Constrained Delegation (security is not a big deal in our environment) and we have simply ensured that all of the accounts involved in this scenario can Delegate to ANY service. So that should not be the issue.



I am now looking for some advice as to how to further troubleshoot this issue. I have reviewed our Kerberos configuration over and over again and I am reasonably satisfied that it should be okay, but I am unsure whether I am missing something else and I'm now at something of an impasse


I think the key to this is to understand why, when the Service code attempts to establish the SQL Server connection (which specifies integrated security  (;Integrated Security=SSPI)  the accepting SQL server nevertheless receives an ANONYMOUS token; bearing in mind that the code itself must be running in the security context of the K2 Service account. I'm assuming this must be a Kerberos issue that is SQL Server specific, and consequently, have investigated the possibility of specifying an SPN in the SQL server connection string, but have been unable to do so as such an option does not seem to be supported by the standard .NET provider.


Any additional thoughts would be gratefully received


thanks


Robert

5 replies

P
Badge +13

If it's a windows service that runs under the K2 service account on K2 Host server then talking to SQL Server is just 1 hop and should not involve with Kerberos.


 

R
Badge +3

Thanks. It's not a Windows Service. The code that establishes the SQL Server connection is part of a custom SmartObject Service.


 


 

R
Badge +3

Hello

 

Further to this post, we were subsequently advised by K2 that double-hop Kerberos authentication (Client > K2 Host Server > SQL Server) was broken by the upgrade from 4.6.6 to 4.6.7.

 

Apparently, a patch was considered, but later cancelled in lieu of a fix to be incorporated with 4.6.8.

 

4.6.8 has now shipped, but we are experiencing exactly the same issue.

 

I have raised this with my contacts at K2, but in the meanwhile, is anyone here aware of whether or not the "broken" Kerberos double-hop at 4.6.7 is officially resolved and supported at 4.6.8? Any confirmation one way or the other would be really useful.

 

thanks

 

Robert

 

R
Badge +3

Here is the final update and information for anyone who may later need it

 

1) It is definitely the case that SQL Server Services using Kerberos and Impersonation to support double-hop authentication from SmartForms:

  a) is broken by an upgrade from 4.6.6 to 4.6.7

  b) is fixed by an upgrade to 4.6.8

 

2) On the upgrade to 4.6.8 it seems that you must undertake the following steps (at a minimum) to restore a previously-working Kerberos-enabled scenario:

 

 a) in the K2 config files, find the "ClientWindows" setting and change it to "ClientKerberos" - it SEEMS that the K2 Installer may (incorrectly) overwrite these settings for an existing Kerberos set up - but I am not 100% sure about that. Certainly, we had to change them, post-upgrade.

 b) In the K2 database, modify the same setting - its in a table called "HostConfiguration" or something similar. Again, you just have to change it from "ClientWindows" to "ClientKerberos"

 c) With those changes made, re-start Blackpearl then re-run the Configuration Analysis tool. With the above changes made, the CA tool will include the extra step of validating your Kerberos set up and "Windows Token" service. You need to fix any problems reported by the CA, which in our case included setting up Constrained Delegation for specific machines.

 d) Finally, and most importantly - in the SQL Server databases that are the targets of your K2 SQL Services, you MUST explicitly GRANT the K2 Service Account the right to Impersonate whichever users you need to impersonate. We are talking here about SQL Server-specifc permissions on Windows (Integrated Security) accounts (not to be confused with Active Directory, K2,  etc.) Note that this is a signficant departure from 4.6.6 where no such MSSQL-specific requirement exists for the equivalent scenario.

 

PLEASE NOTE: YMMV. The above steps are what was required in my specific environment. We were only able to resolve these issues through K2 Support and those guys were working diligently on the problem for over 5 hours. The set up of kerberos is not straight-forward so I'd lean on their support services wherever you can. 

 

I wanted to share this information here to hopefully reduce the pain for others - but any steps or advice given above you take entirely at your own risk. I think what is required probably varies significantly by environment.

R
Badge +3

Further to Kerberos and Impersonation for SQL Server Services at BlackPearl 4.6.8

 

  The Impersonation is carried forward to SQL Server using "EXECUTE AS"

 

Cross-referencing this post to make people aware of certain additional requirements to make this work:

 

http://stackoverflow.com/questions/28463989/sql-server-execute-as-requires-individual-logins

 

NOTE: if you are seeing the notorious "Login failed for NT AUTHORITYAnonymous" messages, do not assume that it is necessarily the case that Kerberos is failing. This message does occur when Kerberos is misconfigured or working improperly. However, you will the SAME message if the SQL Server permission requirements are not met, or if the Sql Server "EXECUTE AS" requirements are not met. It is DEFINITELY the case that SQL Server permissions errors do not work their way fully up the stack, and instead produce this same error, if you are using Impersonation.

 

VALUABLE TIP: make sure the SQL SERVER-specific "EXECUTE AS" requirements are met, if you are using the "Impersonation" option for SQL Server services. See the SO post above.

 

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings