IIS无法将Windows凭据传递给SQL Server for ASP.NET Core应用 [英] IIS fails to pass windows credentials through to SQL Server for ASP.NET Core app

问题描述

我为一家拥有Intranet和Windows AD登录名的大公司工作.我们有许多内部SQL Server数据库，这些数据库允许我们使用Windows身份验证登录，我正在尝试通过ASP.NET Core应用程序连接到其中一个数据库.我可以通过SQL Server Management Studio连接到该数据库并查询表.

我已按照教程中的说明操作 ASP.NET Core应用程序尽可能使用现有数据库，并创建了一个模型类进行测试，以查看是否可以从数据库中读取数据.在Visual Studio中使用IIS Express进行调试时，在访问自动生成的控制器和视图时，我可以从数据库中读取数据.

调试时一切正常，但是发布到IIS时，出现以下错误:

  SqlException:用户'< DOMAIN> \< COMPUTERNAME> $'的登录失败. 

其中domain是我的域名，computername是我的计算机的名称.这是预料之中的，因为我的计算机本身无法访问数据库.但是它不应该尝试使用该系统帐户(带有美元符号)进行连接，而应尝试与我的Windows帐户:< DOMAIN> \< USERNAME> .>

奇怪的是，应用程序确实可以能够以某种方式识别我的Windows凭据-当我访问主页时，得到熟悉的"Hello，< DOMAIN> \<导航栏中的USERNAME> !! 消息.因此，Windows凭据肯定会传递给该应用程序，但是由于某些原因，当尝试通过DbContext连接到数据库时无法传递该信息.

我在这里错过了明显的东西吗?

我的代码

我从Visual Studio的ASP.NET Core Web应用程序模板开始.

在launchSettings.json中，我有:

 "iisSettings":{"windowsAuthentication":是的，"anonymousAuthentication":否，"iisExpress":{"applicationUrl":"http://localhost:60686"，"sslPort":44336}}， 

在appsettings.json中，我有:

 "ConnectionStrings":{"MyDB":服务器=<服务器名> ;;数据库=< dbname> ;; Trusted_Connection = True;"}， 

在Startup.cs中，我在 ConfigureServices

中包含以下行

  services.AddDbContext< MyContext>(options => {options.UseSqlServer(Configuration.GetConnectionString("MyDB"));}); 

然后，从那里开始，我使用Entity Framework为MVC控制器提供了视图.

IIS将Windows身份验证设置为是"，并将匿名身份验证设置为否".我的应用程序池设置为无使用ApplicationPoolIdentity的托管代码".

问题所在

为了说明我要解决的实际问题，我在远程Intranet服务器上有一个SQL Server数据库，该数据库允许通过Windows身份验证访问整个公司的子集.如果我想创建一个ASP.NET应用程序来为由IIS托管的数据库提供API，那么最好的方法是什么?假设:

1. 我不想自己管理权限或以某种方式复制权限
2. 直接有权访问数据库的人应该有权访问API，而不能直接访问数据库的人.
3. 如果他们在登录Windows时从Intranet内部访问它，则不必再次登录.

我以为我可以将他们的Windows凭据从IIS通过应用程序传递到SQL服务器，但我开始怀疑是否确实如此.

解决方案

了解更多有关.NET以及Windows auth在IIS上实际执行的操作之后，我要说的是，我不建议尝试这样做.将Windows凭据传递给.NET应用程序以便从中读取信息与实际以该用户身份执行辅助进程之间有区别.后一种情况是我试图做的，但是应该在IIS中设置我的应用程序池，该用户可以登录数据库，并使用Windows凭据对具有访问权限的用户列表进行验证.

I work for a large company with an intranet and Windows AD logins for everyone. We have a number of internal SQL Server databases which allow us to log in using Windows authentication, one of which I'm trying to connect to through an ASP.NET Core application. I can connect to this database through SQL Server Management Studio and query the tables fine.

I've followed the tutorial for an ASP.NET Core app using an existing database as closely as I possibly could, and created a single model class to test with to see if I could read data from the database. When debugging with IIS Express in Visual Studio, I can read data from the database when accessing the auto-generated controller and views.

Everything seems fine when debugging, but when publishing to IIS, I receive the following error:

SqlException: Login failed for user '<DOMAIN>\<COMPUTERNAME>$'.

Where domain is my domain and computername is my computer's name. This is expected, since my computer itself doesn't have access to the database. But it shouldn't be trying to connect using that system account (with the dollar sign), it should be trying to connect with my windows account: <DOMAIN>\<USERNAME>.

What's weirder, the app does seem to recognize my Windows credentials in some capacity - when I access the home page, I get the familiar "Hello, <DOMAIN>\<USERNAME>!" message in the nav bar. So the Windows credentials are definitely getting passed through to the app, but for some reason not getting passed through when trying to connect to the database through DbContext.

Am I missing something obvious here?

My Code

I started with Visual Studio's ASP.NET Core Web Application template.

In launchSettings.json, I have:

  "iisSettings": {
    "windowsAuthentication": true, 
    "anonymousAuthentication": false, 
    "iisExpress": {
      "applicationUrl": "http://localhost:60686",
      "sslPort": 44336
    }
  },

In appsettings.json, I have:

  "ConnectionStrings": {
    "MyDB": "Server=<servername>;Database=<dbname>;Trusted_Connection=True;"
  },

In Startup.cs, I have the following line in ConfigureServices

            services.AddDbContext<MyContext>(options => {
                options.UseSqlServer(Configuration.GetConnectionString("MyDB"));
            });

And from there, I have scaffolded an MVC controller with views using Entity Framework.

IIS has Windows authentication set to Yes and anonymous authentication set to No. My application pool is set to No Managed Code with ApplicationPoolIdentity.

Edit: The problem

To state the actual problem I'm trying to solve, I have a SQL Server database on a remote intranet server which allows access to a subset of the whole company via Windows authentication. If I want to create an ASP.NET application to provide an API to that database, hosted by IIS, what's the best way to do this? Assuming:

1. I don't want to have to manage permissions myself or have to duplicate them in some way
2. The people who have access to the database directly should have access to the API, the people who don't should not.
3. If they're accessing it from within the intranet while logged in to Windows, they shouldn't have to log in again.

I assumed I could just pass their windows credentials from IIS through the app to SQL server but I'm starting to wonder if that's actually the case.

解决方案

After learning more about .NET and what Windows auth actually does on IIS, I'm going to say that what I was trying to do is not recommended. There is a difference between passing windows credentials to a .NET app in order to read from them, vs. actually executing a secondary process as that user. The latter case is what I was trying to do, but instead should set up my app pool in IIS with a user who can log in to the database, and use the windows credentials to verify against the list of users who have access.

这篇关于IIS无法将Windows凭据传递给SQL Server for ASP.NET Core应用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！