如何设置SQL Server / IIS 7.0以允许ASP.NET MVC网站访问数据库？

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

我已经构建了一个ASP.NET MVC 2网站,我使用IIS 7.0托管Rackspace Cloud Server。

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):

当我尝试在服务器上使用IIS 7.0测试localhost下的网站时,我得到一个错误页面。这来自SQL日志(我正在使用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.

我的网站在家用电脑上工作正常,因此问题与连接数据库有关。

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. * / serverfault) and here is what I have tried, none of which have solved the issue:

研究了错误消息代码后,它表明我有一个有效的数据库登录但服务器访问失败了。我在各种论坛(包括* / serverfault)上做了一堆阅读,这是我尝试过的,没有一个解决了这个问题:

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:

使用SQL Server身份验证设置新登录。我在SQL中创建了一个新的登录名,并为我的DB提供了正确的访问权限。我在web配置中使用了以下连接字符串:

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

数据源= myServer;初始目录= myDB;用户ID = myUsername;密码= myPassword;
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:

创建新的登录名为IIS APPPOOL \ DefaultAppPool。我在SQL中创建了具有正确访问权限的新登录并使用了Windows身份验证。连接字符串如下:

Server=myServer;Database=myDataBase;Trusted_Connection=True;
Use existing network login NT AUTHORITY\NETWORK SERVICE. I mapped this user to my database with correct access rights. Connection string as follows:

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

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

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

当我在线阅读材料时,这些似乎是主要的选择。最后,这里还有一些其他可能有帮助的事情:

Setting SQL to run as administrator did not solve the issue

将SQL设置为以管理员身份运行并不能解决问题

Turning off UAC did not help

关闭UAC没有帮助

My application pool is using ApplicationPoolIdentity (best for security reasons)

我的应用程序池正在使用ApplicationPoolIdentity(最出于安全原因)

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.

简而言之,我的网站将无法启动,因为它无法从数据库中获取对象,因此会抛出错误。问题是未正确设置对服务器的访问权限。我无法弄清楚我需要的SQL登录/连接字符串/域访问权限的配置。

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

这让我烦恼了3个星期 - 你可以花5分钟来帮助我吗?

1 个解决方案

#1

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.

由于您的服务器(Web和SQL)不是域的一部分,因此无法使用Windows身份验证(集成安全性)连接到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.

在IIS中,当在连接字符串中具有Integrated Security = SSPI时,用于连接到SQL Server的实际用户是应用程序池标识。

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

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

So, the steps would be:

那么,步骤将是:

Create your myUsername SQL Server user.

创建myUsername SQL Server用户。

In the user's properties dialog, go to the Securables section and make sure Public is checked.

在用户的属性对话框中,转到Securables部分并确保选中Public。

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.

接下来,转到“用户映射”部分(在左侧)。在列表中查找您的数据库并进行检查。在下面的数据库角色成员列表中,确保检查public,db_datareader和db_datawriter。

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

现在授予您的用户执行存储过程的权限。作为sa,在Management Studio中,执行:

GRANT EXECUTE TO myUserName;

GRANT EXECUTE到myUserName;
You're done.

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

至于安全性,您可以稍后将myUserName的权限细化为不能,例如删除表或其他对象。

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

在您的应用程序中,您现在应该使用您的点1处的连接字符串。

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

如果您有任何其他问题,请发布错误消息。

#1