sql-server database web-services sqlexception

Web service with database : SqlException

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 &#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? Other win forms application what uses the same DB works fine, why WS doesn't?

Solution

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.

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