Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance

Question

No wonder Microsoft is giving it away for free. It’s not worth anything. I am very disappointed in this version of SQL server. I decided to start learning VB so I downloaded and installed VB 2005 express edition and SQL 2005 express. I tried to build a personal website using the starter kit. I kept getting the following error message, for which I have seen numerous postings on this forum.

“Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.”

I went out and bought the Standard version of VS 2005 thinking there may be a feature that the free express version is lacking. I uninstalled the express version, cleaned the registry, deleted all the folders it created and installed the Standard edition. Then I tried to create a personal web using the starter kit and it does the same thing again.

I can not create a database file, connect to a database file. I can not create or attach a database on the SQL 2005 express from VS 2005. The SQL server Express seems to be working OK since I can attach to it using Microsoft SQL Server Management Studio Express. There is something about the way VS tries to connect to the database server that is not working. VB does recognize the local computer as a SQL server but can not connect to it.

SQL server is set to accept Shared memory, TCP/IP and named pipes for local clients.

What is causing this?

This is some information on the system I am using:

 

MS XP (SP2)

 

Microsoft Visual Studio 2005

Version 8.0.50727.42  (RTM.050727-4200)

Microsoft .NET Framework

Version 2.0.50727

Installed Edition: Standard

 

Microsoft SQL Server Management Studio Express                                                                  9.00.1399.00

Microsoft Data Access Components (MDAC)                                                              2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)

Microsoft MSXML                                                                  2.6 3.0 4.0 5.0 6.0

Microsoft Internet Explorer                                                                   6.0.2900.2180

Microsoft .NET Framework                                                                 2.0.50727.42

Operating System                                                                     5.1.2600

 

I am now to programming but nor computer illiterate. I have been working with MS SQL servers since version 6.5 and have MCSE, Network+, A+ and Security plus certifications. Installing and using VS should not be this problematic. What is wrong with Microsoft?

Answer 1

I got this very frustating message everytime I was trying to create a new SQL Express database from VB Express.  All forums seem to point to 2 possible resolutions, NONE of which worked for me:

A) Known bug with Windows XP SP2 when using SQL via RDP (Remote Desktop).

B) Delete C:\Documents and Settings\USERNAME\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS.

The clue for me was all forums relating to solution B seemed to talk about possible authentication problems which gave me a clue.  My solution was therefore:

Solution:  Modify the SQL Server (SQLExpress) service to use your local or system account and restart the service.  This worked for me first time.  I'm not sure if this will cause any problems in the future but hey, at least I can now create a database!!

Hope this helps!!

Answer 2

As far as the "Failed to generate a user instances..." error, check out the error log for the user instance, it will likely give you more information about what is happening. You will fine the error log at C:\Documents and Settings\<user name>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS.

User Instances are a new feature in SQL Express that have some special characteristics. You may want to read the white paper that discusses them to get some background. Creating a database from within VS is actually different that creating one in SSMSE because you're working against different instances of SQL Express. VS uses User Instances by default, where SSMSE works against the main instance of Express.

Regards,

Mike Wachal
SQL Express team

----
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

Answer 3

This error is often caused because the parent instance (for whatever reason) can't copy the system databases to the users local isolated storage folders. Sometimes it is because of a previous install of SQL Express has left files in that directory which is why Reen's suggestion sometimes works

Delete C:\Documents and Settings\USERNAME\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS.

the only trick is knowing the USERNAME, if your writing a web app, the user is usually ASPNET which is usually found in

C:\Documents and Settings\COMPUTERNAME\USERNAME\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

Other times it is a permissions issue which can be solved by giving the SQL service account the necessary permissions.

One thing to remember is that although user instances is the default when working with Visual Studio, it is not mandatory to use them, it is quite possible to attach to databases in the traditional way from SQL Express, to do this you just need to edit the connection string and delete the "user Instance=true". User Instances just make it easier (when it does work) to work with databases as though they were files.

 

Answer 4

This error is simply ridiculous! Not so much because it occurs a lot but that there is insufficient official documentation about this problem. Worst still you will run into this error if you try and copy the official online VS Express tutorial I found. Worse again, when Microsoft ran a seminar at my University this error occurred and they didn’t even know how to deal with it!

This forum thread right here is by far the best documentation of the problem that I have found so far and it’s not very easy to find or thorough.

Answer 5

There seem to be so many things that cause this user instance error.  Currently I think the following explains why I'm getting it today.  Next they will tell us that it only works when there is a full moon!

(Note the mention of SSEUtil not working... I think that's quite interesting)

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=58741&SiteID=1

I think I found the problem, I was not the first one to use the computer the days with the problem.  Even SSEUtil gives the same error.  When I switched users to the one that signed on first, SSEUtil worked. I tried signing off of the other computer users and restarting the SQL Server. SSEUtil still could not get the server to respond.

It looks like bottom line....If you need to use a sqldatabase with visual basic 2005 express,  make sure you signed on the computer first or reboot before you start or return to your database project.

Mike

Answer 6

The post you reference is from before the final release of SQL, so it's hard to say if the information still applies. Why do you think the above explains your situation? What troubleshooting have you done to check this? Have you checked the error logs for both the main instance and the User Instance?

I'd really like to see actual information about this problem as that is the only way to really understand what is happening.

Regards,

Mike Wachal
SQL Express team

Answer 7

Fair enough.

I am 90% sure that when another account has been logged in before mine this error occurs.  I will try generating this error when I have time.

When I checked the error log briefly there did not seem to be any relevant information.  Additionally when I generated this error the error log did not update.  Implying that this error was not logged or maybe I was looking in the wrong place (not both instances)... again I will have to look into this further when I have more time.

I am using VS2005 (not express edition - thanks to my university) and the version of SQL Server that comes with it (Express).

If you browse the web you will find many different "solutions" to this error... I am happy to help where I can

Answer 8

When I ran into this problem the first time, I uninstalled the SQLExpress and reinstalled it. I could then access the database via the Visual C# 2005 Express Edition.  Later, after I used the SQL management console to open and explore the SQL Express, the problem reoccurred.  I uninstalled and reinstalled the SQL Express again, and it works again. 

 

I don’t profess to understand why it works this way, but it did.

Answer 9

Apparently you have to be logged in as jking. And you have to have a D: drive.

SQL Express guys, know who that is?

I cannot get the start kits or time tracker kit going. The start kit does not have an aspnet.mdf. The personal.mdf will not attach or allow a login.

I changed the service to login from the local system account, no change.

I opened Personal.mdf in sql management studio and got:

Directory lookup for the file "D:\Documents and Settings\jking\My Documents\Visual Studio 2005\WebSites\WebSite1\App_Data\Personal_log.LDF" failed with the operating system error 21(the device is not ready)... error 5133

Teach me to use jking's code.

 

 

Answer 10

Login Order Problem Confirmed
Opened VS2005, can refresh database (I am the 1st and only user to log in)… reset computer…

Logged in as user A then switched to user B, opened VS2005, tried to refresh a database…
Timeout Error!
Try again and…
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.


Below is the error log.  Note that when I generate this error the error log does not get updated so I expect the following to be useless.

Where would I find the ‘main instance’ error log?  There is nothing in All Users… not sure where else to look

Anything else?



C:\Documents and Settings\Kevin\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log

2006-04-11 15:30:23.40 Server      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
    Oct 14 2005 00:33:37
    Copyright (c) 1988-2005 Microsoft Corporation
    Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2006-04-11 15:30:23.40 Server      (c) 2005 Microsoft Corporation.
2006-04-11 15:30:23.40 Server      All rights reserved.
2006-04-11 15:30:23.40 Server      Server process ID is 2912.
2006-04-11 15:30:23.40 Server      Logging SQL Server messages in file 'C:\Documents and Settings\Kevin\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log'.
2006-04-11 15:30:23.40 Server      Registry startup parameters:
2006-04-11 15:30:23.40 Server           -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2006-04-11 15:30:23.40 Server           -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2006-04-11 15:30:23.40 Server           -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2006-04-11 15:30:23.40 Server      Command Line Startup Parameters:
2006-04-11 15:30:23.40 Server           -d C:\Documents and Settings\Kevin\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\master.mdf
2006-04-11 15:30:23.40 Server           -l C:\Documents and Settings\Kevin\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\mastlog.ldf
2006-04-11 15:30:23.40 Server           -e C:\Documents and Settings\Kevin\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log
2006-04-11 15:30:23.40 Server           -c
2006-04-11 15:30:23.40 Server           -S SQLEXPRESS
2006-04-11 15:30:23.40 Server           -s AE669D1F-CC06-41
2006-04-11 15:30:23.40 Server           -w 60
2006-04-11 15:30:23.42 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2006-04-11 15:30:23.42 Server      Detected 2 CPUs. This is an informational message; no user action is required.
2006-04-11 15:30:23.87 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2006-04-11 15:30:23.89 Server      Database Mirroring Transport is disabled in the endpoint configuration.
2006-04-11 15:30:23.89 spid4s      Starting up database 'master'.
2006-04-11 15:30:23.95 spid4s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2006-04-11 15:30:24.00 spid4s      SQL Trace ID 1 was started by login "sa".
2006-04-11 15:30:24.03 spid4s      Starting up database 'mssqlsystemresource'.
2006-04-11 15:30:24.20 spid7s      Starting up database 'model'.
2006-04-11 15:30:24.20 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\AE669D1F-CC06-41\tsql\query ].
2006-04-11 15:30:24.20 Server      Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2006-04-11 15:30:24.21 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
2006-04-11 15:30:24.21 spid4s      Server name is 'KEVINS\AE669D1F-CC06-41'. This is an informational message only. No user action is required.
2006-04-11 15:30:24.29 spid4s      Starting up database 'msdb'.
2006-04-11 15:30:24.42 spid7s      Clearing tempdb database.
2006-04-11 15:30:24.85 spid7s      Starting up database 'tempdb'.
2006-04-11 15:30:24.92 spid4s      Recovery is complete. This is an informational message only. No user action is required.
2006-04-11 15:30:24.93 spid10s     The Service Broker protocol transport is disabled or not configured.
2006-04-11 15:30:24.93 spid10s     The Database Mirroring protocol transport is disabled or not configured.
2006-04-11 15:30:24.95 spid10s     Service Broker manager has started.

Answer 11

I just wrote this post on Community Starter Kit personal.mdf, commercedb.mdf and aspnetdb.mdf.

ASP.Net 2.0 Community Starter Kit SQL Express Conversion to SQL 2005

Hope it helps ---

 - Caroline

http://bogartcomputing.com

 

Answer 12

Make user instance as false. It solves the problem...

Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Test\App_Data\test.mdf;Integrated Security=True;User Instance=false

 

Hope it helps,

Thanks,

Manoj.

Answer 13

saggimanoj wrote:

Make user instance as false. It solves the problem... Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Test\App_Data\test.mdf;Integrated Security=True;User Instance=false Hope it helps, Thanks, Manoj.

Doing that caused other problems for me.

Answer 14

 

Hi all,

I'd like to say I was able to reproduce this behavior, but I cannot - at least not 100%... My error(s) seems to alternate between "Failed to generate..." and "Timeout expired. The timeout period elapsed prior to the completion of the operation or the server is not responding."

However, this all seems to happen when another user has or establishes a local session on the computer.  I am running WinXP Pro SP2 w/most recent patches and have Fast User Switching enabled so that my wife and I can still use the machine when the other is not around.

So here's an example.  My wife wakes before me and logs onto the machine and checks the weather.  She leaves, eats breakfast and heads out the door.  20 minutes later, her session is "suspended" and Windows returns to the Welcome Screen.

After dinner, I log onto the machine to do some coding, keeping her session suspended so that she doesn't loose any work.  I fire up VS2k5 and create a new project.  I try and create a new database and, depeneding on the type of project (WinForms or Web Site), I either receive one of the "Timeout" or "Failed to generate..." errors.  I log off out of frustration.

Right before my wife goes to bed, she checks the weather again and then logs off and closes her session.

I return, just before bed, I log in and try once more to create my database...And it works.

I have a handful of error log files if and 2 screen shots if this information would assist anyone.

Thanks,

Answer 15

Exactly the same for me.  Hopefully someone can look into fixing it.

Answer 16

I had posted a fix, my post had an error in it. Please try this now. I'm sorry for any inconvenience my taking the post offline might have caused.

http://asp-net-answers.blogspot.com/2006/04/aspnet-20-community-starter-kit-sql.html

 

Answer 17

I just have to add my voice to this whole thread.  I've been thrashing around with this "user instance error" problem since November when SQL Server Express was first released.  To say that it's driving me up the wall is a vast understatement.

Downloading Service Pack 1 for SQL Server Express last week did not change the situation at all.

None of the solutions/workarounds described here have helped me in the slightest. 

While I might complain about their products from time to time, for the most part I'm a happy Microsoft customer and I consider myself a fan.  I applaud them for releasing a whole suite of products (the Visual Studio Express programs and SQL Server Express) so those of us who are interested from a hobbyist point of view can pursue programming/web design without a major investment.  While I applaud them, I can't help but wonder why they release products when there are errors like this.  Also, why wasn't this particular bug picked up and squashed for the SP1 release?

I guess the only saving grace is that, for whatever reason, SQL Server Express and Visual Web Developer work just fine on my laptop.  I've only experienced this "user instance error" on my desktop machine.  For the life of me I can't figure out what the difference is.  Both machines are running XP Home SP2 with all the latest updates.

I noticed that there appear to be Microsoft staff who frequent this forum.  All I can say is...hear our pain!  That sounds dramatic, but I hope the message I'm trying to convey gets across.

Tomorrow, when I have more time, I'll see about posting the exact error that I'm getting.  I can use SQL Server Management Studio without a problem, but SSEUtil does not work (gives the same user instance error) and as an example, the Personal Web Site Starter Kit also does not work.

One thing before I close...I've noticed that on my laptop, I have an ASPNET account that shows up in the "User Accounts" area in Windows Control Panel.  I don't have that account on my desktop machine.  I've looked around for instructions on how to create this account in Windows XP Home, but have found nothing.  Everything I've seen is geared toward XP Pro.  Could this possibly be the source of the "user instance error"?  Does SQL Server Express (and Visual Web Developer) need to use the ASPNET account behind the scenes?  It's late and perhaps I'm grasping for straws.  However, like I said that's the main difference I've seen between my "working" situation (the laptop) and my "not-working" situation (the desktop).

Thanks for listening.  Sorry for the length of this post.

Cheers,

Jeff Mitchell

Vahid66 wrote:

No wonder Microsoft is giving it away for free. It’s not worth anything. I am very disappointed in this version of SQL server. I decided to start learning VB so I downloaded and installed VB 2005 express edition and SQL 2005 express. I tried to build a personal website using the starter kit. I kept getting the following error message, for which I have seen numerous postings on this forum. “Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.” I went out and bought the Standard version of VS 2005 thinking there may be a feature that the free express version is lacking. I uninstalled the express version, cleaned the registry, deleted all the folders it created and installed the Standard edition. Then I tried to create a personal web using the starter kit and it does the same thing again. I can not create a database file, connect to a database file. I can not create or attach a database on the SQL 2005 express from VS 2005. The SQL server Express seems to be working OK since I can attach to it using Microsoft SQL Server Management Studio Express. There is something about the way VS tries to connect to the database server that is not working. VB does recognize the local computer as a SQL server but can not connect to it. SQL server is set to accept Shared memory, TCP/IP and named pipes for local clients. What is causing this? This is some information on the system I am using: MS XP (SP2) Microsoft Visual Studio 2005 Version 8.0.50727.42 (RTM.050727-4200) Microsoft .NET Framework Version 2.0.50727 Installed Edition: Standard Microsoft SQL Server Management Studio Express 9.00.1399.00 Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158) Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 6.0.2900.2180 Microsoft .NET Framework 2.0.50727.42 Operating System 5.1.2600 I am now to programming but nor computer illiterate. I have been working with MS SQL servers since version 6.5 and have MCSE, Network+, A+ and Security plus certifications. Installing and using VS should not be this problematic. What is wrong with Microsoft?

Answer 18

Jeff - consider a couple of things.

The user instance error implies a Sql Express mdf file. So if you export the whole thing to SQL Server and update the connection string accordingly that problem is solved.

Please see this post:

http://asp-net-answers.blogspot.com/2006/04/aspnet-20-community-starter-kit-sql.html

 

 

Answer 19

Jeff - consider a couple of things.

The user instance error implies a Sql Express mdf file. So if you export the whole thing to SQL Server and update the connection string accordingly that problem is solved.

Please see this post:

http://asp-net-answers.blogspot.com/2006/04/aspnet-20-community-starter-kit-sql.html

 While we're in complain mode: does anyone find the user interface to this forum to be difficult to read and easily fooled into thinking its buttons are clicked upon simple actions like pasting a link?

 

 

Answer 20

The person who noted the discrepancy in multiple users logged in was more accurate then most people here posting useless *** that didnt really work and appeared to only work because of the aforementioned problem and solution.

 

Windows XP Media Center,

Visual Studio 2005 - And Sql Express because Microsoft makes it so difficult t9o get managmenet studio and development sql 2k5

I always run my laptop using remote desktop.  Today after using it earlier today i reopened vs.net and loaded the project up. Got the same error. Searched the forusm, read the ***

Taking the advice on the people who had noticed problems while having multiple users using the system or remote desktiop, I Logged off, actually "logging the user" off. When i reconnected to the laptop it told me i had exceeded my maximum number of terminal service licneses. Bull *** because nobody was even logged on. There were 0 users running. I logged on via the desktop and then logged in through remote desktop. Everything works fine.

Cute little bug, like many other bugs it will probably remain shrouded in mystery, and not actually ever fixed.

 

Answer 21

(My  summary of this error)
From my experience the way to avoid this error is to be the first user to log in.

Surely a fix will be in the next update, this error keeps popping up. I'm just refreshing this solution because it works and I know it does as the inverse generates the error - logging in as another user first.  So restart (not just log off) your pc if you run into this error.

Of course there are a few odd ways to generate the same problem and as mentioned above their fixes have limited success.

Also if any microsoft reps are reading this could you please create and/or provide the link to the offical bug report. It seems relatively easy to identify to me.

Answer 22

Try give everyone access to below directory which belong to whoevery is running it.  Works for me.

 

C:\Documents and Settings\username\My Documents\Visual Studio 2005\WebSites\WebSite1

Answer 23

Hi to all

I solved the problem using the B) solution posted by REEN and also changing to local service on the SQLEXPRESS service.

 

Best regards

 

Abraham

Answer 24

I developed a simple web site that runs locally and used to work just fine. It uses a local database using the following connection string:

<connectionStrings>

<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TestDatabase.mdf;Integrated Security=True;User Instance=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

I recently used SQL Server Management Studio Express to run scripts against that database. I attached it, ran the scripts, and then detached it. Since then, I get the "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." when using my web site as well as when in my Visual Studio 2005 environment.

I have tried the magic solutions proposed here, i.e. deleting the SQLEXPRESS folders for both ASPNET and local users, running the service with different users, etc. Nothing worked.

Any suggestion?

Answer 25

A quick update on that one: I got my web site working again, but only outside the Visual Studio environment, by doing a full uninstall / full install of SQL Server (was not easy as I had to go in the registry to remove instances).

And then I managed to fix the issue with Visual Studio by restarting my computer.

As I suspect this will happen again, I would however still welcome a fix or a better way to get passed that painful hurdle...

Answer 26

I had to do these to get it working:

1. Delete C:\Documents and Settings\User Account\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS
2. Change SQLExpress to run under Local Account
3. In my case SQLServerAgent wasn't running, had to start that.

Thanks for all the useful information in the earlier posts..

Answer 27

If just install the visual studio completely you will recieve this error, To solve this problem u need to install  the microsoft SQL server which is another CD. that is the resean that you cannt access the .mdf file and you will get this error message "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."

Answer 28

Checking the log is the best help in the instance case my problem was that i un-installed sql express and it left teh MSSQL1 folder there with nothing in it. so i re-installed sql server express and copied the files from MSSQL3(my third sql server install) and put them in MSSQL1 folder that solve my problem with adding database in VS. now im going to try and get Sql server developer edition working. i got a solution that i think will work from the sql server forum. re install it over the sql server express instance.

Answer 29

After trying everything under the sun, I followed techietex's steps 1 & 2 and added an additonal step.  To summarize, I exectued the following steps:

 

1. Delete C:\Documents and Settings\User Account\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS
2. Change SQLExpress to run under Local Account
3. Changed the User Instance to false in the connection string (e.g. Data Source=.\SQLEXPRESS;AttachDbFilename="<dbpath\db.mdf";Integrated Security=True;User Instance=False)

HTH

Answer 30

I am creating a GUI Demo (master pages, themes, skins, etc) with only the default.aspx page to use in my main content pane.  The default.aspx page has a button and a textbox.  I am not connecting to a database.  I do not have a database connection in VS.  But I am getting this error message.  Why?

 

Thanks!!

Amy

Answer 31

I would like to start off by stating that most of the time I have problems with a MS product it is because I could not bend myself to the view they have.  That being said I have been pounding my head on this for two weeks.  All of the information I have been reading on this thread is referring to development machines and ironically I have had no problems in this regard.  Now when I upload completely functioning apps to a completely stable server I get this error.  My ASPNET user has dbcreat access and file access to the machine local settings and the local dir that the iis server is pointing to.  I have had some luck creating a user in the db admin tool and getting the database to connect but this does not solve my problem.  I guess my question is:  Is there anything other than rights that may cause this problem in a production environment with sql 2005 standard.

Answer 32

This is another location that holds some temporary files.

 

Make sure to verify the .MDF files are not need/attached to SQL (my disclaimer) before emptying the folder.

C:\Documents and Settings\%username%\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

 

This resolved my error.

Answer 33

I had this problem and I tried Reen's solution b). It worked. Thanks a lot!

Answer 34

 

Have you tried setting the SQLEXPRESS Built-in account to Local System instead of Network Service? I think IIS sees the SQLEXPRESS databases remotely located instead of local.

 

I think Reen's solution B should do the trick.

Answer 35

I am using VS 2005 Pro with SqlExpress installed.  I first installed SQL Server 2005 Developer.  I get this error in my web app because I have both Sql Servers installed.  I have both SQL Servers installed because SQlExpress does not do scripting.

 

I checked the error log and found out my error is cause by SQLExpress (or whatever) looking in the wrong directory.  It wants to find "C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Template Data\mssqlsystemresource.mdf" and its associated log file.  It is looking in "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Template Data.  The MSSQL.1 folder belongs to SQL Server 2005 Developer.

 

If I uninstall both versions and delete all associated folders, clean my registry and then reinstall SQLExpress this error message goes away.  However, I will do that to fix this error.  Is there a way to get SQLExpress to look in the right folder?  Some registry entry or something like that?  I'll do a registry search for the folder names and see if I see anything obvious.

Answer 36

I also experienced this problem.
It occured after i Uninstalled SQL Express since i also had a installation of SQL Server 2005.
I needed a sql express database for testing purposes but after install this error kept apearing.

Now i solved it, Sql server express instance was running as "local system " i also set sql agent to local system, but it did not help.
I deleted the  SQLEXPRESS folder down in c:\documents and setting\users\local.....
That did the trick.
I'm the only one who logs into this computer so thats why i choose this possibility first!

Thanx for all who have contributed to this tread!

Answer 37

The "B" option has been the most  indicated to me.

See commentaries in my space (spanish): http://djjavi707.spaces.live.com/blog/cns!5241B651C27274FD!431.entry

 

Greetings

Answer 38

 

I tried every solution I found when I started getting this error with one of my sites. I renamed the root folder of the website and the error went away. See my post at http://www.jeremywadsworth.com/Default.aspx?blogentryid=64

 

 

Answer 39

It worked for me, tnx a lot

Answer 40

This error occured when i installed sql express 2005 and without restart i ran the application. On rebooting, the issue resolved. Trust this helps.

Answer 41

Thank Manoj,

I was facing this frustrating problem for 2 days and it worked when I changed "User Instance=true" to "User Instance=false".

Thanks again.

Vinod Rowmuria

India

Answer 42

Just thanking you for solving the problem.

Regards,

Natalie

Answer 43

Thanks a lot. I am able to run the SQL Server instance now.

Answer 44

I have the same problem with a little more complexeity.

 

Things are working fine on my local machine. I host my website with my hosting company and when I access my website through http I get this error. Since I dont have any control or access to the server where my website is getting hosted, I cannot try any of the solutions that have been suggested in this thread.

 

However, I did try making User Instance in web.config to false, but it isn't helping. I have raised a ticket with my hosting company

 

Any help in this regard will be highly appreciated

TIA

Pavan

Answer 45

this answer was really helpful to me.because i stucked in this problem since 2 months, after reading this forum i done it,

thanks a lot for that

Answer 46

 

hello everyone.

I've read all your threads but it doesn't seem to solve my problem.I have VS 2005 ,SQL Server 2005 and IIS installed in my remote workstation (webserver) in my company. I'm logging in as NT user and i just simply create a website in VS 2005 and try to create a sql database under app_data folder,sometimes it works and when i log in some other time to continue my work,it doesn;t work anymore and gives me the error:failed to generate a user instance.....even when i create a whole new website,it still gives me the same problem.The error seems to come inconsistantly.My SQL server configuration is logged in as NT authority.Is it because i never set somethings correcly in my SQL configuration or IIS?

Please advice.Thanks.

Answer 47

 

I'm using Windows Server 2003 (64bit) with SQL Express. I've got the same Problem. Setting user instance to false just releases a "Table1 cannot be found"  error.  (It's the table i want to call in my MDF).

 

I am trying to get on it with a ASP.NET site.

http://mrkill90.ath.cx/knight/ 

I am opening the errors for you to see. (Sorry, if they are German, I hope he sends English errors as well, for English users.)

 

Well, the "trick" with the Doc's and Settings / Username... etc doesnt work for me because.. WELL! There is no ASPNET userfolder. Nothing, nada. Anyone know where those temporary files are stored? I don't seem to find them. No ASPNET folder, only my logon admin's folder, Default user, and all users.

 

SQL 2000 doesn't allow connecting with user instances and MDF attaching, right?

Answer 48

lostinholand wrote:

hello everyone. I've read all your threads but it doesn't seem to solve my problem.I have VS 2005 ,SQL Server 2005 and IIS installed in my remote workstation (webserver) in my company. I'm logging in as NT user and i just simply create a website in VS 2005 and try to create a sql database under app_data folder,sometimes it works and when i log in some other time to continue my work,it doesn;t work anymore and gives me the error:failed to generate a user instance.....even when i create a whole new website,it still gives me the same problem.The error seems to come inconsistantly.My SQL server configuration is logged in as NT authority.Is it because i never set somethings correcly in my SQL configuration or IIS? Please advice.Thanks.

 

If you are having trouble with your development machine, do not turn on the IIS flag when creating a website project or adding the a website project from source control.

Answer 49

My instance of SQL Express was working just fine for a local web site I was hosting, but I had to reinstall it and now I'm getting the "Failed to generate a user instance" issue.  After reading through this thread, I still haven't been able to resolve the issue.

I'm running SQL Express on Vista.  I have a database file set up for ASP.Net Authentication.  I can connect to this database through Visual Studio, IIS 7.0 User Administration, and SSEUtil.  However, I cannot connect to the database when accessing it from a Web request.  Upon attempting to access the database, the web site returns the error:

"Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."

My SQLExpress service is running as "Network Service".  I've tried switching it to "Local System", but that didn't help.  Besides, its suppose to run as "Network Service".  All my Application Pools are running as "Network Service".  I also looked at the "Effective Permissions" for user "Network Service" on the "sqlservr.exe" and it says that user has execute permissions.  The Event Viewer error entry just indicates the same error message and indicates the user was "Network Service".

Most of the responses here have discussed this probelm as it relates to a local process like Visual Studio or SSEUtil not being able to create the user instance (delete the user template files, run as "Local System", etc.)  I'm not having this issue.  I'm only having an issue when trying to connect through a web service.

I know there is some magic configuration that makes this work, because however Visual Studio 2005 installed it to begin with seemed to be just fine.  Unfortunately, it appears installing it by myself seems to have missed whatever settings VS2005 gave me.  Does anyone know how to fix this?  Thanks.

Answer 50

Ok, the issue is probably 1 or both of the following.

 

1. Network Service does not have the necessary file permissions on the .mdf file that you are trying to connect to.

2. You are openning the file from within Visual Studio which successfully creates a user instance to the database under your currently logged in user account, and keeps this connection open when you attempt to connect to it from IIS with your ASP.Net web site.

 

If you don't need to use User Instances, I would suggest removing the User Instance=True statement from your connection string, otherwise, ensure that VS is detached from the database and that Network Service has the appropriate file permissions on the .mdf file.

 

Cheers.

 

Answer 51

Scott,

Unfortunately neither of these items holds true.  I have checked the permissions on the App_Data folder, MDF, and LDF files and all have full access for Network Service.  I also do not have any other applications running which might have the database file open.

While I can turn user instances off, I'm really interested in discovering why exactly they aren't working for my Web requests.  I'd rather not go around the problem.

Answer 52

Here is a more concise list of the things I know:

• Running on Vista Ultimate
• Running SQL Server Express 2005
• Connection string: data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true
• Was working before uninstalling and reinstalling SQL Server Express 2005
• SQLExpress service is run as user “Network Service”
• "Network Service" has full permissions on App_Data, MDF, and LDF
• “Network Service” has execute permissions on “sqlservr.exe”.
• All IIS 7.0 Application Pools are running as “Network Service”
• No other processes currently have the files locked
• User instance creation works fine when accessed through IIS 7.0 User administration, SSEUtil, and VS2005
• User instance cannot be created when accessed via Web request
  
• Event Viewer reports: “Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed. [CLIENT: <local machine>]” for user “Network Service”

 

I’d like to solve this problem without workarounds.  That is, I don’t want to solve the problem by not using SQL Server Express or using user instances.  Thanks.

 

Answer 53

Did you try:

1. Rename the folder of the root of your website. So if your website is in a folder named Website1, rename it to Website11.
2. Open in VS or VWD and run. For me, it would run at this point.
3. Then try to rename the website folder back to the original name and run.

Answer 54

Interesting.

 

Firstly, are you using Cassinni (VS Web Development server) to run your website, or are you using IIS7?

 

If you're using cassinni, the user that will be trying to create the user instance will not be Network service, but the user you are currently logged in as.

 

If IIS7, I assume you've set IIS6 compatibility and all the other settings you need to develop ASP.Net applications on vista (see http://sjbdeveloper.blogspot.com/2007/06/developing-on-vista-part-1.html)

 

Also, from SQL Server management studio, try the following

 

SELECT *
FROM sys.dm_os_child_instances

 

This will tell you if the user instance is being claimed by another user.

 

Also, just to be clear on this, whether or not you use User instances has nothing to do with whether or not you use SQL Express. User Instances is simply a feature of SQL Express that allows a least privileged user to host a database instance in it's own user context. SQL Server Express can operate using the traditional methodology as well, although I do understand your desire to get to the bottom of the problem.

 

Answer 55

Jeremy,

I don't see how renaming the folder would have any effect, but just to rule out any magical voodoo solution I gave it a try.  It didn't do anything as I suspected.

The reason I wouldn't have expected this to work is because I believe this is somehow related to security permissions.  Since renaming the folder should have no effect on the permissions of the underlying files, I wouldn't expect this to have suddenly caused it to start working.  If you were able to resolve your problem this way then I would theorize that renaming the folder was only coincidental to something you actually did to solve the problem.  Perhaps you were running into an issue with locked files or something that going through the motions of renaming the folder helped you resolve.

Derek

Answer 56

Scott J Baldwin wrote:

Interesting.   Firstly, are you using Cassinni (VS Web Development server) to run your website, or are you using IIS7?

No, I'm hosting the web site in IIS7.

Scott J Baldwin wrote:

If IIS7, I assume you've set IIS6 compatibility and all the other settings you need to develop ASP.Net applications on vista (see http://sjbdeveloper.blogspot.com/2007/06/developing-on-vista-part-1.html)

I do have IIS 6 Management Compatability installed, but I doubt not having this has anything to do with using SQL Server Express with user instances successfully.  Either way, I would rule this out as a factor given this all worked for me before uninstalling and reinstalling SQL Server Express just a few days ago during which time no IIS components have been added or removed to my machine.

Scott J Baldwin wrote:

Also, from SQL Server management studio, try the following   SELECT * FROM sys.dm_os_child_instances   This will tell you if the user instance is being claimed by another user.

I've only got one entry showing that I've created a user instance in the past with my account, but the heartbeat is currently dead so it isn't currently being used.

Scott J Baldwin wrote:

Also, just to be clear on this, whether or not you use User instances has nothing to do with whether or not you use SQL Express. User Instances is simply a feature of SQL Express that allows a least privileged user to host a database instance in it's own user context. SQL Server Express can operate using the traditional methodology as well, although I do understand your desire to get to the bottom of the problem.

Understood.  I just wanted to state this explicitly to avoid getting any responses like "Just set User Instance=false"

Derek

Answer 57

derekgreer wrote:

Jeremy, I don't see how renaming the folder would have any effect, but just to rule out any magical voodoo solution I gave it a try.  It didn't do anything as I suspected. The reason I wouldn't have expected this to work is because I believe this is somehow related to security permissions.  Since renaming the folder should have no effect on the permissions of the underlying files, I wouldn't expect this to have suddenly caused it to start working.  If you were able to resolve your problem this way then I would theorize that renaming the folder was only coincidental to something you actually did to solve the problem.  Perhaps you were running into an issue with locked files or something that going through the motions of renaming the folder helped you resolve. Derek

 

Derek,

 

I just thought it would be good for you to try. I have run into this twice and both times it occurred after changing or reinstalling SQL Server. The first time I finally resolved it by copying the website to a new folder named differently. I wrote this down in case it ever occurred again because it was such a painful mess. The next time it happened, the first thing I did was just rename the folder and that fixed the issue. I see now that my scenario was different from yours. In both cases, I was using Visual Web Developer 2005 running Cassini. Good luck to you.

 

Jeremy

Answer 58

Jeremy,

Thanks for the suggestion anyway.  If you were using Cassini then you would have been experiencing an issue related to your own login.  I'm not sure how entries pertaining to individual database files get cleaned out of the actual database instance, but by renaming the folder you may have triggered this.  Renaming the folder may also have had some affect on the files in "C:\Users\[userid]\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS"  which could have resolved a permission issue you were having.

My particular issue has something to do with  the "Network Service" account not being able to successfully create a user instance process.  In theory, this should be the result of the account not having the proper permissions on  the actual database files, the process which spawns the new user instance (i.e. “sqlservr.exe”), or some other  as yet unknown process or file.

Thanks,
Derek

Answer 59

Here are my latest findings:

According to Mike Wachal at Microsoft, user instances can only be created by an interactive account because system files have to be copied into the user's profile folder .  Because the "Network Service" account isn't an interactive account, there is no place to copy these files.  Additionally, the default application pool is set up to run as "Network Service" which is the account the user instance will be attempted for.  I'm not sure if there even is a way to allow "Network Service" to be an interactive account, but given my Vista machine has all the default configurations of "Network Service" as a non-interactive account, default application pool run as "Network Service", and my web site assigned to the default application pool, this in theory is why I am receiving this error.

That answer would be satisfying except for one thing:  I haven't modified any of these conditions and my Web site was working WITH user instances just a few days ago ... just before I reinstalled SQL Server Express 2005.  Obviously, one of these assertions must not be true.  Either I wasn't actually using user instances, the application pool associated with my site was running as an interactive user, or it isn't true that user instances can only be used with interactive accounts.

So, I'm left with questioning my sanity or questioning the knowledge of the Program Manager of SQL Server Express.

Is there anyone out there who is successfully using SQL Express with user instances with a Website?  If so, can you please see what account your Website's application pool is running under?


Derek

Answer 60

I have had it working many times before, however I have never used the Network Service as the user for my Application Pool. I would suggest either using the ASPNET account, or creating a specific account (interactive obviously) that you can then use for your Application Pool.

 

Cheers.

Answer 61

Since I'm on Vista, there isn't an ASPNET account.  Nevertheless, I don't see how this account would function any differently on XP as I don't think ASPNET is an interactive account. As far as actually creating an interactive account to run my applicaton pool as, this seems like an odd thing to do and doesn't really serve my purpose of figuring out what changed to cause this to stop working.

Derek

Answer 62

Okay, I am running on Win XP SP2, Visual Studio 2005 and SQL Server Express 2005...

 

This started occuring to me suddenly, ofcourse when I was going to give a demo of a PoC that I built.   YIKES...  Could not run the demo...  After reading the post, I went to services.msc and changed the SQL Server (SQLEXPRESS) to run under my account (LOGON AS property to myUSERNAME).  My username is also local admin to the box. 

 

This worked...  for me...

Answer 63

 

Many thanks. This fixed 1/2 the problem. I now have my Visual Studio working with my asp.net project. But my two IIS websites still do not work.

I have SQL2005 full and SQL 2005 express on the same computer. Is this OK. I need the full to learn the next tools but yet run my two websites.

I have installed sp2. This will not apply to the express version.

I have two instants. MSSQLSERVER and SQLEXPRESS. They look to run in the app.

But will it work. No.

I even un-installed and re-installed the VB project as a deploy. No joy.

Lacking in SQL Admin skills here. But I am sure it is a easy fix.

Answer 64

 

After I checked every user permission from data and binary folders to database I tried to play with protocols. I used sseutil.exe as test tool and from Management Studio I was connecting to user instance name for example :“\\.\pipe\78868914-5C59-4A\tsql\query” to make sure that everything is properly created.

 

1)      “sseutil –c” –> FAIL

2)      Removed all protocols for SQLEXPRESS except Pipes.

3)      Restarted SQL Server

4)      “sseutil –c” – WORK

5)      Returned back TCP/IP and Shared Memory

6)      Restarted SQL Server

7)      “sseutil –c” – FAIL

8)      Repeated first 7 steps couple time to make sure that result is consistent.

9)      Made user instance working again.

10)  Rebooted machine.

11)  Everything works fine with enabled TCP/IP and Shared Memory.

 

If I want to have same error again I just disable pipes but this time it works fine after I return them to enable.

 

Did some caching made initial problem ?

 

Answer 65

This thread is refusing to let me unsubscribe. I get alerts every time it's updated. I cannot unsubscribe because it doesn't show up in my threads or subscriptions.

THat has to be a can spam act violation. It is definitely annoying.

Answer 66

Dear Scott,

 

Thanks a lot. You really helped me. I spent several days to find out a solution until I met your message.

 

Your Valeri

Answer 67

 

I am dealing with the same problem but I believe I migh have found a resolution.

 

Rather then take all of the recommended steps mentioned previously, I am going to simplify.

 

I am making sure that I have all relevant .mdf files and relevant database app data stored on a seperate hard drive, or a specific location on the C: drive.

 

Then I am wiping clean all instances of Server 2005. By wiping clean, I mean uninstalling.

 

After this I will reinstall only the instances of Server 2005 that are needed for my implementation.

 

I am utilizing Visual Web Developer Express Edition, which needs SQL 2005 Express. I will start with this install and work my way back.

 

 

I will reply with results of completely uninstalling SQL and reinstalling shortly.

Answer 68

I had this error on SQl Server 2008 Express and C# Express and used this information to resolve my issues quickly

Answer 69

Eventually it was that simple for me too - in my case using Visual Basic Express developing a winforms application. I've been a bit frantic about solving this issue, and thought I had already tried this a few days ago, but I'm a bit of a newbie with visual basic. Make sure User Instance is False in both My Project: Settings and in app.config (although that is probably the same thing).

Answer 70

Hi,

 

Had the same problem.

My Fix:

 

turn this from :

User Instance=true

to:

User Instance=false

 

On the conection string.

 

Worked for me.

 

hope it will help.

 

Regards ,

 

André

Answer 71

Thanks for help. This error occurred for me when I wanted to reload application generated by VS 2005 and SQL Express 2005 , Windows XP x32 to VS 2008 and SQL Server 2008 Windows vista business x64. I solved it by using hint from this forum:

The error came up on ASP.net Web Site Administration Tool on Security tab.

1. In Web.config I changed AspNetSqlProvider's connectionString from Data Source=.\SQLEXPRESS to MyServer\SQLEXPRESS
2. I copied all .mdf and .ldf files from C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Template Data to C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\Template Data

That was the trick. Everything started working again.

Answer 72

This worked for me. The only difference was that I browsed out to the directory listed above, and I didn't have any MDF/LDF files in my user instance (MSSQL.1\MSSQL\Template Data). I copied the files into that directory and viola!! Thanks.

Answer 73

Post Monday, April 17, 2006 5:03:22 AM by kevin067 solved my problem. After working through the list of other options.

Thanks!

---

Beginner DBA

Answer 74

Thank you,

Deleting SqlExpress folder worked for me.

Answer 75

It makes no sense to me but all I needed to
do to fix was to delete the files within the
SQLEXPRESS FOLDER AS WELL...

Wednesday, February 25, 2009
Failed to generate a user instance of SQL Server due to a

failure in starting the process for the user instanc
The error message I had was "Failed to generate a user

instance of SQL Server due to a failure in starting the

process for the user instance."

The fix was this entry from Craddock on a Microsoft forum:

"If you don't want to follow the link above and read the

details, the short answer is that SQLExpress creates a

directory per user in "c:\Documents and

Settings\[user]\Local Settings\Application

Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS"

that it uses to store information. I deleted the files from that

directory and was able to connect succesfully using User

Instances on the next try."


ps... DONT FORGET [USER] IS your logon name...good luck

Answer 76

I am replying to myself so as to be more clear as to what the
above fix did for me...

I have winxp pro... run visual web developer with SQLEXPRESS
and got the advanced download of that... while the manager studio
allowied me to create a dbase, when i was in Visual Developer and
tried to create a DBASE in apps folder... I got the infamous but prolific
error discussed in this forum/thread....  I am not running remote under
IIS at this time so if your prob is that, the above is not meant to address
it but if I were you... I would certainly try...  I couldnt believe it worked..

Answer 77

Just another note to the above... don't worry... the file will repopulate...
somehow... it seems like the initial entries upon download prevent the
establishment of user instance and by removing the initial files... the
proper instance can occur with the right info per your circumstance...
I will continue to test this and wonder whether in the future if I get the
error again that I will just have to delete those files once more... but in
the mean time its working and after a week of pulling out my hair on
one of the toughest downloads ever... i am sure smiling now...
and believe me... I AM A MICROSOFT FAN!!!  GO MICROSOFT....GO SQL!

Answer 78

Oh Well...

Still pulling out my hair... while the above allowed management studio to  create and connect to a dbase... I can't for the life of me connect to the dbase using IIS...
I want to tell Microsoft something... this is not working and it needs to be fixed... I would rather have potential system insecurities and be able to develop than to be
going thru this... let the ISP's have a damn docturate in how to configure the connection... I want to develop and I am wasting so so so so so so so much time trying that I am about to look for the next alternative... How is it that the lesss robust dbase... ACCESS works just fine?...AND I AM A MICROSOFT FAN... Here's the scoop...
give me a setup option that says "You'll be able to connect thru IIS with no problem but your database will not be secure and you'll connect the very first time .... its guaranteed by Microsoft..."  I WILL SELECT THAT OPTION!

Answer 79

May the saga continue...

I figured out that in sql management studio that I need to grant ASPNET login and permission to a particular sqldbase file... so I did... and what this means if you are experiencing the same or similar is that you will grant login and permission to specifically YOURCOMPUTERNAME\ASPNET and as such that is what I did and loe and behold iis was able to connect and there was the database files!=)... BUT WAIT... NOW AS I PULL MY HAIR OUT MORE... THE INSERT, DELETE, AND UPDATE COMMANDS GENERATED BY VISUAL WEB DEVELOPER THAT ARE VERIFIABLY THERE (MANY TIMES VERIFIED NO LESS) DO NOT WORK, DO NOT WORK, DO NOT WORK, DO NOT WORK... AND IF MY REPEATING IS IRRITATING, ... JUST IMAGINE MY REPEATING TEN BILLION THINGS (EXAGERATION) JUST TO SEE THE SQL DATABASE AT ALL... MAYBE ....ADMITTEDLY... MY PERMISSION TO ASPNET WAS A 'SHOULD HAVE KNOWN'... BUT I AM CERTAIN, CERTAIN, CERTAIN... THAT THE FACT THAT INSERT, DELETE, AND UPDATE DO NOT WORK WITH ANY VIEW AT ALL IS A PROGRAMMATIC MESS UP....(I AM BEING VERY KIND HERE)... AM i MISSING SOMETHING?  PS...WINXP, SERV PACK2, VIS WEB DEV, SQL 2008... PLEASE HELP...CONSTRUCTIVELY...DAN.

Answer 80

The sun will come out tommorrow!!!

...and it did for me yesterday as I sit here with a complete smile... ITS ALL WORKING ;=)

And its working great!  So don't give in and give up... how else would Bill have gotten where
he is today.  If I can be of help... here it is.

1. download and install visual web developer 2008 and SQLExpress 2008 advanced pack.

2. imediately delete the contents of the sqlexpress file with path described above ...
"c:\Documents and Settings\[user]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS"

3. give yourself login and permissions in SQL Studio

4. give ASPNET login and permissions in SQL Studio

5. give ASPNET and Yourself login and permissions in the complete file directory... house this in your virtual web directory under IIS

6. if you attatch a dbase in sql studio... and want to access it in visual web... then detatch it in sql studio and then connect to dbase thru visual web

7. Note: not every connection string auto generated in visual web seems to work... keep trying...

here's an example from default.aspx that works for me:  ConnectionString="<%$ ConnectionStrings:C:\INETPUB\WWWROOT\ZSQL\APP_DATA\DATABASE.MDFConnectionString2 %>"

and here is the web.configue file reference

<add name="C:\INETPUB\WWWROOT\ZSQL\APP_DATA\DATABASE.MDFConnectionString2"
            connectionString="Data Source=YOUR-4DACD0EA75\SQLEXPRESS;Initial Catalog=C:\INETPUB\WWWROOT\ZSQL\APP_DATA\DATABASE.MDF;Integrated Security=True"
            providerName="System.Data.SqlClient" />

I am not using user instance and if on some tests where i set to false... this works   and
it also works to take out (maybe not all the time) authentication="windows"

anyway... as a courtesy to Microsoft and SQLEXPRESS Team... just wanted to say Go Microsoft... Go SQL... cause everything is working just great.

dan

Answer 81

Option (b) worked for me thanks :-)

Answer 82

I just wanted to add what worked for me.

I have developed an application, not a web-based app - a "normal" application that gets installed, etc.  The Option B did not work in this case.

To have my application communicate with the SQLEXPRESS server, I had Windows Authentication turned on and had to set UserInstance=False in my connection string.

That corrected the issue for my application.

Peace, Love, and Light,

/s/ Jon C. Munson II

Answer 83

My experience about SQL server 2005:
I have installed SQL server 2005 with VB before and it worked OK.
Then I uninstalled it and installed entire Visual Studio 2008 and tried to install SQL Server 2005 again. There was a problem SQL instalation pack found old instance os SQL server 2005, so I couldn' t install it again and VB 2008 could not establish connection with .mdf base. I tried everything: run control panel>add/remove programs  - there we MS SQL server Native client, ... Setup support, ...  VSS Writer but Microsoft SQL server 2005 was not there. I uninstalled all MS SQL programs and it didn't work. I used Windows install Clean up program and it didn't help also. I deleted folder C:\Program Files\Microsoft SQL Server and it didn't help. Installer also recogniced old version od SQL server 2005. Then i deleted all registry entries contained "SQLServer", restarted computer and then I could install MS SQL Server 2005. But problem was not yet solved. Bfore instalation of SQL server there was an error when I performed operation in VB 2008:"menu>data>Add new data source>Database>Next>New Connection>choose Microsoft SQL Server Database File>Northwind.mdb>test Connection" Test was not succesfull.(I can' remember exactly what was the message, but I would not try again :). )
After succesfull instalation of new instance SQL server 2005 when I performed same action:" menu>data>Add new data source>Database>Next>New Connection", nothing happened - button didn't open anything.
I tried to compile old VB 2008 project and error "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed" occured.
Then I deleted folder "C:\Documents and Settings\Admin\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS". Then there was an error "The Event Log File is Full". As it was described in page http://www.eggheadcafe.com/conversation.aspx?messageid=31943555&threadid=31694680 I started program eventvwr in "start>run" then i went to Event Viewer > choose "Application" then Action>Preferences and
and choose to "Overwrite events as needed".
Now SQL server 2005 works correctly.
With regards.

Answer 84

This error is often caused because the parent instance (for whatever reason) can't copy the system databases to the users local isolated storage folders. Sometimes it is because of a previous install of SQL Express has left files in that directory which is why Reen's suggestion sometimes works

Delete C:\Documents and Settings\USERNAME\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS.

the only trick is knowing the USERNAME, if your writing a web app, the user is usually ASPNET which is usually found in

C:\Documents and Settings\COMPUTERNAME\USERNAME\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

Other times it is a permissions issue which can be solved by giving the SQL service account the necessary permissions.

One thing to remember is that although user instances is the default when working with Visual Studio, it is not mandatory to use them, it is quite possible to attach to databases in the traditional way from SQL Express, to do this you just need to edit the connection string and delete the "user Instance=true". User Instances just make it easier (when it does work) to work with databases as though they were files.

 

Many tanks, deleting that directory worked for me with Sqlexpress 2008 in Windows Server 2008.

To solve the 'Access is denied' issue to reach the folder I used this path:

C:\Users\USERNAME\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

Answer 85

I had the same problem after uninstalling SQL Server 2005 and installing SQL Server 2008. And guess what! Reen's B solution worked for me!

Answer 86

Have just read thru all the above having installed MS C# and SQL server, and I'm extremely disheartened by all of this. Completely new to all this server setup stuff, but it seems to me that Microsoft have released something which is pretty user-unfriendly and non-intuitive. Add to this the apparent lack of documentation/info in this area and it strikes me as pretty shoddy really. Personally, I've wasted 2 days just getting this far, and as a newby it's enough to put me off completely! This just re-affirms my belief that MS are losing it. Vista is non-intuitive as is IE7 (which is also buggy and crashes continually, despite updates) and now this. Good ideas poorly handled. As someone said, surely this cr*p should be sorted before stuff is released?

Guess I'll get some flak from MS fans here, but this forum thread seems to be a prime example of my recent experiences with MS and their software.

Answer 87

Just make sure that User Instance=false under Source category.
Also make sure that the database you are attaching is not on
your SQL Server.The default server within VS is SQL Express.
Every time you add database to you project ,VS will attach it
to SQL Express unless you specify the connection string.


Talley
Blog:myblog

Answer 88

Thanks Manoj

Your solution is working good for me....

Answer 89

Hi all,
REEN's solution worked perfectly for me,i combined both

-Modifying SQL Express to use Local System Account
And
-Deleting the following folder C:\Documents and Settings\USERNAME\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS.

Dont' forget to restart ur SQL EXPRESS Service after doing the steps above from Administrative tools--->Services

THX REEN.!!

Answer 90

I am new in SQL Server Express 2008 Edition. Dear How did you Modify the SQL Server (SQLExpress) service to use your local or system account and restart the serviceb. Please can you help me I am facing the same probs.

Regards

Asif

Answer 91

Hi Asif,

To modify SQLExpress Account go to Start Manu---> SQL Configuration Tools----->Right Click on SQL Server(SQLExpress)---->in [Log on] tab---->Built-in Account combo box--->choose Local System.

To restart ur SQLExpress Service go to Control Panel--->Administrative Tools---->Services---->find SQLExpress in the list of sevices in front of u---->right click--->restart.

That's all...hope it works

Answer 92

What does this mean?  It was in the SQLsetup log on my PC.

 Does it have anything to do with the Instance error?  I'm fighting that battle too.


Complete: OpenPipeAction at: 2009/9/21 19:10:28, returned false
Error: Action "OpenPipeAction" failed during execution.

Error: Action "CreatePipeAction" failed during execution.
Action "RunRemoteSetupAction" will be skipped due to the following restrictions:
Condition "Action: CreatePipeAction has finished and passed." returned false.

Answer 93

http://crystalrepterrinfo.blogspot.com/2009/12/you-should-provide-default-values-for.html

Answer 94

Other times it is a permissions issue which can be solved by giving the SQL service account the necessary permissions.

I'm a newbie to SQL Server administration. How do you "give the SQL service account the necessary permissions"?

Answer 95

Thanks, this worked for me!

Answer 96

I had the same problem and was directed to this site:

http://www.aspdotnetfaq.com/Faq/fix-error-Failed-to-generate-a-user-instance-of-SQL-Server-due-to-a-failure-in-starting-the-process-for-the-user-instance.aspx

It worked perfectly!!

Answer 97

I had the same problem and was directed to this site:

http://www.aspdotnetfaq.com/Faq/fix-error-Failed-to-generate-a-user-instance-of-SQL-Server-due-to-a-failure-in-starting-the-process-for-the-user-instance.aspx

It worked perfectly!!

 

It doesn't work for me always, I usually get the "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."  error when I start my PC and start IIS and browse the Web page, not doing anything else. I have an APPPOOL created for my application named Test and to get past this error I stop the Web server for my application go into Task Manager and kill the sqlservr.exe process under Test Username. I then restart the page and it works, ALWAYS!

Answer 98

If you are using the Network Service user:

 1. Delete C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

 2. Reboot

Answer 99

I had the same issue after installing SQL 2008 Express.   I have VS 2008 and VS 2010 installed.  I originally had SQL Express 2005 installed, but needed to be able to grade my student's work who use 2008 (standard with VS 20100).  I tried the upgrade from 2005 to 2008 and it did not take.  I uninstalled SQL Express 2005 and then installed SQL Express 2008 and got this message.  I had to both, rename (did not want to delete) SQLEXPRESS in C:\Users\Kent\AppData\Local\Microsoft\Microsoft SQL Server Data AS well as running the accountss under Local System.  Just changing it to Local System did not work.

Thank you for your post as it saved me lots of headaches.

Answer 100

After some time I got the following error: "Failed to generate a user instance of SQL Server due to a failure in reading registry keys. The connection will be closed."

No matter what I did, nothing seemed to work, except one from which I got the action - immediate response behavior: go in the directory where you have the Web project, go in App_Data and in the properties window for the database file go in the security tab. Here click Edit, then Add, then enter IIS APPPOOL\Test - this is the name for the application pool for my Website, then click check names and it should leave only Test . Then click Ok and you will go back to the security tab only, here give full control to Test then click Ok, then Apply.

So I got rid of the error, but still can't open the database from VS and also browse the Website using IIS, I have to close manually the Server Explorer window in VS(closing the connection to the database before) and kill the process sqlservr.exe from Task Manager under my username(Not Test).

Hope this helps.

Answer 101

http://amastaneh.blogspot.com/2010/05/sql-express-windows-7.html

Answer 102

Hi everyone

I was having the same problem "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed"  using the following configuration:

- Visual Web Developper 2010

- SQL Server Express 2008 R2

- Windows 7

The link that solved me the problem was:

 http://www.aspdotnetfaq.com/Faq/fix-error-Failed-to-generate-a-user-instance-of-SQL-Server-due-to-a-failure-in-starting-the-process-for-the-user-instance.aspx

Now I can add .mdf databases throught the Visual Web Developper IDE

Thanks for all!

Answer 103

Thank Reen ,  you saved hours ...  you solution B worked for me .....

 

Regards 

Gourav 

 

Answer 104

Yes I am also thankful to Reen  - deleting the sqlexpress directory was the fix - worked for me too

Thanks

Dom

Answer 105

Gracias Manoj,

he resuelto también este problema con esta respuesta que has propuesto, no tengo otra cosa que decir que gracias a ambos.

desoqu.

Answer 106

Hi,

This dmacaya's solution worked also for me when I got the same error. I have similar a setting:

- Visual Web Developer 2010 Express

- SQL Server Express 2008 R2

- Windows 7

Couple of points to notice in the blog post mentioned:

1. User instances seem to be enabled by default in SQL Server Express 2008 R2.
2. Of course You have to notice that in W7 the path is C:\Users\USERNAME\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS.

So this is actually Reen's solution. Works for me.

Thanks for the the discussion!

 

Answer 107

Would anyone care to make a guess at the number of productive hours that have been wasted on this problem. At least over 100,000!

Thanks to Reen for the solution I needed.

Answer 108

Is not necessary change the user login in SQL Server to Local.

You can use the network service as user login, but the directory to remove in Win 7 is positioned in:

 

- C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

 

Only delete this directory and works all.

 

P.S. Sorry for my english, but I'm italian

Answer 109

Thanks Reen

 

Been scatching my head over this for a while.  Solution B may not have worked for you, but it worked for me

Answer 110

Hey everyone,

Not sure what solutions fixed everyone's problems, but my problem was because I was using a SQL Express instance that I named "SQLEXPRESS2K8R2" instead of default "SQLEXPRESS". I first tried configuring it under the Studio options for Database instance name, but that did not help. What finally fixed it for me was this:

1) Go to the "C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config" folder and open the Machine.config in notepad.

2) Locate or do a Find for the line that contains "LocalSqlServer" under the <connectionStrings> section.

3) Change the part of the line connectionString="data source=.\SQLEXPRESS" to your instance name; in my case SQLEXPRESS2K8R2

4) Save the changes. Note that on Server 2008 R2/Windows 7 notepad does not auto-elevate rights, so you may need to save to an alternate location and then copy over the existing file. You could also launch notepad with admin rights and then open this file to avoid the copy over step.

NOTE: I also did these steps under the Framework64\Config location as well just in case.

I hope everyone worked out what ever problems they had. ASP.NET 4.0 really rocks. :)

Answer 111

Here's another possible solution to the problem which I found after following Mike's tip to read the error log:

The FILESTREAM feature is not supported when using User Instances.

---

Vote here for a new Microsoft Connect feedback channel on Windows!

---

Answer 112

The following link also worked for me. Thanks!

 

http://www.aspdotnetfaq.com/Faq/fix-error-Failed-to-generate-a-user-instance-of-SQL-Server-due-to-a-failure-in-starting-the-process-for-the-user-instance.aspx

Answer 113

This error is often caused because the parent instance (for whatever reason) can't copy the system databases to the users local isolated storage folders. Sometimes it is because of a previous install of SQL Express has left files in that directory which is why Reen's suggestion sometimes works

Delete C:\Documents and Settings\USERNAME\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS.

This worked for me on 2011-01-28. Deleted the directory, and the error disappeared.

I'm using Visual Studio 2010 + SQL Express 2005 SP3.

Answer 114

hi , I'm using windows 7 as development platform , 

my map locations was different  C:\Users\USERNAME\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

 deleting the contents worked for me too

Answer 115

this worked for me:

http://dthatcher.blogspot.com/2011/01/sql-server-2005-installation-error.html

Answer 116

Thankyou so much Gentle men. I was unable to debug this error. Deleting the content worked for me too.

Answer 117

Finally I could solve this! After several hours and lots of forum topics...

I'm Using Windows 7 + Microsoft Visual C# 2008 Express + SQL Server 2008 R2 Express.

I was getting the error : "failed to generate a user instance of sql server due to a failure in starting the process for the user instance", when trying to Connect (or add a connection) to a Data Base.

I did what other guys did: I deleted the following directory

C:\Users\<user>\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

Then, i restarted VS, tried again, and it worked fine.

Thanks a lot.

Answer 118

I just had this issue with Windows 7 installing vb express 2010 and sql express r2

as mentioned by Reen:

Delete C:\Documents and Settings\USERNAME\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

instead delete

C:\Users\%USERNAME%\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

 

WHOOT !

Answer 119

tx.  Option B did the trick for me (I restored data from a crashed PC but renamed the machine)

---

Van den Driessche Willy