Windows authentication information lost when make call to MSSQL

[OleksandrChekmez]

We created sample project with Entity Framework and RESTier using impersonation to use Windows authentication when we make calls to MSSQL DB. Web service make calls to Db using application pool user account instead of account who called web service. If we exclude RESTier and create simple WEB API service with Entity Framework 6, authentication works fine.

Assemblies affected

We use RESTier 0.4.0-rc2

Reproduce steps

Create simple DB in MS SQL with view v_System_User which returns SYSTEM_USER and CURRENT_USER. Create project according to http://odata.github.io/RESTier/#01-02-Bootstrap guide, with the following exceptions:

• ASP.NET template - Web API
• Authentication- Windows authentication
• Entity Data Model Wizard - EF Designer from database

Open http://localhost:2940/api/Test/v_System_User

Expected result

SYSTEM_USER and CURRENT_USER is real user who called web service

Actual result

SYSTEM_USER and CURRENT_USER is application pool user.

Additional details

Sample project - https://github.com/OleksandrChekmez/ODataRESTier

[chinadragon0515]

RESTIer is transparent for any security configuration or settings.

I did some quick search on your issues, if should be related to your configuration in web.config file.

You can refer to some link like http://stackoverflow.com/questions/13344968/using-windows-authentication-with-connection-string-in-iis-to-connect-to-sql-200

http://stackoverflow.com/questions/27587167/entity-framework-windows-authentication-with-asp-net-impersonation

So I close the issue now it is some configuration of security.

[OleksandrChekmez]

There are no any problem with configuration or settings. Because when we create service using Entity Framework, but without RESTier, everything works correct. Therefore web.config, connection string, IIS, MSSQL DB, AD domain are configured correctly.

After we add RESTier to work via OData protocol, all our requests to DB done under application pool account.

[chinadragon0515]

I did some quick check and search,

In your web.config,

We can see security configuration is SSPI,

From this link, http://stackoverflow.com/questions/1229691/difference-between-integrated-security-true-and-integrated-security-sspi

When true, the current Windows account credentials are used for authentication. Recognized values are true, false, yes, no, and sspi (strongly recommended), which is equivalent to true.

So we can see the configuration is to use windows authentication.

Maybe you can compare two web.config file and should see the difference.

[OleksandrChekmez]

I tried both options Integrated Security=SSPI and Integrated Security=true

It works wrong. web service makes call using account of application pool.

[markdstafford]

@OleksandrChekmez - can you point us to where in your sample project you have configured your Web application for Windows authentication? I don't see those settings in your Web.config. According to my understanding, it makes perfect sense that you see the app accessing the database with the application pool account.

Being able to see the working project without RESTier and with ASP.NET/EF would be super helpful.

[OleksandrChekmez]

First of all, I set Windows authentication in New Project dialog in VS, but you are right that my Web.config file missing line. But it was missing in github project only. It was present in real project and in published sample project. I fixed Web.config for sample project in GitHub. Windows authentication is defined in connectionString also, using flag Integrated Security=SSPI.

I added working project without RESTier and with ASP.NET/EF to GitHub: https://github.com/OleksandrChekmez/ODataRESTier/tree/master/WebAPI_project You need to adjust connectionString to connect to existing MSSQL DB. To see the result, please open page http://localhost:56685/v_System_User (or your server address where you published it).

Please note that I didn`t write any code for this sample project at all. Everything was generated by VS, I followed this guide - http://www.asp.net/mvc/overview/getting-started/database-first-development/creating-the-web-application, with the following exceptions:

• ASP.NET template - Web API
• Authentication- Windows authentication

Note: Sample RESTier project moved to https://github.com/OleksandrChekmez/ODataRESTier/tree/master/RESTier_project

[markdstafford]

@OleksandrChekmez - no worries, we believe you. :) Just trying to dig in here and understand what's going on. The Windows authentication in the connectionString is a totally different purpose than the one in the IIS configuration. The former determines whether to use the currently authenticated principal to connect to the database. The latter determines _who_ is the currently authenticated principal (the app pool account or the person who connected to the Web site).

We'll take a look at your repositories and see if we can repro on our side. Can we get a feel for urgency from your perspective?

[OleksandrChekmez]

@markdstafford our project release is scheduled at the end of June, therefore it is critical to get solution for authentication before that.

[chinadragon0515]

@OleksandrChekmez

1. You may upload the wrong code for project WebAPI_project which can not be run. a).Seems you did not create project as http://www.asp.net/mvc/overview/getting-started/database-first-development/creating-the-web-application described, when create project, you choose WebApi but not MVC, right? b). The project you share is using WebAPi, so new controller need to extends ApiController but not Controller, or we will get error like this, or anything else I missed?

No HTTP resource was found that matches the request URI 'http://localhost:56686/api/v_System_User'. No type was found that matches the controller named 'v_System_User'.

1. I did test based on the two projects your provided, and only replace the DB connection part, both test get expected result and return windows user. You can refer to document https://github.com/chinadragon0515/Issue385/blob/master/Github%23385.docx for the detail steps I have done and the screenshot of each step. I am not sure whether you have enabled windows authentication in your IIS manager . The other alternative is configured in your web.config file, refer to this link https://msdn.microsoft.com/en-us/library/ff647405.aspx.
2. For the projects I have tested, I share in this link, https://github.com/chinadragon0515/Issue385/tree/master/WebApplication, you can refer to it. I share both the whole project and a word documents with screen shot.

At this moment, I did not any issues with restier or possible issue in restier now(RESTier is transparent to security).

If you still have more questions, I will suggest you ask questions in stackoverflow and close this issue where you may get more help from community and we are more familiar with RESTier code but not so familiar with setting which are transparent to RESTier.

[OleksandrChekmez]

@chinadragon0515

1. I created Web API project. Not MVC.
2. I followed this guide - http://www.asp.net/mvc/overview/getting-started/database-first-development/generating-views to create controller. And you are right that I used MVC 5 Controller instead of Web API. I updated my project to use Web API controller (v_System_UserAPIController). I tested it and it works fine ( http://localhost/WebAPIplusEF/api/v_System_UserAPI ).

I tried your sample project RestierTestWebApplication and it works fine if I use my local SQLExpress, but when I publish it to real server, where we have Active Directory and Windows authentication, it connects to DB using application pool account.

See https://github.com/OleksandrChekmez/ODataRESTier/blob/master/steps%20to%20reproduce.docx

Since https://github.com/OleksandrChekmez/ODataRESTier/tree/master/WebAPI_project/WebAPIplusEF project works fine on real server, therefore configuration of the server is correct. Because we use the same IIS, the same DB, only project code is different, therefore I assume that problem is in the code.

[OleksandrChekmez]

After a lot of long hours in RESTier source code debugging and checking we found the reason of the problem. All calls to Db done as async calls and some special manipulations required in order to do not loose user identity during such call.

This article help us to the the problem - https://blogs.msdn.microsoft.com/tom/2008/04/22/making-an-asynchronous-call-using-the-impersonation-identity/

We fixed it by changing aspnet.config file. It is not the best solution, because it can conflict with other application, but it is the only solution we found.

I hope this article will help you to fix the problem inside RESTier and it will work even without aspnet.config file changes.

[chinadragon0515]

@OleksandrChekmez Sorry for the later response, we are busy to release 0.5.0 of RESTier which we do not expect big changes before GA.

I am glad to hear you have a way to workaround this, we will look at this.

[markdstafford]

Thanks for the tip, and I'm really glad you at least have it functional. As @chinadragon0515, we are following up with some internal resources to see if there's something we can do here to reduce pain for future customers.

[chinadragon0515]

@markdstafford An issue against ASP .NET was already opened yesterday to see how we can improve future customers experience. https://github.com/aspnet/Identity/issues/835

I will continue follow up on that issue.

[chinadragon0515]

Ask on stack overflow, http://stackoverflow.com/questions/37604029/webapi-project-entityframework-asynchronize-call-does-not-use-the-same-account-a

[chinadragon0515]

Ask internal, was still recommend to this way, as this is for all Web Api project for async call, not something can be handled at RESTier code level, will add some note and then close the issue.

[chinadragon0515]

document is updated, close the issue.