Solution to avoid double transition from Client> Web Services> SQL Server

Question

My project uses a user connecting from a client to a web service, and then the web service to SQL Server. Web Services and SQL Server are on different machines. Due to security requirements, we cannot use mixed mode in SQL Server, only Windows authentication.

We are faced with the “double break” problem between the web service and SQL Server. We use NTLM authentication and do not want to configure Kerberos due to overhead and learning curve. We also do not want the web service and SQL Server to be on the same computer.

From what I understand, all of our requirements make this scenario impossible to solve. However, the developer came up with this proposal:

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

2) Somehow to convert the Windows username and password into a security token that can be authenticated by SQL Server

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

My questions:

1) Is the proposed solution possible?

2) If so, how to do it?

3) Any web resources that will help me understand how this can be done?

Answer 1

No, It is Immpossible. The client process does not have access to the user's password and, therefore, cannot send it to the web service level. The client will have to explicitly ask the user for a password. If the client process has a password and is ready to send it to the web service, theoretically WebService can create a token for this user / password (using LogonUser ), and then connect to SQL Server using this token. This so-called solution is so riddled with many security issues that are not worth discussing. If your team insists on this, create a web service that will do this, ask the team member to connect to him, and as soon as you take his authority (he will send you his password, remember?) Connect to the exchange server and send an email to the CEO with text "Fire me, I'm an idiot." Or change his direct deposit bank and HR account. Use your imagination ... I hope now it’s a little clear why going down the path you suggest is a very bad idea .

Just use Kerberos.

By the way, if you need to authenticate in the background due to government regulation, keep in mind that authentication and auditing are always associated with the requirement of “refusal” and sending a password to the web service so that it authenticates at your obvious contradiction with this requirement, since the web service can perform any operation that it wants to mask as a user. This is what Kerberos delegation is limited delegation.

Answer 2

It's impossible. Think about it, if applications can just generate security tokens in such a way, what would it be good? You will need Kerberos to solve this problem.

Edit: also, the runas analogy does not apply if the client tries to authenticate to two systems in a row, since runas requires only one transition, even when switching the user.