当前位置： 文江博客话题详情

带有数据库的 Web 服务：SqlException

发布于 2024-11-10 07:59:56 字数 3029 浏览 7 评论 0原文

我编写了一个与 SQL Server'08 数据库配合使用的 Web 服务

当我尝试调用 Web 方法时，我得到以下信息：

System.Data.SqlClient.SqlException: Login failed for user 'IIS APPPOOL\ASP.NET v4.0'.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(IConnectionUser user)
   at System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe()
   at System.Data.Linq.SqlClient.SqlProvider.InitializeProviderMode()
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at route.Logic..ctor() in C:\Users\Ilya\Documents\Visual Studio 2010\Projects\MobitourLibrary\route\Logic.cs:line 20
   at Service..ctor() in c:\inetpub\wwwroot\RouteGen\App_Code\Service.cs:line 14

问题出在哪里？ 使用相同数据库的其他 win 表单应用程序工作正常，为什么 WS 不行？

原文

I wrote a web service which works with SQL Server'08 Database

When I try to invoke a web method, I get this:

System.Data.SqlClient.SqlException: Login failed for user 'IIS APPPOOL\ASP.NET v4.0'.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(IConnectionUser user)
   at System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe()
   at System.Data.Linq.SqlClient.SqlProvider.InitializeProviderMode()
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at route.Logic..ctor() in C:\Users\Ilya\Documents\Visual Studio 2010\Projects\MobitourLibrary\route\Logic.cs:line 20
   at Service..ctor() in c:\inetpub\wwwroot\RouteGen\App_Code\Service.cs:line 14

Where is the problem?
Other win forms application what uses the same DB works fine, why WS doesn't?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（3）

与他有关 2024-11-17 07:59:56

Web 服务将在与 WinForms 应用程序不同的用户帐户下运行。 WinForms 应用程序将从登录用户的用户帐户进行操作，而 Web 服务将是 ASP.NET 使用的帐户。

您需要在 SQL Server 中设置该帐户并为其授予相关权限。

基本上，您需要将登录名添加到服务器，并将该登录名作为用户添加到需要它的每个数据库。

以下是使用 SQL Server Management Studio 时的分步说明：

  • 在对象资源管理器中，打开 SQL Server 树并进入安全性 --> 登录分支
  • 右键单击​​“登录”并选择“新建登录.. .”
  • 接下来是登录名，单击“搜索”
  • 单击“高级”以出现“选择用户或组”对话框
  • 单击“立即查找”
  • 滚动对话框底部的列表，找到相关用户并双击它。对话框将关闭。
  • 按“确定”。对话框将关闭。
  • 返回“登录 - 新建”对话框，按“确定”

您现在有了一个新的登录名。

现在，在数据库中创建用户。

  • 打开树的数据库分支。
  • 打开您需要的特定数据库的分支，然后是安全-->用户分支。
  • 右键单击“用户”并选择“新用户...”
  • 写入用户名（不必与登录名匹配，但通常与登录名匹配）
  • 在“登录名”旁边按“...”按钮
  • 按“选择登录对话框中的“浏览”
  • 在“浏览对象”对话框中检查所需的登录名。
  • 按“确定”。该对话框将关闭
  • ，按“确定”。选择登录对话框将关闭。
  • 选择“此用户拥有的模式” - 通常为“db_owner”
  • 选择数据库角色成员资格 - 通常它将是由 DBA 创建的特定角色，如果不是，则为 db_datareader & db_datawriter。如果您遇到问题，请选择 db_owner，然后与您的 DBA 一起更正权限（您不希望为 ASP.NET 进程分配 DB Owner 权限，除非您确实必须这样做，这是一个即将发生的安全漏洞）
  • 按“确定” " 关闭数据库用户新建对话框。

现在应该一切都好了。

或

您可以更改 Web 服务中的连接字符串，以使其使用特定帐户连接到 SQL Server。

The web service will be operating under a different user account from the WinForms application. The WinForms application will be operating from the user account of the logged in user, while the web service will be the account that ASP.NET uses.

You need to set that account up in SQL Server and give it the relevant permissions.

Basically, you need to add the login to the Server, and add that login as a user to each database that requires it.

Here's the step by step instructions when you are in SQL Server Management Studio:

  • In the object explorer, open up your SQL Server tree and go into the Security-->Logins branch
  • Right-click "Logins" and select "New Login..."
  • Next the login Name, click "Search"
  • Click "Advanced" to get the "Select User or Group" dialog
  • Click "Find Now"
  • Scroll through the list at the bottom of the dialog and find the relevant user and double click it. The dialog will close.
  • Press "Okay". The dialog will close.
  • Back in the "Login - New" dialog press "OK"

You now have a new Login.

Now, to create the user in the database.

  • Open up the Databases branch of your tree.
  • Open up the branch for the specific database you need, then it's Security-->Users branch.
  • Right click "Users" and select "New User..."
  • Write a user name (it does not have to match the login name, but generally it does)
  • Next to "Login name" press the "..." button
  • Press "Browse" in the Select Login Dialog
  • Check the login name you want in "Browse for Objects" dialog.
  • Press "OK". The dialog will close
  • Press "OK". The Select login dialog will close.
  • Select the "Schemas owned by this user" - normally "db_owner"
  • Select the Database role membership - normally it will be a specific role that has been created by the DBA, if not then db_datareader & db_datawriter. If you have issues, select db_owner then work with your DBA to correct the permissions afterwards (you do not want to assign the ASP.NET process with DB Owner permissions unless you really must, it is a security breach waiting to happen)
  • Press "OK" to close the Database User New dialog.

It should all be good now.

OR

You can change the connection string in the web service to get it to connect to SQL Server using a specific account.

回复 原文
往昔成烟 2024-11-17 07:59:56

Colin Mackay 已为您的问题提供了详细的答案。这是我的两分钱。

如果您的环境中设置了 Active Directory 域，我不建议您在连接中包含用户 ID 和密码。相反，我建议执行以下操作：

  1. 创建一个域帐户，例如 MyDomain\webservice，其中 MyDomain 是活动目录域，webservice将是该域中的 Windows 用户帐户。在 SQL 中，为这个新的域帐户用户提供访问数据库的适当权限。

  2. 在 Internet 信息服务 (IIS) 管理器 中，将应用程序池更改为在此服务帐户下运行。如果您运行的是 IIS 7.5，则可以执行以下步骤来执行此操作：

  3. 在 IIS 中，展开 节点并单击 应用程序池。

  4. 最好创建一个新的应用程序池，这样就不会干扰可能使用该应用程序池的其他应用程序的功能ASP.NET v4.0 >.

  5. 创建与 ASP.NET v4.0 应用程序池类似的新应用程序池（例如 WebServiceAppPool），不同之处在于新应用程序池将使用新应用程序池的身份创建域帐户 MyDomain\webservice，而不是通常的 ApplicationPoolIdentity。

  6. 在部署 Web 服务的虚拟目录/站点的高级设置选项中，更改应用程序池属性以使用新创建的应用程序池WebServiceAppPool。

我相信这种设置更安全，并且可以避免在连接字符串中硬编码用户 ID 和密码。

希望有帮助。

Colin Mackay has provided detailed answer to your question. Here are my two cents.

If you have Active Directory domain setup in your environment, I would not recommend including user id and password in your connection. Instead, I would suggest the following:

  1. Create a domain account say MyDomain\webservice, where MyDomain would be active directory domain and webservice would be the Windows user account in that domain. In SQL, provide this new domain account user with appropriate permissions to access the database.

  2. In Internet Information Services (IIS) Manager, change the Application Pool to run under this service account. If you are running IIS 7.5, following steps can be performed to do that:

  3. In IIS, expand your <server name> node and click on Application Pools.

  4. It is better to create a new Application Pool, so that you don't disturb the functionality of other applications that might use the application pool ASP.NET v4.0.

  5. Create the new application pool (say WebServiceAppPool) similar to ASP.NET v4.0 application pool, except that the new application pool will use the Identity of the newly created domain account MyDomain\webservice instead of the usual ApplicationPoolIdentity.

  6. On the Advanced Settings option of the virtual directory/site where your web service is deployed to, change the Application Pool property to use the newly created application pool WebServiceAppPool.

I believe that this set up is more secure and also it will avoid hard coding user id and passwords in the connection string.

Hope that helps.

回复 原文
陈独秀 2024-11-17 07:59:56

该消息清楚地表明：登录失败 .....尝试连接到数据库的用户（IIS APPPOOL\ASP.NET v4.0）没有权限由于

不知道任何其他详细信息，我假设这是托管在 IIS 中的 ASP.NET Web 应用程序/Web 服务，并且您很可能有一个到具有 Integrated Security=SSPI 的数据库的连接字符串。 > 设置已打开 - 因此，当前用户（此处为 IIS 应用程序池用户）尝试连接到数据库 - 并且无法

更改您的连接字符串（请参阅 http://www.connectionstrings.com) 指定可以连接的特定数据库用户：

server=YourServer;database=YourDatabase;User ID=SomeValidUser;Pwd=Top$ecret

The message clearly says it: LOGIN FAILED ..... the user (IIS APPPOOL\ASP.NET v4.0 trying to connect to the database doesn't have permissions there.

Not knowing any other details, I'm assuming this is a ASP.NET web application / webservice hosted in IIS, and you most likely have a connection string to the database that has the Integrated Security=SSPI setting turned on - therefore, the current user (here the IIS apppool user) tries to connect to the database - and can't.

So change your connection string (see lots of samples on http://www.connectionstrings.com) to specify a specific database user that can connect:

server=YourServer;database=YourDatabase;User ID=SomeValidUser;Pwd=Top$ecret
回复 原文
~没有更多了~

关于作者

去了角落

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

苦中寻乐

文章 0 评论 0

lueluelue

文章 0 评论 0

嗼ふ静

文章 0 评论 0

王权女流氓

文章 0 评论 0

与花如笺

文章 0 评论 0

残酷

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文