Geek Answers Handbook

Web service with database: SqlException

I wrote a web service that works with SQL Server'08 database

When I try to call a web method, I get the following:

System.Data.SqlClient.SqlException: Login failed for user &#39;IIS APPPOOL\ASP.NET v4.0&#39;. 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? Another win forms application using the same database works fine, why doesn't WS do?

0
source share
3 answers

The web service will run under a different user account from the WinForms application. The WinForms application will work with the user account logged in to the user, while the web service will have an account that uses ASP.NET.

You need to install this account in SQL Server and grant it the appropriate permissions.

Basically, you need to add the username to the server, and also add that login as the user for each database that requires it.

Here are step-by-step instructions when you are in SQL Server Management Studio:

  • In the object explorer, open your SQL Server tree and go to the Security → Logins branch
  • Right-click "Logins" and select "New Login ..."
  • Next login, click Search
  • Click Advanced to open the Select User or Group dialog box.
  • Click Find Now
  • Scroll to the bottom of the dialog box and find the corresponding user and double-click it. The dialog closes.
  • Click OK. The dialog closes.
  • In the Login - New dialog box, click OK.

Now you have a new entrance.

Now to create a user in the database.

  • Open the data branch of your tree.
  • Open a branch for a specific database, then click "Security" → "Users".
  • Right-click "Users" and select "New User ..."
  • Enter the username (it should not match the login, but usually it).
  • Next to the name "Username" click "..."
  • Click Browse in the Login Selection dialog box.
  • Check the login name in the Browse Objects dialog box.
  • Click OK. A dialog box opens
  • Click OK. The "Select Input" dialog box appears.
  • Select "Schemas owned by this user" - usually "db_owner"
  • Choose a membership in the database role - as a rule, this will be a special role that the DBA created, if not, then db_datareader and db_datawriter. If you have problems, select db_owner, then work with your database administrator (DBA) to subsequently fix the rights (you do not want to assign the ASP.NET process with the permissions of the owner of the database, if you do not want to, this will be a security violation expected )
  • Click OK to close the Database User dialog box.

Everything should be fine now.

OR

You can change the connection string in the web service so that it connects to SQL Server using a specific account.

+3
source

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

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

  • Create a MyDomain\webservice domain MyDomain\webservice , where MyDomain will be the active directory domain and webservice will be the Windows user account in this domain. In SQL grant this new user a domain account the appropriate permissions to access the database.

  • In Internet Information Services (IIS) Manager change the application pool to run under this service account. If you are using IIS 7.5, you can follow these steps:

  • In IIS expand <server name> node and click Application Pools .

  • It is better to create a new Application Pool so that you do not interfere with the functionality of other applications that can use the ASP.NET v4.0 application pool.

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

  • In the Advanced Settings option of the virtual directory / site on which your web service is deployed, change the Application Pool Property property to use the newly created WebServiceAppPool application pool.

I believe this setting is more secure and will also avoid hard-coding the user ID and passwords in the connection string.

Hope this helps.

+2
source

The message clearly states: ENTRANCE MALFUNCTION ..... user ( IIS APPPOOL\ASP.NET v4.0 tries to connect to the database, does not have rights there.

Without knowing any other details, I assume that this is an ASP.NET/webservice web application hosted in IIS, and you most likely have a database connection string with the Integrated Security=SSPI setting enabled, therefore, the current user ( here the IIS apppool user) tries to connect to the database - and cannot.

So, change the connection string (see lots of samples at http://www.connectionstrings.com ) to indicate a specific database user who can connect:

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

More articles:


All Articles