Solution to avoid double-hop from client > web service > SQL Server

My project invoves a user connecting from client to web service, and then web service to SQL Server. The web services and SQL Server are on separate machines. Because of security requirements, we cannot used mixed mode in SQL Server, only Windows authentication.

We are experiencing the "double-hop" issue between web service and SQL Server. We are using NTLM authentication and do not want to configure Kerberos because of the overhead and learning curve. We also don't want to have the web service and SQL Server on the same machine.

From what I understand, all of our requirements make this scenario impossible to resolve. However, a developer came up with this suggestion:

1) Send the windows username and password from the client to the web service under SSL encryption

2) Somehow convert the windows username and password into a security token that could be authenticated by SQL Server

To make an analogy, it sounds like we would be doing a RUNAS in the C# code when connecting to SQL Server. There would be no authentication for the web service, only through SQL Server.

My questions:

1) Is the proposed solution possible?

2) If so, how would it be done?

3) Any web resources to help me understand how it could be done?

Just use Kerberos.

BTW, if you need to authenticate to the back end due to government regulation, then bear in mind that authentication and audit always come with a 'non-repudiation' requirement, and sending the password to the web-service so it authenticates on your blatantly contradicts that requirement, since the web-service can do any operation it wants masquerading as the user. This is what Kerberos delegation is constrained delegation.

Thanks for the answer. But here is a link that suggests it's possible: http://www.eggheadcafe.com/software/aspnet/31180511/role-based-connections-us.aspxAlthough it's also possible I misunderstood the info in the link. I posed the question here because I was looking for more details.

8kb 2010-06-25 00:46:12

Got it. Why not add the account that serves as the identity for the application pool running the web service as a SQL user?

Pierreten 2010-06-25 00:50:25

If you need to distinguish between users, your web service will be able to do so; you can control security from that level effectively.

Pierreten 2010-06-25 00:52:06

It probably sounds ridiculous, but the reason we are not authenticationg only on the web service level is because of a government security requirement that states all activity in the database must be attributable to a single user. We proposed a situation where the application layer logs all user activity, or we even pass the username to the stored procedures and log it in SQL Server, but that was nixed by our IA department.

8kb 2010-06-25 00:56:37

In that case, you'll have to go the painful and possibly expensive route of setting up Kerboros (but considering this is a government entity you're dealing with, they wouldn't have any concerns regarding cost, would they ;)

Pierreten 2010-06-25 02:04:29

Thanks for responding. I have referenced your articles on queues and service broker on multiple occasions. The idea is that, yes, the username and password would be sent from the client to the web service under encrypted SSL. Only the webservice would have the information and would create the token to access SQL Server. I'm not sure how this is a security risk unless you mean that our programmers could write code to possibly hijack the information. Still, I would rely on your experience over mine, so I'm going assume you're right. I'll take this info back to IA (it was actually their idea...)

8kb 2010-06-25 03:49:41

There are two reasons why this is a bad idea: 1) the user has to explicitly pass in his password in the client, ie. the client does *not* simply flow on the authentication from the user Windows session login. And 2) lack of constraints in the web service: once the user is impersonated, the web service can do *anything*. With Kerberos the impersonated context delegation is *constrained*, it can only connect to specific resources, controlled from the AD and from the SPN registration process.

Remus Rusanu 2010-06-25 04:53:40

"unless you mean that our programmers could write code to possibly hijack the information" that is one very viable possibility. Just think about various scenarios when the users of the web service could be some *very* important users, like company executives. The stakes could be very high. But even if you have complete trust in your programmers, you still have the problem of a service compromise, an attacker gaining control of the web service could gain control/knowledge of all service users passwords.

Remus Rusanu 2010-06-25 05:02:59

The further your explanation goes into detail, the more I can see how bad an idea this is. Especially considering the data we are working with must be protected even from the users themselves. Thanks for your time.

8kb 2010-06-25 05:12:34

ansaurus

tags:

views:

answers:

Solution to avoid double-hop from client > web service > SQL Server

Just use Kerberos.

related questions