SqlException: User does not have permission to perform this action.

Question

I have been using my website (ASP.NET MVC) in visual studio but now I want to host it on my server. I published from visual studio onto the network share to be used. The server is running Windows Home Server, IIS 6 and SQL Server 2008 R2 (express).

In Microsoft SQL Server Management Studio, I've attached the database and made sure that the user IUSR_SERVER is owner of the db. I also made sure that the user Network Service has access.

The Web Site is configured in IIS to run anonymously as IUSR_SERVER. I have granted write and read access to IUSR_SERVER as well as Network Service in the file system and made sure that nothing is read only.

The web.config has this connectionstring:

        <connectionStrings>
    <remove name="ApplicationServices" />
  <add name="ApplicationServices" connectionString="Data Source=.\SQLExpress;Integrated Security=True;Initial Catalog=MyDatebase"
   providerName="System.Data.SqlClient" />
     </connectionStrings>

However, I cannot browse my web site. I only get this error:

Server Error in '/' Application.

User does not have permission to perform this action. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: User does not have permission to perform this action.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): User does not have permission to perform this action.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846887 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194

Feels like I've tried everything. Would be very grateful for your aid in this.

EDIT:

This is the event in the event viewer:
Event code: 3005 
Event message: An unhandled exception has occurred. 
Event time: 2010-06-05 23:55:08 
Event time (UTC): 2010-06-05 21:55:08 
Event ID: 88a3a76c00a34e21bf6e711b3b3a8f21 
Event sequence: 137 
Event occurrence: 59 
Event detail code: 0 

Application information: 
    Application domain: /LM/W3SVC/6893/Root-2-129202466028125000 
    Trust level: Full 
    Application Virtual Path: / 
    Application Path: C:\Inetpub\WebPage\ 
    Machine name: SERVER 

Process information: 
    Process ID: 5864 
    Process name: w3wp.exe 
    Account name: SERVER\USER

Exception information: 
    Exception type: SqlException 
    Exception message: User does not have permission to perform this action. 

Request information: 
    Request URL: http://localhost/ 
    Request path: / 
    User host address: 127.0.0.1 
    User:  
    Is authenticated: False 
    Authentication Type:  
    Thread account name: SERVER\USER

Thread information: 
    Thread ID: 1 
    Thread account name: SERVER\USER
    Is impersonating: True 
    Stack trace:    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   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.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, 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)     

Custom event details: 

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Answer 1

Have you verified that the IUSR_Server account has full access to the SQLExpress database in the filesystem?

You could also try making the application pool in IIS run as IUSR_Server (the application is running in its own app pool right?). Then change your website security for the anonymous user back to the default. When you do this make sure the IUSR_Server account is a part of the IIS_WPG group, this will give the account all of the correct permissions for impersonation in the application pool.

Answer 2

Permissions are not by the user id, rather by an internal id that SQL Server holds for the login. Since these are two servers, the internal ids for the login name is different on both machines.

When you move databases across servers, you have to fix users logins because they become orphaned. The internal ID is different on the new server, so you fix the users login by running the following TSQL command:

EXEC sp_change_users_login 'Auto_Fix', 'IUSR_Server'

There is a user in a database, but there has to also be a login on the server. You have to check that both of them exist for the issue to be resolved (if it has to do with the database user being an orphan)

Answer 3

Turns out that the problem was that my DataContext was using it's own connectionstring, which was not in the web.config. Therefore, it was never changed and the connection could not be made. I changed my code so that my datacontext used the string from the web.config and it worked :)

Answer 4

At following line No. 3

Thread information:
Thread ID: 1
Thread account name: SERVER\USER

<-- what is SERVER\USER, is it your IUSER or Network Service? if it is Network Service, (usually it is ID of application pool), your this Thread Account Name should have access to your Sql server database. The ideal solution would be to either change configuration by giving explicit credentials or make sure Thread account name has complete access (read/write) access to your database.