Failed to generate a user instance of SQL Server due to failure in retrieving the user's local

Current Process:
What ASP.NET 2.0 does by default with application settings is create SQLExpress database within the App_Data folder of the application.

If you look closely at the default connection string (LocalSqlServer), it actually attempts to dynamically attach the .mdf to the SQLExpress instance (.\SQLExpress). That is just plain stupid from a security and infrastructure standpoint. Not only will you need to use impersonation if you have IIS Authentication set to Anonymous only (which you have to use roles and users anyway!), but that user must have administrative rights in SQLExpress and on your local box. And that doesn't even bring in troubleshooting the procedure if something fails in the attaching or creating of the database. It is a nightmare that has caused me to re-install VS2005, SQLExpress and the .NET Framework to clear.

One Solution:
So, what's the alternative? Since all applications will use the same database, why not just create it yourself, instead of dynamically attaching it. That way you can give the ASPNET account the permissions to access the aspnetdb database without being able to do everything else (good security alternative). Also, you'll take the whole complicated attaching process out of the mix.

How do you do this? Well, this is what I did on Win XP running SQLExpress and VS2005 prof:

Change ASP.NET settings

1) Open up the iis mmc (Administrative Tools > Internet Information Services) and either right click on Default Web Site (if you want this to apply to all current and future web apps) or on the specific web app with the problem.
2) Choose the ASP.NET tab (make sure the version begins with 2) and click Edit Configuration.
3) On the General tab, change the LocalSqlServer connection string to:
data source=.\SQLEXPRESS;Integrated Security=SSPI;initial catalog=aspnetdb
4) On the Application tab, make sure you are NOT using impersonation. Check that the Local Impersonation checkbox is cleared (which it is in the default installation).

Create and Configure the aspnetdb for use with ASP.NET

1) To create the database, you need to choose the Visual Studio 2005 command prompt (under Visual Studio Tools menu).
2) On the commandline, type aspnet_reqsql. This will launch a wizard. Keep clicking next. The only setting you might have to adjust is the instance name under the Select the Server and Database step. If you have any other instance of SQLExpress or SQL 2000/2005 installed, then you could use that, but then you'd have to change your ASP.NET LocalSqlServer connection string.
3) When it is done, it will create the aspnetdb database on your SQL instance. Now, you need to configure the ASPNET account for accessing and writing to it.
4) Open SQL Server Management Studio (if you don't have it, then download the client tools for SQL Server 2005) and create a new login for the ASPNET account.
5) Expand the Security directory and then right-click on the Logins to create a new Login
6) Type in ASPNET as the login name (keep Windows Auth selected)
7) On the User Mappings page, give the ASPNET account full access (check all of the roles if you like) to the aspnetdb database.

Then, you're done. You shouldn't have to restart anything, but iis can always use it. Start->Run, then iisreset will do the trick. Now, you have a permanent db installed in SQLExpress (or SQL Server 2000/2005 with some adjustment) with the least ammount of privilege given to the ASP.NET process.

This setup is also a lot easier to debug later! I hope that helps.

thePrisoner06 at 2007-9-8 >

I have spent several days also trying to work around this problem.

The database is the aspnetdb with the membership provider. When the site was added to the production server, it was setup using it's own aplication pool.
I tried setting up a loggin for web01\sitename.com_web as login on the sql server but to no avail. Finally I moved the site out of its own application pool and all worked.

Hope this gives some help on other places to look.

Bobmmp at 2007-9-8 >

It gave me the path to solve the problem... after going to Edit Configuration on Asp.Net tab, I saw there are TWO connection strings (the default and my own connstring). My web.config had just one. I made the two the same value and now the website is working.

Really thanks for the light.

CesarRonchese at 2007-9-8 >