最好的方式来连接的SQL Server（Windows身份验证VS SQL Server身份验证）的asp.net应用 [英] the best way to connect sql server (Windows authentication vs SQL Server authentication) for asp.net app

问题描述

我有一个数据库和一个网站有窗体身份验证。这是工作的罚款与VS2008。这一次，我使用Trusted_connection =真。但是，当它被从外面打开或直接从浏览器上，我得到错误登录失败，用户NT AUTHORITY \\ ANONYMOUS登录。

I have a database and a site having forms authentication. It is working fine with VS2008. This time, I am using "Trusted_connection =True". But when it is opened from outside or directly from browser then I am getting error "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

我知道这是由于权限。 SQL服务器是基于Windows身份验证。

I know this is due to permission. SQL server is based on windows authentication.

• 什么是管理用户连接的SQL Server的最佳方法？


• 我应该启用SQL Server身份验证？

让我知道该怎么做，以便它使得生产和手感不会有部署过程中的任何问题。

Let me know what to do so that it makes the production feel and there wouldn't be any problem during deployment.

注：SQL Server安装域服务器上

Note: SQL Server is installed on domain server.

推荐答案

该网站的身份验证无关ASP和SQL之间的认证。在窗体身份验证，其实并不是真正的身份验证的一种形式，只是为了ASP.Net应用程序角色和成员，无关的安全基础设施。

The site authentication has nothing to do with the authentication between ASP and SQL. The 'forms authentication' is actually not a form of true authentication, is just a role and membership for the ASP.Net application, unrelated to the security infrastructure.

当您从Visual Studio中，你正在启动在自己的帐户在Visual Studio的Web服务器您的网站，此Web服务器将使用NTLM / Kerberos作为你的，因为是在您的凭据运行验证到SQL Server。

When you run your site from Visual Studio you are launching the Visual Studio web server under your own account, and this web server will authenticate to SQL Server using NTLM/Kerberos as you, since is running under your credentials.

当您访问从浏览器的网站，你的网站是由与该应用程序池，这通常是一个名为IUSER本地用户_...这个本地用户，身份的IIS应用程序池与SQL Server进行验证时运行使用NTLM / Kerberos的将作为匿名用户进行身份验证，因为本地帐户在托管SQL Server的远程计算机/域没有意义。

When you visit the site from browser, your site is running from the IIS application pool with the identity of the said application pool, which usually is a local user named IUSER_... This local user, when authenticating with the SQL Server using NTLM/Kerberos will authenticate as the Anonymous user, since the local account has no meaning on the remote machine/domain hosting the SQL Server.

解决方案是更改应用程序池标识，可以与SQL Server身份验证精细的用户。为此，IIS托管计算机必须加入到同一个域中的SQL Server的主机（或信任SQL Server主机域中的域）和应用程序池标识必须从这个领域变成一个帐户。因此，如果一台机器上的SQL Server机器运行加入域FOO，那么：

The solution is to change the application pool identity to a user that can authenticate fine with the SQL Server. For this, the IIS hosting machine must be joined to the same domain as SQL Server's host machine (or a domain that trusts the SQL Server host machine domain) and the application pool identity has to be changed to an account from this domain. So if the SQL Server machine runs on a machine joined to the domain FOO, then:

• 确保IIS计算机加入到FOO


• 创建一个域用户FOO \\ MyWebApp


• 更改应用程序池标识FOO \\ MyWebApp


• 添加SQL登录为FOO \\ MyWebApp


• 授予SQL必要的权限，以FOO \\ MyWebApp

使用SQL身份验证的另一种方法是坏的原因有几个（潜在的暴露在web.config中的密码，潜在的暴露在电线上的认证过程中密码）。如果IIS托管机器没有加入到域，那么你的可能的使用镜像帐户（本地帐户既IIS主机和SQL主机上相同的名称和密码），但也有缺陷：不能使用Kerberos工作，账号密码必须保持同步两台主机等。

The alternative of using SQL Authentication is bad for several reasons (potential to expose the password in web.config, potential to expose the password during authentication on the wire). If the IIS hosting machine is not joined to the domain then you may use mirrored accounts (local accounts with same name and password on both IIS host and SQL host) but that is also flawed: cannot work with Kerberos, the account password has to be kept in sync on the two hosts etc.

这篇关于最好的方式来连接的SQL Server（Windows身份验证VS SQL Server身份验证）的asp.net应用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！