Login failed for user computer\ASPNET

我的資料庫連接字串是這樣的：server=MachineName\SQLEXPRESS;database=test;trusted_connection=true

感到很是奇怪，當單獨部署運行web service時沒問題，但把它發布到IIS, 訪問就有問題了，查了下資料，如下：

 

SYMPTOMS

When you create a trusted connection from Microsoft ASP.NET to Microsoft SQL Server, you may receive the following error message:

Login failed for user 'MachineName\ASPNET'

For computers that run Internet Information Services (IIS) 6.0, you may receive the following error message:

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'

Note You receive either of these error messages specifically when you use integrated security (when you include the integrated security=sspi attribute in a connection string).

RESOLUTION

To resolve this issue, use one of the following methods:

•Method 1 Programmatically change the security context of the ASP.NET worker process to a user who has the correct SQL Server permissions.

•Method 2 Change the default configuration of ASP.NET so that the ASP.NET worker process starts and runs under the context of a user who has the correct permissions in SQL Server.

•Method 3 Grant the correct permissions in SQL Server so that the ASPNET account (or NetworkService account, for an application that runs on IIS 6.0) has the appropriate access to the required resources.
Note This method will make all the Web applications on the server have the corresponding right on the computer that is running SQL Server.

 

我採用的是第3種方法，在SQL SERVER中增加一個ASPNET的使用者，然後賦予一定的許可權，因為我的web service僅做學慣用途，所以賦的許可權是sysadmin。添加方法如下：

1．進入Microsoft SQL Server Management Studio Express, 右擊Security下的Loings，建立一個Login

2. 建立一個MachineName \ASPNET, MachineName 為你的機器名

3. 設定 Server Roles 等選項

-----------------------------------------

This error can take a variety of forms, including:

• "Login failed for user '(null)'"
• "Login failed for user domain\username"
• "Login failed for user computer\ASPNET"

The likely cause is that you are working with a Web application and are trying to access a SQL Server. The specific error or exception that is thrown depends on whether the SQL Server is on the same computer as the Web server or on a separate computer. This problem arises because the Web application is not passing proper credentials to the SQL Server. In general, the solution is to:

• If the SQL Server is on the same computer as the Web server, give the local ASPNET user login privileges on the SQL Server.
• Set IIS or ASP.NET to impersonate a Windows domain user who has login privileges on the SQL Server.

If you want to authenticate your users in DataSource, you need add to web.config in your web-application:

<identity impersonate="true"/> and use "Windows integrated security" in your DataSource configuration. As result, your users will be authenticated in you database.

• Use a connection string with an explicit user ID and password.

change the auto generated connection string in web.config file from
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myDatabaseName.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
To
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myDatabaseName.mdf;User ID=sa;Password=mySAPassword;database=myDatabaseName" providerName="System.Data.SqlClient"/>

 

For more information, see Accessing SQL Server from a Web Application.

 

 

from:

http://msdn.microsoft.com/en-us/library/aa984220(VS.71).aspx