数据库的Web服务：SqlException [英] Web service with database : SqlException

问题描述

我写了一个与SQL Server'08数据库兼容的Web服务

当我尝试调用web方法时，我得到：

  System.Data.SqlClient.SqlException：用户&IIS＃APPPOOL\ASP.NET v4.0&＃39的登录失败。 
在System.Data.SqlClient.SqlInternalConnection.OnError（SqlException异常，布尔breakConnection）
在System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning（）
在System.Data.SqlClient.TdsParser。运行（RunBehavior runBehavior，SqlCommand cmdHandler，SqlDataReader dataStream，BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject stateObj）
在System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin（Boolean enlistOK）
在System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover ServerInfo serverInfo，String newPassword，Boolean redirectedUserInstance，SqlConnection owningObject，SqlConnectionString connectionOptions，TimeoutTimer timeout）
在System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist（SqlConnection owningObject，TimeoutTimer timeout，SqlConnectionString connectionOptions，String newPassword，Boolean redirectedUserInstance）
在System.Data.SqlClient.SqlInternalConnectionTds..ctor（DbConnectionPoolIdentity标识，SqlConnectionString connectionOptions，Object providerInfo，String newPassword，SqlConnection owningObject，Boolean redirectedUserInstance）
在System.Data.SqlClient.SqlConnectionFactory.CreateConnection（DbConnectionOptions选项，对象poolGroupProviderInfo，DbConnectionPool池，DbConnection owningConnection）
在System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection（DbConnection owningConnection，DbConnectionPool池，DbConnectionOptions选项）
在System.Data.ProviderBase.DbConnectionPool.CreateObject（DbConnection owningObject ）
在System.Data.ProviderBase.DbConnectionPool.UserCreateRequest（DbConnection owningObject）
在System.Data.ProviderBase.DbConnectionPool.GetConnection（DbConnection owningObject）
在System.Data.ProviderBase.DbConnectionFactory。在System.Data中的System.Data.SqlClient.SqlConnection.Open（）
上，
在System.Data.ProviderBase.DbConnectionClosed.OpenConnection（DbConnection outerConnection，DbConnectionFactory connectionFactory）中
 
 .Linq.SqlClient.SqlConnectionManager.UseConnection（IConnectionUser用户）
在System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe（）
在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（）
在System.Collections.Generic.List`1..ctor（IEnumerable`1集合）
在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不？

解决方案

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

您需要设置

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

以下是您在SQL Server Management Studio中的分步说明：

• 在对象资源管理器中，打开SQL Server树并进入安全 - >登录分支


• 右键单击登录，然后选择


• 点击高级以获取选择用户或群组对话框。
>

• 点击立即查找


• 滚动对话框底部的列表，找到相关用户并双击。对话框将关闭。


• 按确定。

p>

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

• 打开树的数据库分支。


• 打开所需的特定数据库的分支，然后选择安全 - >用户分支。
  
• 右键单击用户并选择新用户...


• 编写用户名（不必与登录名匹配，


• 在登录名称旁边按...按钮


• 在选择登录对话框中按浏览 >
  
• 在浏览对象对话框中检查所需的登录名。


• 按确定。对话框将关闭


• 按确定。


• 选择用户拥有的模式 - 通常为db_owner


• 选择数据库角色成员资格它将是由DBA创建的特定角色，如果不是则db_datareader& db_datawriter。如果您有问题，请选择db_owner，然后使用您的DBA更正权限（您不想分配ASP.NET进程与数据库所有者权限，除非您真的必须，这是一个安全漏洞等待发生）


• 按确定关闭数据库用户对话框。

或

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

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?

解决方案

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.

这篇关于数据库的Web服务：SqlException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！