The server hosting csharpindepth.com has recently been migrated.
Most of it is working fine, but I can't get at the database used to hold errata, notes etc.
Data Source=.;AttachDbFilename=|DataDirectory|\WebSiteData.mdf; Integrated Security=True;User Instance=True
I'm connecting just by creating a new
WebSiteDataContext (which has the above connection string as its default)
Using a small test console app running from the directory containing the files, as the administrator account, using the same query, I can see the contents of the database.
In ASP.NET I'm getting this exception:
SqlException (0x80131904): User does not have permission to perform this action.
EDIT: More information, here's the stack trace:
[SqlException (0x80131904): User does not have permission to perform this action.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844759 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +35 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +144 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +342 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +221 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +189 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +4859187 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +31 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +433 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +499 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +65 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +117 System.Data.SqlClient.SqlConnection.Open() +122 System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(IConnectionUser user) +44 System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe() +45 System.Data.Linq.SqlClient.SqlProvider.InitializeProviderMode() +20 System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) +57 System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +35
EDIT: I was wrong about the filename having to be right - changing the connection string to make it talk to a different file doesn't change the error.
ProcMon never shows it touching the file...
EDIT: Further oddities - restarting the application pool with it running under the Local System account still has problems, which is about as odd as it gets! It's as if, despite the error message, it's actually trying to do something impossible (e.g. with the wrong path) rather than just being a permissions issue.
EDIT: More information - I've just run my little test console app from a service as "NETWORK SERVICE" and (after an initial attempt which timed out) it was successful. So it's not a permissions thing in terms of the user account... it's something about the environment in which it runs...
Does the child instance even start? When a RANU database is requested the master instance (.\SQLEXPRESS in this case) has to create a 'child' instance, in other words start the sqlservr.exe process as an user process running under the credentials of the user that requested the RANU connection from the .SQLEXPRESS instance. In this case the instance would have to be started as 'NETWORK SERVICE'.
To validate if the child instance is started, connect to the master instance (.\SQLEXPRESS) and check sys.dm_os_child_instances :
SELECT * FROM sys.dm_os_child_instances
If an NETWORK SERVICE owned child instance is started, take it's
instance_pipe_name and connect straight to the child instance:
sqlcmd -S np:\\.\pipe\<child pipe name>\tsql\query
Ideally, connect as NETWORK SERVICE (eg. from an interactive cmd console started as NEWTORK SERVICE, perhaps using at.exe to schedule it 1 minute in future). If that works, the last step is to try to attach the MDF using ordinarry sp_attach_db.
The idea of these steps is not to solve the issue, but pinpoint the failure cause, since the error you get is kinda generic and ... not exactly helpfull. http://msdn.microsoft.com/en-us/library/ms165698.aspx
This question appears related to: "Cannot open user default database. Login failed." after installing SQL Server Management Studio Express 
Apparently User Instance and SQL Server Express has some interesting issues. You might want to take a look at http://blogs.msdn.com/b/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx. This seems to do the trick for most who have this problem. http://stackoverflow.com/questions/8528768/cannot-open-user-default-database-login-failed-after-installing-sql-server-m/8583258#8583258
Have you done this?
To configure SQL Server for Windows integrated security
From the Windows Start menu, select Microsoft SQL Server, and then select Enterprise Manager.
Open the node for the server and expand the node for the database you want to give users permissions for.
Right-click the Users node and select New Database User.
In the Database User Properties dialog box, enter domain\username in the Login name box, and then click OK. Additionally, configure the SQL Server to allow all domain users to access the database.
So basically verify that you have "NT AUTHORITY\NETWORK SERVICE" listed there.
Also in SQL 2008 at least, not sure about 2005 you would set it at the server level too (Server -> Security -> Logins). http://msdn.microsoft.com/en-us/library/bsz5788z.aspx
Is the user logon(s) in the database connected to user accounts in the machine? Could it be that there is some mapping issue around that?
If you're using user instancing your're connecting as admin anyway, which incidentally is why it's not recommended for hosting scenarios. The perms problems I tend to get are physical ones on the files themselves, not the folders - detaching database has a tendency to strip perms.
It might help if you can narrow down exactly where the exception is happening. What action is it trying to perform? Database open, read, write? Also try another database (ie, is it that particular database) and try a non-user instanced version; attach the database to an instance.
The first thing I would do is fire off filemon (or better Process Monitor) to see if the IIS process is able to open the database file - it looks like a security issue to me.
Also: Make sure that the ~/App_Data folder is not read-only. Also, check if you have granted full permissions to the "Network Service” account for the same folder.
You may want to run IISRESET to restart IIS and to refresh its permissions as well. http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx
When you say users have full permissions to the file, do all users have full permissions on the app_data directory? Sql will need to create a lock file along side the MDF file.
Also are you sure the SQL Express instance has user instances enabled?
Have you tried running aspnet_regsql.exe on the server to potentially kick something back into place (sort of like how have to run aspnet_regiis once in blue moon)? Scott Gu has a basic overview on the whole process found here . http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx
Since you have Trusted Authentication=True, the connection uses the security context of the calling process. That means you're running the development server in the security context of the logged in user, so every thing works fine. When you run in IIS, you are in the security context of the application pool process, which is NETWORK SERVICE, which does not have a user profile, therefore it crashes.
To fix, use solution #1 or #2.
1.) Change the identity of the application pool to a normal user with access to the database, as a custom account. HINT: set this user:
SELECT owning_principal_name FROM sys.dm_os_child_instances WHERE heart_beat = 'alive'
2.) Use a connection string with user name and password; meaning use SQL Server Authentication, not Trusted Authentication=True.