如何建立的SQL Server / IIS 7.0允许ASP.NET MVC网站访问数据库？ [英] How to set-up SQL Server / IIS 7.0 to allow ASP.NET MVC website to access to database?

问题描述

我已经建立了一个ASP.NET MVC 2网站，我与Rackspace的云服务器使用的是IIS 7.0托管。

I have built an ASP.NET MVC 2 website that I am hosting with Rackspace Cloud Server using IIS 7.0.

当我尝试测试的网站在本地主机与IIS 7.0服务器上我得到一个错误页面。这个来自SQL日志（我使用SQL网络版）：

When I try to test the website under localhost with IIS 7.0 on the server I get an error page. This comes from the SQL logs (I am using SQL Web Edition):

Error: 18456, Severity: 14, State: 11
Login failed for user 'IIS APPPOOL\DefaultAppPool'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

这样的问题是事做与数据库连接我的网站的工作我的家用电脑上的罚款。

My website works fine on my home PC so the issue is something to do with connecting with the database.

已经研究了错误消息，code这意味着我有一个有效的数据库登录，但服务器访问失败。我做了解决此阅读各种论坛和这里堆栈我都试过了，没有一个已经解决了的问题（包括计算器/ serverfault）：

Having researched the error message code it suggests that I have a valid DB login but server access has failed. I did a stack of reading around this on various forums (incl. stackoverflow / serverfault) and here is what I have tried, none of which have solved the issue:

1. 使用SQL Server身份验证 设置新的登录。我创建了SQL一个新的登录，给正确的访问权限，我的数据库。我用下面的Web配置连接字符串：

1. Set up new login using SQL Server Authentication. I created a new login in SQL and gave correct access rights to my DB. I used following connection string in web config:

数据源= myServer上;初始目录= mydb的;用户ID =名为myUsername;密码= MYPASSWORD;

Data Source=myServer;Initial Catalog=myDB;User Id=myUsername;Password=myPassword;

创建新的登录为 IIS APPPOOL \\默认应用 。我在SQL创建新的登录与正确的访问权限和使用Windows身份验证。连接字符串如下：

Create new login as IIS APPPOOL\DefaultAppPool. I created new login in SQL with correct access rights and used windows authentication. Connection string as follows:

服务器= myServer上;数据库= MyDatabase的; Trusted_Connection = TRUE;

Server=myServer;Database=myDataBase;Trusted_Connection=True;

使用现有的网络登录 NT AUTHORITY \\ NETWORK SERVICE 。我映射该用户我与正确的访问权限的数据库。连接字符串如下：

Use existing network login NT AUTHORITY\NETWORK SERVICE. I mapped this user to my database with correct access rights. Connection string as follows:

服务器= myServer上;数据库= MyDatabase的; Trusted_Connection = TRUE;

Server=myServer;Database=myDataBase;Trusted_Connection=True;

这些appeard是主要的选择，因为我通过物质在线阅读。最后，这里的一些其他的东西，可能会有所帮助：

These appeard to be the main options as I read through material online. Finally, here as some other things that may be helpful:

• 设置SQL为以管理员身份运行并没有解决这个问题


• 关闭 UAC 并没有帮助


• 我的应用程序池使用的 ApplicationPoolIdentity （最佳出于安全原因）

• Setting SQL to run as administrator did not solve the issue
• Turning off UAC did not help
• My application pool is using ApplicationPoolIdentity (best for security reasons)

在短，我的网站将无法启动，因为它无法从DB获得一个对象，因此引发错误。问题是，对服务器的访问权限设置不正确。我想不通，我需要SQL登录/连接字符串/域的访问权限什么配置。

In short, my website won't start because it cannot get an object from the DB so throws an error. The issue is that access rights to the server are not set correctly. I cannot work out what configuration of SQL login / connection string / domain access rights I need.

这困扰了我3个星期 - 你能抽出5分钟，以帮助我，请

This has vexed me for 3 weeks - can you spare 5 mins to help me please?

推荐答案

由于您的服务器（Web和SQL）不是域的一部分，您不能使用Windows身份验证（集成安全性）来连接到SQL Server。

Since your servers (web and SQL) are not part of a domain, you can't use Windows Authentication (Integrated Security) to connect to SQL Server.

在IIS中，有当集成安全性= SSPI 在连接字符串中，用于连接到SQL Server的实际用户的应用程序池标识。

In IIS, when having Integrated Security=SSPI in a connection string, the actual user used to connect to SQL Server is the application pool identity.

您应该与您的选项1走，这意味着使用SQL Server身份验证设置新的登录

You should go with your option 1, meaning Set up new login using SQL Server Authentication.

因此​​，步骤是：

1. 创建您的名为myUsername SQL Server用户。


2. 在用户的属性对话框，转到安全对象部分，确保公开被选中。


3. 接下来，移动到用户映射部分（左侧）。寻找你的列表中的数据库（S）和检​​查。楼下，在数据库角色成员列表中，请务必检查公共，的db_datareader 和 db_datawriter权限。



4. 现在给你的用户权限执行存储过程。由于 SA ，在Management Studio中，执行：

1. Create your myUsername SQL Server user.
2. In the user's properties dialog, go to the Securables section and make sure Public is checked.
3. Next, move to the User mapping section (in the left side). Look for your database(s) in the list and check it. Down below, in the Database role membership list, make sure you check public, db_datareader and db_datawriter.

4. Now give your user rights to execute stored procedures. As sa, in Management Studio, execute:

GRANT EXECUTE T​​O名为myUsername;

GRANT EXECUTE TO myUserName;

您就大功告成了。

至于安全性，可以在以后提炼 MyUserName输入的权利不能够，例如删除表或其他物体。

As for security, you can later refine the rights of myUserName to not be able, for example to drop tables or other objects.

在您的应用程序，你应该在你的观点现在使用的连接字符串1

In your application you should use now the connection string at your point 1.

如果您有任何其他的问题，那么请张贴错误消息。

If you have any other problems, then please post the error message.

这篇关于如何建立的SQL Server / IIS 7.0允许ASP.NET MVC网站访问数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！