Contact   •   Products   •   Search

Rick Strahl's Web Log

Wind, waves, code and everything in between...
ASP.NET • C# • HTML5 • JavaScript • AngularJs

SQL Server Express in APP_DATA and running with IIS


I haven't really been paying much attention to SQLEXPRESS and the APP_DATA directory in the past but over the last couple of days of finalizing my slides for the ASP.NET Connections conference I realized that it's one heck of a lot easier to ship a database in APP_DATA then to have scripts or even something like Red Gate's Sql Packager generated database. Sql Packager BTW, is an excellent tool if you need to shuttle around databases frequently between machines as it packages databases into an EXE that you can move and simply install on another machine. It's much faster than anything native including attaching/detaching.

 

Anyway, using APP_DATA and shipping a detached database is also very easy and it works great if you have SQL Express installed and you're running with the built-in ASP.NET 2.0 Web server.

 

However, I've not been able to get APP_DATA to work with IIS. I get security errors which basically point at the ASPNET account not having rights to the database. I'm not sure exactly how SQLExpress deals with permissions in this scenario, but it's not recognizing the ASPNET account as a valid user even though on this demo machine it is set up as an Admin account (yeah I know bad idea, but for demos this is lets me use integrated security so I don't have to expose passwords to the world). I also made sure I added ASPNET to APP_DATA ACL list and gave it full permissions.

 

I figured that SQLEXPRESS must be triggering off the impersonated account in some way, but in that case ASPNET should work the same as my login account both of which are Admin at this point. And it's not…

 

The workaround I suppose is to attach the database and then administer the security policy properly through SQL Security, but given that the expectation is SQLEXPRESS this is not so transparent since SQL Express doesn't come with Administration tools (although you can download a beta version of the Express Management Studio).

 

Is there an easier way?

Make Donation


Feedback for this Post

 
# re: SQL Server Express in APP_DATA and running with IIS
by soccerdad March 31, 2006 @ 10:33am
I can't directly answer your question. Based on what you're saying, if I had no further clues, I'd turn failure auditing on for the database file in App_Data and then review the event log to see which account was actually attempting to access the file.

FWIW...
# re: SQL Server Express in APP_DATA and running with IIS
by Andrew Robinson March 31, 2006 @ 11:33am
Rick, are you using the User Instance as part of your connection string?

connectionString="Data Source=.\SQLExpress;AttachDBFilename=|DataDirectory|MyDataBase.mdf;Integrated Security=True;User Instance=True;
# re: SQL Server Express in APP_DATA and running with IIS
by Jacques PHILIP March 31, 2006 @ 1:35pm
I have been attaching and managing the DB in Express Management Studio, setting the DBOwner and Public roles to the ASPNET account for that DB, it works and I find it easier to manage the database in Express Management Studio than VS which already cluttered enough with the project files.
# re: SQL Server Express in APP_DATA and running with IIS
by Rick Strahl March 31, 2006 @ 1:42pm
That's what I have and as I mentioned it works fine with the local server.

Jacques - I agree and that's usually what I do with my databases in APP_DATA: I attach them to my full local SQL Server. Not only is it still easier to manage data and queries etc. in Management Studio but it also reduces the application load time which is horrible with the local database needing to attach.

I'm more thinking of the ease of use of people who download the demo and only have SQL Express and nothing else.
# re: SQL Server Express in APP_DATA and running with IIS
by David Neal April 04, 2006 @ 5:17am
I found that if I changed the service account for SQL Server Express to Local System, then my IIS issues went away. Of course, this probably has some serious security ramifications, but at least it works.
# re: SQL Server Express in APP_DATA and running with IIS
by Denny April 30, 2006 @ 6:17am
how I did it:

connect to the db via express manager, rename the database from the default [C:\bar\foo\aspnetdb.mdf]
to aspnetdb

then edit the config to just database=aspnetdb
and forget the attach and user instance.

works now.

Oh and I did add NT AUTHORITY\Network service as a valid user/login
# re: SQL Server Express in APP_DATA and running with IIS
by Rick Strahl April 30, 2006 @ 11:43am
Denny, great tip! Didn't know that could be done - I agree that is a good way to distributed.

Unfortunately if you attach the database now and you have many of those you can run into problems with the name.
# re: SQL Server Express in APP_DATA and running with IIS
by Eron Wright June 05, 2006 @ 1:20pm
My solution to allow IIS to use an auto-attached App_Data\ASPNETDB.MDF was to add read/write access for Network Service to App_Data. It works, and I did not touch the login/user settings.
# re: SQL Server Express in APP_DATA and running with IIS
by Kristo September 09, 2006 @ 4:19pm
I found a solution that may work .. after a lot of reinstalling, googleing, ...
the simple answer is to let the SQL Express services run under the System account instead of the Network Services Account ...
Hope this helps..
# re: SQL Server Express in APP_DATA and running with IIS
by Jim Selleck October 19, 2006 @ 2:35pm
Kristo!!!

Many thanks. You ended several DAYS of beating my head against the wall with your simple fix. YES. Changing SQL Express services to run under Local System instead of Network Services fixed ALL of my problems at once.

The root of my issue is I have group of setup scripts that live on the C: drive. The C: drive is NOT shared, so Network Services does not trust it. Therefore, I got "Access is denied" every time I tried to run a setup script via OSQL.
# DotNetSlackers: SQL Server Express in APP_DATA and running with IIS
by DotNetSlackers Latest ASP.NET News October 26, 2006 @ 12:20am
# re: SQL Server Express in APP_DATA and running with IIS
by Mike March 14, 2008 @ 10:49am
So I've tried all of these, but can't get it to work. I am trying to talk to the SQL Express DB asynchronously through a WCF service on behalf of a Silverlight 2 beta 1 control.

Are the Silverlight security policies the problem? Thanks in advance.
# re: SQL Server Express in APP_DATA and running with IIS
by Mike March 14, 2008 @ 10:59am
I take it back, it works within VS 2008, so I don't think it's Silverlight, it's gotta be something with IIS or SQL Express.
# re: SQL Server Express in APP_DATA and running with IIS
by Will September 15, 2008 @ 12:30pm
errrr, ummmmmm... newbie question:

how do you change which account Sql Express runs under. Do you have to reinstall?
# re: SQL Server Express in APP_DATA and running with IIS
by Rick Strahl September 15, 2008 @ 12:51pm
@Wil - Offhand I'm not sure how you can do this through the UI, but you can certainly change the account in the SQL Service in Services... That should do the trick.
# Could not attach database '' to file 'c:\inetpub\wwwroot\SqlServer\App_Data\imsys_Data.MDF'.
by vipin kohli February 02, 2009 @ 3:04am
hi to all,

i have delpoyed my website to ftp and iis server,, the site is working well,, but the problem is, when i am debuging the website using visual web developer 2008 then the database is working well, but after the deployment it does not wrok... everytime i got the error :

Could not attach database '' to file 'c:\inetpub\wwwroot\SqlServer\App_Data\imsys_Data.MDF'.

this may be due to change of path.. from sql/data/<database Name> to c:\inetpub\wwwroot\<website>\<database>.mdf

please help me what to do with this

thanx
regards
# re: SQL Server Express in APP_DATA and running with IIS
by shalu October 26, 2009 @ 2:17am
use <identity impersonate="true" in web.config file .it will work
 


West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2014