为asp.net应用程序连接sql server (Windows身份验证vs sql server身份验证)的最佳方式

时间:2021-12-29 01:39:55

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'."

我有一个数据库和一个具有表单验证的站点。它在VS2008上运行良好。这一次,我使用的是“Trusted_connection =True”。但当它从外部或直接从浏览器打开时,我就会收到“用户'NT AUTHORITY ANONYMOUS Login”错误。

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

我知道这是出于许可。SQL server基于windows身份验证。

  • What is the best approach to manage user to connect SQL Server?
  • 管理用户连接SQL Server的最佳方法是什么?
  • Should I enable SQL Server authentication?
  • 是否应该启用SQL Server身份验证?

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

让我知道要做什么,以便让产品有感觉,并且在部署期间不会出现任何问题。

Note: SQL Server is installed on domain server.

注意:SQL Server安装在域服务器上。

3 个解决方案

#1


10  

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.

站点身份验证与ASP和SQL之间的身份验证无关。“表单身份验证”实际上不是一种真正的身份验证形式,而是ASP的角色和成员。Net应用程序,与安全基础设施无关。

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.

当您在Visual Studio中运行您的站点时,您将在您自己的帐户下启动Visual Studio web服务器,并且这个web服务器将使用NTLM/Kerberos作为您的身份验证到SQL服务器,因为它是在您的凭证中运行的。

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.

当您从浏览器访问站点时,您的站点将从IIS应用程序池中运行,该应用程序池的标识通常是一个名为IUSER_的本地用户。当使用NTLM/Kerberos对SQL服务器进行身份验证时,这个本地用户将作为匿名用户进行身份验证,因为本地帐户在远程主机/域上没有任何意义。

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:

解决方案是将应用程序池标识更改为可以很好地使用SQL服务器进行身份验证的用户。为此,必须将IIS主机连接到与SQL Server的主机(或信任SQL Server主机域的域)相同的域中,并且必须将应用程序池标识从该域中更改为帐户。因此,如果SQL Server机器运行在与域FOO连接的机器上,则:

  • make sure the IIS machine is joined to FOO
  • 确保IIS机器与FOO连接
  • create a domain user FOO\MyWebApp
  • 创建一个域用户FOO\MyWebApp
  • change the app pool identity to FOO\MyWebApp
  • 将应用程序池标识更改为FOO my\ webapp
  • add a SQL login for FOO\MyWebApp
  • 为FOO\MyWebApp添加一个SQL登录
  • grant the necessary permission in SQL to FOO\MyWebApp
  • 将SQL中的必要权限授予FOO\MyWebApp

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身份验证的另一种选择是不好的,原因有几个(可能在web上公开密码)。配置,可能在网络上的身份验证期间公开密码)。如果IIS托管机没有加入到域,那么你可以使用镜像账户相同(本地账户名和密码主机IIS和SQL主机),但也有缺陷:不能使用Kerberos,帐户密码必须保持同步的两个主机等。

#2


2  

One approach is to create a service account for the application to use. You create the account in Active Directory or similar. In IIS, set the app pool to operate under that service account. In SQL Server, grant permissions for the service account, either directly or by putting the service account in a role.

一种方法是为要使用的应用程序创建一个服务帐户。您可以在活动目录或类似的目录中创建帐户。在IIS中,将应用程序池设置为在该服务帐户下运行。在SQL Server中,为服务帐户授予权限,可以直接授予,也可以将服务帐户放入角色。

Here's one article about how to do that.

这里有一篇关于如何做到这一点的文章。

#3


2  

Create a managed service account in the domain where both SQL server machine and Web Server (IIS) machine belongs to or trust. Then run the web servers' application pool on the managed service account. Create a SQL Server login using the managed service account. Read the following Technet post for more information about creating managed service accounts;

在SQL server machine和Web server (IIS)机器都属于或信任的域中创建托管服务帐户。然后在托管服务帐户上运行web服务器的应用程序池。使用托管服务帐户创建SQL服务器登录。阅读以下Technet文章,了解更多关于创建托管服务帐户的信息;

http://technet.microsoft.com/en-us/library/dd548356(v=ws.10).aspx

http://technet.microsoft.com/en-us/library/dd548356(v = ws.10). aspx

#1


10  

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.

站点身份验证与ASP和SQL之间的身份验证无关。“表单身份验证”实际上不是一种真正的身份验证形式,而是ASP的角色和成员。Net应用程序,与安全基础设施无关。

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.

当您在Visual Studio中运行您的站点时,您将在您自己的帐户下启动Visual Studio web服务器,并且这个web服务器将使用NTLM/Kerberos作为您的身份验证到SQL服务器,因为它是在您的凭证中运行的。

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.

当您从浏览器访问站点时,您的站点将从IIS应用程序池中运行,该应用程序池的标识通常是一个名为IUSER_的本地用户。当使用NTLM/Kerberos对SQL服务器进行身份验证时,这个本地用户将作为匿名用户进行身份验证,因为本地帐户在远程主机/域上没有任何意义。

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:

解决方案是将应用程序池标识更改为可以很好地使用SQL服务器进行身份验证的用户。为此,必须将IIS主机连接到与SQL Server的主机(或信任SQL Server主机域的域)相同的域中,并且必须将应用程序池标识从该域中更改为帐户。因此,如果SQL Server机器运行在与域FOO连接的机器上,则:

  • make sure the IIS machine is joined to FOO
  • 确保IIS机器与FOO连接
  • create a domain user FOO\MyWebApp
  • 创建一个域用户FOO\MyWebApp
  • change the app pool identity to FOO\MyWebApp
  • 将应用程序池标识更改为FOO my\ webapp
  • add a SQL login for FOO\MyWebApp
  • 为FOO\MyWebApp添加一个SQL登录
  • grant the necessary permission in SQL to FOO\MyWebApp
  • 将SQL中的必要权限授予FOO\MyWebApp

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身份验证的另一种选择是不好的,原因有几个(可能在web上公开密码)。配置,可能在网络上的身份验证期间公开密码)。如果IIS托管机没有加入到域,那么你可以使用镜像账户相同(本地账户名和密码主机IIS和SQL主机),但也有缺陷:不能使用Kerberos,帐户密码必须保持同步的两个主机等。

#2


2  

One approach is to create a service account for the application to use. You create the account in Active Directory or similar. In IIS, set the app pool to operate under that service account. In SQL Server, grant permissions for the service account, either directly or by putting the service account in a role.

一种方法是为要使用的应用程序创建一个服务帐户。您可以在活动目录或类似的目录中创建帐户。在IIS中,将应用程序池设置为在该服务帐户下运行。在SQL Server中,为服务帐户授予权限,可以直接授予,也可以将服务帐户放入角色。

Here's one article about how to do that.

这里有一篇关于如何做到这一点的文章。

#3


2  

Create a managed service account in the domain where both SQL server machine and Web Server (IIS) machine belongs to or trust. Then run the web servers' application pool on the managed service account. Create a SQL Server login using the managed service account. Read the following Technet post for more information about creating managed service accounts;

在SQL server machine和Web server (IIS)机器都属于或信任的域中创建托管服务帐户。然后在托管服务帐户上运行web服务器的应用程序池。使用托管服务帐户创建SQL服务器登录。阅读以下Technet文章,了解更多关于创建托管服务帐户的信息;

http://technet.microsoft.com/en-us/library/dd548356(v=ws.10).aspx

http://technet.microsoft.com/en-us/library/dd548356(v = ws.10). aspx