share
Stack OverflowUser permission error when accessing "user instance" database from ASP.NET
[+26] [9] Jon Skeet
[2009-07-15 21:17:36]
[ asp.net .net sql sql-server permissions ]
[ https://stackoverflow.com/questions/1134151/user-permission-error-when-accessing-user-instance-database-from-asp-net ]

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.

Relevant details:

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...

(52) OMG, Jon Skeet asked a question? - rlbond
Is DataDirectory a UNC path or mapped drive? - Brian R. Bondy
@Brian: Nope, |DataDirectory| is the way of referring to App_Data in ASP.NET. Worked fine on the old machine, and works fine in my local dev environment... - Jon Skeet
(9) I figure that just about everyone here knows your site, Jon, but I've seen questions being closed as spam because the asker used it to "promote" their site by mentioning and linking it without any necessity... - balpha
(7) To clarify: I'm not trying to accuse you of anything. I just want to avoid the impression of double standards. - balpha
(8) Okay, will edit to remove link. - Jon Skeet
(12) With 4422 answers I think he deserves a small unintentional plug. - Brian R. Bondy
Does the Network Service account have read access to the MDF file? - Janie
Yup - I've given full permission to everyone on both the directory and the files. - Jon Skeet
(2) @Brian: Totally agree... Who cares if he put a link to his site... He has a very valid and legit issue. At over 70K rep points, nobody should complain... - RSolberg
(2) @Jon Skeet: did you try temporarily setting the pool to run as administrator. Does that bypass the error? - Brian R. Bondy
(2) @Jon Skeet: I presume you fixed this issue (the question was posted over a year ago). Any chance you could post the solution if you found one? It'd be helpful if anyone comes across this question with the same issue :-) - Doctor Jones
(3) @DoctaJonez: Unfortunately, I never did. I'm now using an XML file as my storage - it's a lot simpler all round! - Jon Skeet
Looks like you Skeet'ed away from this one with a workaround based on a comment I found. But when you have time, I've provided 2 solutions for you. - JustBeingHelpful
@MacGyver: Thanks for giving them, for posterity - but I'm very happy with the XML solution now :) - Jon Skeet
[+6] [2009-07-16 00:01:18] Remus Rusanu

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 [1]:

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.

[1] http://msdn.microsoft.com/en-us/library/ms165698.aspx

No sign that the child instance was started... and indeed I suspect that's the problem, but I can't see where exactly it's failing. Changing my application pool to run as local system doesn't help either, which is most odd. - Jon Skeet
What's the easiest way of starting a console as NETWORK SERVICE? It would be interesting to try my test app in the same way... - Jon Skeet
I know how to start it as system using AT.exe: at 10:45pm /INTERACTIVE cmd (where 10:45pm is like 1 minute into future). For NETWORK SERVICE I'm at loss because I don't know how one can retrieve it's... password. RUNAS needs is (besides I'm not sure NETWORK SERVICE is allowed to run in runas) and trying to create a dummy service with sc.exe: sc create cmd_as_ns binpath= c:\WINDOWS\system32\cmd.exe start= demand obj= "NETWORK SERVICE" also needs its password (even after wrapping cmd.exe in srvany.exe). - Remus Rusanu
Any restrictions in C:\Documents and Settings\NetworkService\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS that may prevent the copy of the master/model/tempdb? - Remus Rusanu
Running CMD.EXE as Local System: blogs.msdn.com/adioltean/articles/271063.aspx - Pavel Chuchuva
@Pavel: Thanks, but it doesn't work with remote desktop :( Will try at.exe - Jon Skeet
Okay, managed to run my test app as NETWORK SERVICE successfully - will edit question. - Jon Skeet
Jon, I believe your AT.EXE runs as LocalSystem, not NETWORK SERVICE. If you managed to start a child instance from with AT, check the owner of the child instance in dm_os_child_instances and make sure is NETWORK SERVICE. - Remus Rusanu
@Remus: I didn't use at.exe in the end; I used a service. However, I've just checked for the SQLEXPRESS directory you mentioned and it doesn't exist. There's an MSSQLSERVER directory at the same location, which perhaps represents the "." instance... how do I get the appropriate data into a SQLEXPRESS directory? - Jon Skeet
If there is a MSSQLSERVER directory there it means that a default (noname) Express instance was once able to start a child instance for NETWORK SERVICE. The data is placed there by the master instance during the process of creating the child instance. Also check if is not in %WINDIR%\ServiceProfiles\NetworkService\AppData\Local\Micros‌​oft\Microsoft SQL Server Data\SQLEXPRESS - Remus Rusanu
But anyway, if you have ur child instance running, does it open/attach your MDF of choice? - Remus Rusanu
@Remus: There are 2 SQL Server instances running - "." and "SQLEXPRESS". The SQLEXPRESS instance doesn't have any child instance running. The "." one has three, one of which is under NETWORK SERVICE - Jon Skeet
... but that one was dead. I've managed to connect to the one running under LOCAL SYSTEM. Just about managed to attach the database, too. - Jon Skeet
So it meas you're creating your child instances under the "." master, which means the "MSSQLSERVER" folder in 'Application Data\...' is correct. Which is all expected since your data source is "."... one of those doh! moments lol, haven't even bother to look at ur connect string before. - Remus Rusanu
Sounds like NETWORK SERVICE is able to create the child instance and attach the MDF, so the problem it appears to be just the time it takes to create the instance on first request. The child instances last 60 minutes by default, after last access. You need to increase the connect timeout in the connection string to something like 60 seconds to allow for the creation of the child instance, if is not started. - Remus Rusanu
@Remus: I don't think I'm creating the child instances under MSSQLSERVER - there are other web apps which may well have been doing it. I don't think the timeout would do it, otherwise subsequent requests would succeed, wouldn't they? - Jon Skeet
SO is great, but posting comments back and forth to troubleshoot is kinda inefficient. Shoot me a mail on my site contact or tweet @rusanu, I can probably help if we have a faster turnaround for chit-chat. - Remus Rusanu
1
[+3] [2011-12-21 23:42:42] user806549

This question appears related to: "Cannot open user default database. Login failed." after installing SQL Server Management Studio Express [1]

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.

[1] https://stackoverflow.com/questions/8528768/cannot-open-user-default-database-login-failed-after-installing-sql-server-m/8583258#8583258

Thanks - will do if I ever come across it again. Fortunately I moved away from using a database at all for this particular problem. Just an XML file did just as well, and was a lot simpler :) - Jon Skeet
2
[+2] [2009-07-15 21:25:04] Brian R. Bondy

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.

Source [1]

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).

[1] http://msdn.microsoft.com/en-us/library/bsz5788z.aspx

Just set it at server level too, no joy. - Jon Skeet
Maybe try restarting the SQL service and try restarting the w3svc service after that change. - Brian R. Bondy
Might want to verify the permissions of that newly added login too. - Brian R. Bondy
(1) Yup, restarted various things. I just wish I could work out which process to look for failing... it doesn't seem to be any of the usual suspects. - Jon Skeet
3
[+2] [2009-07-15 21:28:17] Fredrik Mörk

Integrated Security=True

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?


There are no user logins in the database - it's just a database file. - Jon Skeet
4
[+1] [2009-07-15 22:46:58] Dave Sussman

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.


I presume you've checked that your SQL Server supports user instancing? See msdn.microsoft.com/en-us/library/ms254504(VS.80).aspx. SQLEXPRESS supports this by default, but you're not connecting to SQLEXPRESS so it's possible it's not enabled. - Dave Sussman
5
[0] [2009-07-15 21:23:24] Otávio Décio

The first thing I would do is fire off filemon (or better Process Monitor) [1]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.

[1] http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx

6
[0] [2009-07-15 21:43:54] blowdart

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?


@blowdart: Directory, yes. User instances enabled could be it - where's that? - Jon Skeet
My partner in crime says he's checked that user instances are indeed enabled. - Jon Skeet
OK, try removing the user instance parameter temporarily and see what happens. Just in case it's gotten very confused. They're going away in 2010 anyway :) - blowdart
I guess that should work. This is what the link, I gave below says. - shahkalpesh
Yup, that changes things in a pretty explicit way: "System.Data.SqlClient.SqlException: Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances." - Jon Skeet
And then if you do that and switch it back? (You may need to manually detach the database first) - blowdart
Yes, I re-enabled user instances and I'm getting the same problem. - Jon Skeet
Weird, that should rule out a permissions problem then - blowdart
7
[0] [2009-07-15 23:20:28] Dillie-O

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 [1].

[1] http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

I don't think it's the same sort of tool, but gave it a try anyway. Doesn't look like it's really for use with user instances though. - Jon Skeet
Bummer. I was thinking that too, until I looked towards the end of the article and noticed the configuration setting had the AttachDbFilename going with it and thought it might help. - Dillie-O
8
[0] [2012-08-07 03:46:01] JustBeingHelpful

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.


9