none
Stuck between "Failed to generate user instance" and "An attempt to attach...failed"

    Question

  • I wrote an application using Visual Studio 2005 beta 2 which uses a SQL Express .mdf file, included in the project, for the database.  After installing Visual Studio 2005 RC and the SQL Express that comes with it (I followed all of the uninstall instructions first) I can no longer add a SQL Express database to any of my projects, nor can I open the SQL Express database in my original project.  When I try either of these tasks, I get the "Failed to generate user instance of SQL Server due to a failure in starting the process for the user instance.  The connection will be closed."  I read the thread which dealt with this error, and changed the connection string for the database file that had worked before to User Instance = false.  The moment I click Test Connection or OK, I get the error: "An attempt to attach an auto-named database for file C:\<path to project>EngSQL.mdf failed.  A database with the same name exists, or specified file cannot be opened, or it is located on UNC share." (<path to project> is actually a full path, I didn't want to type the whole thing out. :) ).  A search for this error turns up a solution of setting User Instance = false, which puts me in a catch 22 position.

    Any suggestions?

    -- Chris
    Thursday, September 29, 2005 12:54 AM

Answers

  • OK.  First know that the error message you are getting is very often caused (if not by the issues described by my original post) by using Terminal Server to get into the box and then using SSE.  There is a fix for the TS bug posted by Windows. 

    But, the error message is fairly general; it catches a number of different issues. Your original bug description didn't mention TS and you are *currently* able to get into SSE under a different profile. 

    So, here's some additional info about how SSE runs under this mode.  When you first run SSE this way, it makes a local copy of master etc. in C:\Documents and Settings\<your user account name>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS.  That's why it's so slow the very first time it loads under a new user profile.  It may even timeout the first time you ever use it under a given profile.

    Based on your description, it's very possible something went bad in those files for some reason.  So, I would first make a backup and then delete the SQLExpress directory under C:\Documents and Settings\<your user account name>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data.  That will get rid of the local copy of master, etc.  Then, reboot. 

    When you run under your user account again and try to connect to SSE, it will recopy all of the appropriate files (master, etc.) to your local settings.  It should work after that.  You shouldn't worry about the recopy of master etc.  Generally, unless you are adding logins, etc, (which you don't need to if you are working with UserInstance=true) the recopy of master should be fine.

    Monday, October 24, 2005 7:06 PM
  • Unfortunately, your guess is incorrect. It is perfectly normal for sys.dm_os_child_instances to report a User Instance as Dead, that just means that the User Instance is not currently running. When a User Instances is running, it is reported as Alive. This has nothing to do with the ability to start a User Instance. It is the designed behavior of User Instances that they are automatically shut down if no connections are made to the User Instance within a specified timeout. The default time out is 60 minutes and can be configured via sp_configure and the 'user instance timeout' setting. You can find a broad overview of User Instances here.

    It is also incorrect that sys.dm_os_child_instances is a system table, it is not. This is a Dynamic Management View (DMV) and in the case of User Instances, the list is built dynamically out of memory when you make the call to the DMV. Information about User Instances is not stored in a system table and you do not need any special permissions, beyond file permissions, to delete the special directories related to a specific User Instance. Once warning I'll offer is that when you delete that directory, you are also deleting the User Instances specific system databases (master et. Al) and any specific configurations you have made to that User Instance. If you've put any user databases in that directory, you would be deleting those as well. I have here the "nuke it and start over" approach works in some cases, but not in all, so I generally don't recommend this as a troubleshooting step in solving User Instances problems, certainly not as the first troubleshooting step.

    There are a number of different causes of the error that titles this thread, I count 13 different error strings. Unfortunately, the sentance that describes the specific problem comes after the part that reads "Faild to generate a User Instance of SQL Server..." so from the title of this thread I can't tell you what the real problem is. The best advice I can give you is as follows:

    1. Read the whole error message as there are multiple error messages that look the same if you only read the first seven words. The second sentance often gives you more information.
    2. Check the error log for the main instances of SQL Express to check with issues that happen before the User Instance is even started.
    3. Check the error log for the specific User Instance for issues that cause a failure within the User Instance itself. You can find this log in the User Instance directory.

    With this information in had, you will have a better idea where the real problem lies. The most common "Failed to generate..." error comes when you move your application to production and you are now running your application under a different user who does not have an interactive login profile. This is common for web applications which will run in an Application Pool using either ASPNET or Network Service as the user (depending on which version of Windows and IIS you are using). Since these users typically have not logged in interactively, there is no User Profile, and thus no directoy structure for the User Instance directory to be placed in.

    I do not recomend User Instances for use with web applications, when you move a web application to production, you should really attach your database directly to the production server and change your connection string to remove the AttachDbfilename and User Instance keywords. User Instances are designed primarily for storing local data in single user, WinForm applications. User Instances provide some ease of use when you are developing web applications because they allow you to keep your database with your project and VWD has done the work to make User Instances work well with the builtin Casini web service, but you will have problems with them when you move to IIS. (As a side note: One of the best discussions on SQL connections and ASP.NET as they apply to using SQL Express is in chapter 11 of a book titles Professional ASP.NET 2.0: Security, Membership, and Role Management from Wrox. The discussion is centered around the aspnetbd database and membership, but there is plenty of information that can be more broadly applied.)

    The error mentioned about "An attempt to attach..." is related to the connection between the database name and the location of the database file. Again, there has been too much divergence of topic in this thread for me to give any specific analysis, and there are a couple different causes of this problem as well. In general I can tell you that User Instances provide functionality to automatically name a database when the database is attached. The name of the database is typically based on the location of the file. A couple reasons you get this error:

    • There the autogenerated name based on the file path is already listed in the User Instances' master database but is associated with a different file.
    • You have specified a database name in your connection string (i.e. Initial Catalog=foo) and that name is already associated with a different file.
    • Ocationally this can be cause when you try to switch a file back an forth between being used by the User Instance and attaching it to the parent instance to work with in using SSMS.

    The best way to resolve this issue is to use SSEUtil to detach the named database that is conflicting which should then allow your application to run and attach the database.

    I'm marking this thread as answered with this general guide. If these don't resolve the problems you're having, I recomend you submit you question as a new post on the SQL Express forum with specific information about you're failure including log findings and exact connection strings.

    Regards,

    Mike Wachal
    SQL Express

    Monday, November 20, 2006 10:01 PM
    Moderator

All replies

  • +1

    Chris, I don't have anything to add to your post except for the fact that I'm experience exactly the same symptoms you are. Did the uninstall/install steps for the RC from the PDC. What used to work (drag/drop MDF file into project) doesn't. And I tried the same thing you did (user instance=false).

     

     

    Tuesday, October 04, 2005 2:59 AM
  • OK, here's the deal.  There are actually two key things associated with the ability to use an MDF locally.  The first is the *logical* database name.  If you look closely at a connection string that uses MDFs locally, you will see that it is missing.  The missing logical database name is a signal to SQL Server to create one for you automatically. 

    The second thing is the User Instance = True.  This simply says, "let me use an MDF even though I"m not logged in as an admin".  Thus, it will let you work with an MDF as a "normal user".  

    The other thing to keep in mind is that once you attach an MDF, SQL Server keeps track of it until you detach it.   So, here is the remedy.

    1.)  Download SSEUtil from the web.  http://www.microsoft.com/downloads/details.aspx?FamilyID=FA87E828-173F-472E-A85C-27ED01CF6B02&displaylang=en and use it to detach your database from the running instance of SSE.  It is very much like SQLCmd, but much friendlier.

    2.)  You should be OK to create/connect the way you have in the past.  However, if you want to continue down the path you were on, you can add a logical database name to your connection and be off. 
    Monday, October 10, 2005 10:13 PM
  • Thank you for your post, but in regards to it being marked in green as the "Answer" I must respectfully disagree and as such have unmarked it.

    My problem lies deeper than your suggested solution. 

    For me, running sseutil -l just to get a list of attached databases gives:

    C:\sseutil>sseutil -l
    Failed to generate a user instance of SQL Server due to a failure in starting th
    e process for the user instance. The connection will be closed.

    I used some ideas presented in the bug tracking forum for VS .NET 2005 and the following is what I posted there:

    I uninstalled everything again (VS 2005 RC, SQL Express, .NET 2.0, etc), re-installed, and got same result. Can't even add a database file to a new project. I then created a new user on the machine and was able to add a database to a new project. I opened the project I had done in Beta 2 and when trying to open the database received an error saying that converting from a non-release version of SS (592 if I recall correctly) was not supported and I have to recreate the database. Something in the original user profile is causing the problem.

    Something in my current user profile is preventing me from using SQL Express databases normally.  And this something is not corrected when VS.NET 2005, SQL Express, .NET 2.0, etc are uninstalled and re-installed.

    Now, if I do a runas and use the new profile I added, I can do a sseutil -l and here is what I get:

    C:\sseutil>sseutil -l
    1. master
    2. tempdb
    3. model
    4. msdb


    A sseutil -l on each of these says they are system databases.  Are any of these the "logical" database you refer to in your post?

    I really would like to figure out what in my current profile is causing this problem, but for now have resorted to using the new profile to continue development on my application.

    -- Christopher C. Bernholt

    Sunday, October 16, 2005 3:45 PM
  • OK.  First know that the error message you are getting is very often caused (if not by the issues described by my original post) by using Terminal Server to get into the box and then using SSE.  There is a fix for the TS bug posted by Windows. 

    But, the error message is fairly general; it catches a number of different issues. Your original bug description didn't mention TS and you are *currently* able to get into SSE under a different profile. 

    So, here's some additional info about how SSE runs under this mode.  When you first run SSE this way, it makes a local copy of master etc. in C:\Documents and Settings\<your user account name>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS.  That's why it's so slow the very first time it loads under a new user profile.  It may even timeout the first time you ever use it under a given profile.

    Based on your description, it's very possible something went bad in those files for some reason.  So, I would first make a backup and then delete the SQLExpress directory under C:\Documents and Settings\<your user account name>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data.  That will get rid of the local copy of master, etc.  Then, reboot. 

    When you run under your user account again and try to connect to SSE, it will recopy all of the appropriate files (master, etc.) to your local settings.  It should work after that.  You shouldn't worry about the recopy of master etc.  Generally, unless you are adding logins, etc, (which you don't need to if you are working with UserInstance=true) the recopy of master should be fine.

    Monday, October 24, 2005 7:06 PM
  • I get the same probleme on my server but none of the answers abose solve my problem. I also try to put all rights on Database directory to Everyone.
    But for the last post i have no content in document and settings for my IIS_WPG members then i can't delete this files.
    Is there a compatibility issue between June CTP .mdf generated files and september CTP files ?(i use my old database)

    Respectfully
    Tuesday, November 01, 2005 12:55 PM
  • I got the same problem here.

    The hotfix did not correct the problem.

    Running SSEUtil -l under a different user account works.

    Tuesday, November 01, 2005 3:07 PM
  • If you have the June CTP and then upgraded to the September CTP, you may well have issues with your installation.  If you had the ability to log into SSE with the June ctp on the primary account, then there will be files at : C:\Documents and Settings\<your user account name>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS. You will need to view hidden folders to see it, but it will be there. 

    Wednesday, November 02, 2005 4:22 AM
  • thanks but it does no solve my problem on my server i have just copy my .mdf and there was no .mdf in documents and settings directory. So i have nothingto delete. And i see hidden folders and files : activate this option is my first action when i setup windows ;).

    I add that on my server the only one version that has never been setup is September CTP. This on my developpement machine where i have used several version of sql server. but on this dev computer it work fine...

    Respectfully

    Wednesday, November 02, 2005 4:29 AM
  • What is troubling is that you say that it works fine on a different account on the same machine.  If that is in fact true, then that speaks to the only thing that may be unique per your account.  The only data that is unique per your account is the data at the location I mentioned above.  But, you're saying, if I understand correctly, that there is nothing to delete at that location. So, that won't actually help.  So, I don't know what to tell you.  And, I don't think anyone is the SQL org could tell you either.  It just doesn't really make any sense. 
    Wednesday, November 02, 2005 4:49 AM
  • Thx. Deleting the above mentioned directory did actually help. We're up and runnning again.  :-)


    Ilsandor: Note that in \Documents and Settings\<your user account name> you should look in the "Local Settings\Application Data\"  not just the "Application Data\" diretory. (I did make that mistake myself.....) 

     

    Wednesday, November 02, 2005 6:44 AM
  • the problem is really simple i have one machine : developpement and one other : hosting.
    When i copy file from the first to the other i get that error when a run my asp.net website
    Wednesday, November 02, 2005 7:45 AM
  • And, on the machine where you have the problem, are you able to connect to SSE at all?  Or, are you only able to connect on the development machine?
    Wednesday, November 02, 2005 3:52 PM
  • I can Connect SSE but its when i'm listing databases i have only default databases (master ...)
    I suppose that's normal why my database has never been attached on the server (i has always failed with this error :( ).

    Perhaps the folder for IIS_WPG member accounts is not document and settings?

    Respectfully,
    Wednesday, November 02, 2005 6:10 PM
  • It sounds like attaching using the auto-attach functionality is failing for some reason that is not clear to me.  I suspect that your installation is faulty for some reason

    Thursday, November 03, 2005 5:17 AM
  • Lance,

    Is this problem fixed in the final release of Visual Studio 2005??
    Wednesday, November 09, 2005 9:03 PM
  • If the problem is a conflict with a previous version of VS, then no. 

    By the way, you're not, by chance, terminal serving into this box, are you?  If so, then there is another fix you would want to know about at:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;896613

    Thursday, November 10, 2005 1:05 AM
  • I am experiencing the same problem.

    My asp page is very simple.  I have two databound controls on the page, and they connect to the DB with two different SQL Data Sources.  An attempt to run the page in my build environment works perfectly.  An attempt to access the page remotely is a disaster - I get the error message:

    An attempt to attach an auto-named database for file C:\Documents and Settings\kendray\My Documents\Visual Studio 2005\WebSites\WebSite1\App_Data\Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

    I'm at my wits' end.  I have been working on this for a solid week now, with zero progress.  I have tried every suggested fix in this thread; multiple times.  I cannot make it work from a different user context, either.

    I am not TSing into the machine.
    Thursday, November 10, 2005 4:59 AM
  • I did have the same problem after uninstall of beta-versions and new install of Visual Studio 2005 (final release). The problem was not related with Terminal Services. I just had to delete the SQL Express Data under my profile as mentioned by LanceDelano ...

    thx!

    Pieter
    http://kinnie.blogspot.com/2005/11/trouble-connecting-to-sse.html
    Wednesday, November 16, 2005 10:05 AM
  • Is it because you have an entry in the web.config file to attach a database with non-existant file?

    Sunday, November 20, 2005 8:41 PM
  • i have a iis 6.0 in my hosting computer i was having the same problem u describe but i solved by adding NT_AUTHORITY/Networkservice on my wwwroot folder and is working
    Good luck
    Monday, November 21, 2005 2:50 PM
  • I,m having the same problem. I tryed to install and run the SQL Server 2005 Eval and use the management studio. After that my app was broke. I also reinstalled everything. I plugged the error message into google and got to this blog.
    Thursday, November 24, 2005 4:25 AM
  • ok, I have the same problem and I don't understand how deleting the local SQL Express Data under the profile would fix the problem. It's not logical.
    The problem is happening when running the application from a live server but it does work perfectly on my local machine.
    Wednesday, November 30, 2005 11:01 PM
  • Hi,

    I setup last releases and i get always the same problem. I think there is really a serious bug with SQL Express

    Respectfully
    Cyprien Autexier
    Wednesday, November 30, 2005 11:05 PM
  • I have the same problem and I don't understand how deleting the local SQL Express Data under the profile would fix the problem. It's not logical.
    The problem is happening when running the application from a live server but it does work perfectly on my local machine.

    Thursday, December 01, 2005 12:00 AM
  • You have a different problem.  Your problem is that you are trying to use a local data feature on a remote server.  This will not work.  This is a scenario we considered.  However, once you are using a database on a remote server, the right way to use SSE (or any other edition of SQL) is to formally attach the MDF and leave it attached.
    Thursday, December 01, 2005 6:59 AM
  • OK, once you use management studio, the MDF is attached in a traditional SQL Server style attachment.  You must detach it in order to use it in a local MDF style scenario.  When you use the local data feature with SSE, the database is dynamically attached and then released so it can be copied around, etc. 

    When you attach via management studio, it doesn't release it dynamically the same way.  Detach it and you should be ok to use it in a local database style scenario.
    Thursday, December 01, 2005 7:03 AM
  • Well honestly, I'm so hopelessly confused.

    I published a web application to a clean server with a clean installation of SQL Server 2005 Express and I'm getting the above "An attempt to attach an auto-named database for file...".  Using SSEUtil I am able to see the list of 4 server-databases.  I can view them and navigate them just fine.  There is no other database attached.

    I went into /documents and settings/[username]/local settings/. . . and found the "sql server express data" folder mentioned and renamed it to "sql server express data.old" and rebooted as some have suggested.  Re-ran SSEUtil, the databases in /documents and settings/[username]/local... were re-created and listed in the util again are the 4 server databases {master, tempdb, model, msdb}.

    I don't exactly understand what this has to do with why a file-attached database using ASP.NET has anything to do with these four databases.  These are getting attached fine, but at web-application startup time, my custom databases and aspnetdb.mdf files are not getting attached.

    Perhaps if someone would give a listed procedure from start to finish of how to overcome this error:
    "An attempt to attach an auto-named database for file [..appData/somefile.mdf]failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

    I am running SQL Server Express version 9.00.1399.06.

    Jonathan

    Thursday, December 01, 2005 1:55 PM
  • Ok, let's be clear once for all.

    Correct me if I'm wrong.

    1) When working locally, the SSE database is auto attached using the local path.  
    2) When deployed to a remote server, the SSE database has to be manually attached using the Studio tool
    3) What happen in a shared hosting environement as the database has always the same name. If you manually attach the database, what happen if another application is to be deployed on the same server?
    Thursday, December 01, 2005 3:40 PM
  • I have been struggling with this problem for several days - reading all that I can but so far the magic solution has eluded me.  Finally after some poking around in SQL Server Management Studio I managed to get it to work.  What I did was add ASPNET as a login to the sql server, by clicking on Security( under the server name - local server in my case) then Logins, then right clicking on Logins and selecting "New Login". I typed ASPNET in the Login Name box then clicked Search and typed AspNet and clicked Check Names.  That gave me <machinename>\ASPNET - I hit ok, then ok again to add this login. ( I am running on XP )

    Then I double clicked the newly added ASPNET login ( <machinename>\ASPNET ) and selected "Server Roles".  In that dialog I selected "sysadmin" and "serveradmin" hit ok. 

    Then I went back to my IE screen and hit refresh, and I was able to successfully connect to my database where I was previously getting the "An attempt to attach an auto-named database for file... failure" .  It works with the database attached to the server in SQL Server Mangement Studio, but doesn't work it if isn't attached there.

    The connection string in web.config is

    connectionString="DataSource=<MACHINENAME>;AttachDbFilename=C:\inetpub\<virroot>\app_data\<mdf_FileName>.mdf; Integrated Security=True"

    There must be a better way to do this, but this has at least go me going.... hope it helps...

    Saturday, December 03, 2005 6:50 AM
  • Using SSEUtil, try attaching a new database (file based).  Check out the attach commands.  Try something like :

     SSEUtil -attach c:\data\northwind.mdf

    Once you attach, then you should be able to list the databases current attached.  (i.e., you should be able to see 5 databses [the four plus your new one].)  If your just currently attached database shows up, you're good.  You should be able to work with it as a file.  (At least you know it's not SSE that's causing the problem.) 

    Saturday, December 03, 2005 11:42 PM
  • There are a few problems with the connection string above.

    1)  You are missing the User Instance=true clause, which tells SQL Express to spin up a separate process for the ASP.NET account, eliminating the need to explicitly grant permission to ASP.NET.  Note that you should never grant sysadmin priviledge to the ASP.NET worker process, since that is a very priviledged permission level.

    2)  You should be using the |DataDirectory| variable in place of the fully-qualified path to the database, for example: |DataDirectory|\<mdf_filename>.MDF

    3)  MACHINENAME must be local when using SQL Express.  Consider using .\SQLExpress for the machine name.


    I believe that only issue #1 is affecting the error you receive, but you should also fix #2 and #3 just in case.  Check out the more detailed description of the local database connection string format here:

    http://beta.asp.net/QUICKSTART/aspnet/doc/data/vwd.aspx#intro 

    You should also make sure the database is not already attached to another SQL process, as Lance points out above.

    Hope this helps,
    Bradley Millington
    (The Web Platform and Tools Team)
    Monday, December 05, 2005 4:45 PM
  • I get a similar error in an application in C# this application is developed using a SQL server 2005 Database destined to be stand alone... When i am debugging the application i got no problem at all. I use the publish wizard and then distribute the application among diferent systems. The installation though slow it completes successfuly, but when the application starts and request for data the Attempt to attach error surges...
    I tried almost everything with disturbing successes and failures...
    Strangely enough in some systems it work instantly without need of fixes and in others it required the weirdest, Thing I done in the end are listed:
    First I deleted the files for the user
    Then I accessed the database in an absolute path (c:\Mydatabase.mdf) and finally
    I named the database changing the value of the initial catalog property in the connection String
    While this seems to work I am deeply concerned about the viability of this solution. I need to install this application to many machines and for that it will be very dificult to delete and change files on all of them
    How can I make it work right?
    Is there a way to publish the application so that it doenst get this error?

    My connection string is:
    Data Source=.\SQLEXPRESS;AttachDbFilename=c:\TM.mdf;Initial Catalog=TMAppDataSource;Integrated Security=True;Min Pool Size=15;Max Pool Size=120;Connect Timeout=90;User Instance=False;Context Connection=False

    And seemed to work in the installed computers... but something seems to be wrong...

    by the way I tried:
    Data Source=.\SQLEXPRESS;AttachDbFilename=c:\TM.mdf;Initial Catalog=TMAppDataSource;Integrated Security=True;Min Pool Size=15;Max Pool Size=120;Connect Timeout=90;User Instance=True;Context Connection=False

    But with little success...
    What is wrong when I publish my app?
    Wednesday, December 07, 2005 5:35 PM
  • Use SSEUtil to get a sense for what is going on with the database service itself.  There are two instances of SSE you can attach to.  The first is the traditional service and the other is the user instance version.  When you use SSEUtil, by default, you connect to the user instance version.  With that connection, list the databases.  Detach anything that is not one of the key SQL Server databases (e.g., master, model, etc.)  Then, connect to the main instance.  You can do this by using a switch with SSEUtil.  Repeat the same procedure. 

    You can test attaching by using SSEUtil directly.  There are commands that will let you do that.

    Then try using VS to connect and work with the MDF.

    Wednesday, December 07, 2005 7:56 PM
  • You say ...
    " and seemed to work in the installed computers...but something seems to be wrong..."

    When you publish your application via clickOnce, it uses a relative path reference.  However, you are using a hard-coded reference ("C:\TM.MDF").  You need the relative reference ( AttachDbFilename=|DataDirectory|\TM.mdf ).

    Wednesday, December 07, 2005 8:34 PM
  • (Not sure if my problem is posted to the appropriate forum)


    I have had a problem connecting to SQL Express database from a VB express application.

    I wrote an application on my home computer and all the database functions work. When I move the application to my work computer the database functions do not work. The application, the sqlserver express and vb express are all locally on my work computer.

    My home computer is not on a network, the work computer is on the company network.

    In a new project I
    1. Click "New database",
    2. Select "Database" for data source type
    3. Click "new connection"
    4. Browse to the database file myfile.mdf
    5. use Windows Authentication
    6. click "Test connection" and I get the error message below,

    The error message is,

    "--
    Unable to open physical file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\myfile.mdf". Operating system error 32: 32(The process cannot access the file because it is being used by another process.)"
    An attempt to attach an auto-named database for file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\myfile.mdf" failed. A database with  the same name exists, or specified file cannot be opened, or it is located on UNC share.
    --"

    I have tried many other things as well with sql server authentication etc. One of the other messages is,

    "--
    Failed to generate a user instance of SQL server. Only an integrated connection can generate a user instance. The connection will be closed.
    --"

    Any help with this would be appreciated.

    Thanks
    Thursday, December 08, 2005 9:29 PM
  • I'm a bit confused by your post.   There are a couple of ways to create a new database.

    Scenario #1.)  The traditional way to create a new database is with an existing database running as a server.  In this scenario, from the SERVER EXPLORER, you choose "Create New SQL Server Database".  This will bring up the Create New SQL Server Database Dialog.  If you are using SQL Server Express (SSE) as install by default with Visual Studio, SSE will have installed to the "SQL Express" instance.  Thus, to create a database here you will need to type ".\SQLExpress" in the dialog for the server name and then type in a logical database name and choose OK.  This will create a database on SSE as it runs as a SERVICE.   Creating or using a project is orthoginal to creating a database in this scenario.

    Scenario #2.)  A second way to create a database is new in VS 2005/SQL 2005.  It involves using USER INSTANCES, which are separate instances of SSE spun up locally in your user account. (They go away automatically when you application stops using the database.) A User Instance version of SSE is NOT the Service version described in 1 (above).  In Visual Studio, one way to do this is to create a new project and then, in the Add New Items (right click on the project node), choose to create a new database.  This will create an MDF in your project source directory.  When you design, etc the database in this scenario, it is using SSE User Instance capability.  When you build your .EXE, it actually copies the MDF into your output directory.

    Scenario #3.)  Now, a third way is a variation of an 2 above.  It involves adding an EXISTING MDF already created.  However, you need to be careful.  If you created that database via the 1 scenario above, you must first detach it before you can actually add it to your project.  SSE/SQL won't let you copy an MDF that is currently in use by a service into your project. VS should complain if it can't copy it.  But, other than that, after you actually copy the MDF into your directory, it should work like #2.

    Now, the reason I'm confused is that there is no place to click "New Database" unless you are talking about scenario #1.  And, if you're talking about scenario #1, then you can't just copy the MDF around and move it to your work computer unless you formally detach the database etc. (And, you didn't describe anything like that.) 

    In the #2 scenario when you "Add a New Item", it does create a new database for you automatically.  But, it also creates a connection for you as well.  So, you shouldn't need to create one.  So, I'm confused about your step where you explicitly create a connection.  If you did #2, you shouldn't have to create one. If for some reason a connection is not present, you can create one by simply double clicking on the MDF in the Solution Explorer.

    In both #2 & #3, you can move the MDF up to a SQL Server Server when you're ready by simply copying the MDF and LDF to the server and then attaching them.

    Now, one thing that may be going is that you are doing #1 but you are using the connection dialog provider that specifies that you want to use the #2 type approach.  You do mention the Data Source dialog which is available when making a connection.  You may be choosing the SQL Server Database FILE choice when you are really using it in the #1 type scenario.   Choose the SQL Server type provider for the #1 type of connection.  Use the SQL Server FILE type provider for the #2 & #3 type connections.   VS automatically using the FILE type provider if you use the Add New Item/Add Existing Item route from the Solution Explorer.
    Friday, December 09, 2005 6:36 PM
  •  

    Here's a revelation, check this location also:

    C:\Documents and Settings\[ YOUR COMPUTER NAME ]\ASPNET\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

    Monday, January 16, 2006 2:08 PM
  • While adding a Data Source in VB2005EE I also got this error which you received:

     "Failed to generate a user instance of SQL server. Only an integrated connection can generate a user instance. The connection will be closed."

    Solution that worked for me:

    On the "Add Connection" Dialog, click the "Advanced" Button. Go to the Property "User Instance" under "Source". Change the value to "False" & you are done.


    • Proposed as answer by Diego Thoms Monday, October 05, 2009 7:52 PM
    Tuesday, January 17, 2006 10:57 AM
  • Checking "User Instance = false" in the connection dialog will create a connection to the main instance as described in previous parts of this post.
    Tuesday, January 17, 2006 8:52 PM
  • Ditto for me, I have beating my head on the monitor trying to figure this one out, I could NOT get the ASP.NET Configurations selection on the Website menu of Visual Web Developer to work, it kept telling me it could not establish a connection with the Database.  Once I deleted that directory it worked fine (so far)
    Sunday, January 22, 2006 2:03 AM
  • What worked for me:

    1. In VS2005, Build->Publish Web Site
    2. Target location: C:\test
    3. Click OK

    Tried deleting the SQLEXPRESS dir in docs and settings (for both admin user and machine name user), detaching dbs with SSE tool, altering the connectionString in web.config but none of that worked so after almost giving up I tried the above procedure and now it works.

    I would really like it to work with the default path that VS2005 suggests in the Public Web Site dialog window. Microsoft?

    Greets,

    ZAiNT

    Edit: My app. is not working 100%. The "An attempt to attach an auto-named database for file.." error messages are gone but instead my login module is broken (can't login to the app.) Any clues?

    Edit2: Login works now. I set read/write permission for the ASP.NET user for my .mdf .ldf files in App_Data dir under my c:\test app dir http://forums.asp.net/941457/ShowPost.aspx
     

    • Proposed as answer by roofpro Wednesday, March 03, 2010 2:22 PM
    Wednesday, February 08, 2006 5:49 PM
  • I am getting the same error using visual studio c# with sql server 2005 express.

    It was actually working for a while then every once in a while it would stop working (restart would cure) and now I can't get it to work at all.  Actually I found a work around solution, but  I cannot get the user instance feature to work.

    When I right lick on the solution to add a new database, it gives me 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."

    When I am using database explorer to browse the database I already created, the only way I could get it to attach the database was to:

    Give permision to access "my documents" on the profile I am using on my computer to everyone.  I probably could have just added the user name that the main instance of sql uses to run under.  But didn't really care as when I publish the app, it won't be under "my documents" anyway.

    Then I had to change the user instance to false.  If you don't do the previouse step you get another error because the main instance does not have permission to attach the database (if you created in your my documents, this also depends on how you have your profile set up in xp) because it does not have access to "my documents".

    Is there any advantage to using the "user instance" feature over not using it?  This is a stand alone app and the sql server will only be accessed locally from the machine it is running on.  I would have used an access file to make my life easier but I wanted to learn a little more about sql servers and here I am...

    Tuesday, February 21, 2006 3:04 PM
  • Nevermind, I was usning remote desktop from my laptop on to my main computer and I guess that is a known bug.  However, if you logon localy first and then use remote desktop, I guess it works.  You just can't reboot your computer and logon from a remote desktop for the first time or for some reason, you can not create a user instance for sql server 2005 express.

    oh well, in the process of this, I lost the database I started to create, so now I'm back at square one.  At least I know how to make it work, I just have to get off my but and come upstairs and do it off the main computer.

    By the way, if you log in remotly after a reboot, you have to do a restart and logon locally to get it to work.

    Tuesday, February 21, 2006 11:55 PM
  • hii

    this has occered because the volume c (drive ) must have been compressed to reduce the disk space.,,, try to uncompress the the c drive ...

     

    balaji

     

    Saturday, April 01, 2006 2:30 PM
  • i had this same error, but it was due to me having an extra ODBC datasource (created by VISIO 2003) i believe. Fixed by; Goto Control Panel> admin tools> Datasources> File DSN tab then i deleted the overlapping datasource after 3 days of misery.... *Relief*
    Thursday, April 20, 2006 5:32 PM
  • I've seen this sort of behaviour before.

    The same thing occurs with XML data in Internet Explorer when using 'Run As'

    I believe the basic issue is that the full profile configuration (temp directories, user folders, etc) is not created unless the user account has actually logged on to the machine directly.

     

    Monday, May 08, 2006 9:38 PM
  • Failed To Generate User Instance:

    When your connecting to your server are you connecting to 'localhost' or 'localhost\SQLEXPRESS' ?

    How I fixed the dreaded error: An attempt to attach...failed

    The reason I was getting this error was because I had the database attached to my sqlserver on my local machine. 

    After 3 days of brain ache I finally figured out that you have to detached it from your local server before you upload it to your host. 

    Saturday, June 03, 2006 3:03 PM
  • wow, 4 pages.  This comes to the humor statment, 'how many engineers does it take to over-complicate a small issue?'  but I think this post will help most of you having this issue with ASP.NET.

    Resolution:  Security Requirements


    On 2003 Server boxes;  ASP.NET's default security account of running a web site is 'Network Service'.  Web sites & virtual directories run in 'Application Pools'.  Application pools have an an account that they run as.  First determine what Application Pool, your site or virtual directory is in, and then locate the Application Pool in the 'Application Pools' tree item.  View it's properties.  The Identity tab will tell you which security account the application pool is running as.

    On 2000 & XP boxes; the default security for asp.net is the 'ASPNET' security account of the machine.

    Which ever account your web site or virtual directory is running under it must have full access to the folder that the database file is located in (not just the database file, unless you place the database log file in the folder and give full access to both files).  Generally this file is placed in the App_Data folder.

    The database file cannot be attached and in 'online' mode with a SQL Server while attempting to use the file using SQL Express against the file.  You can take the database offline of a SQL Server without detaching and use it with SQL Express.

    Connection String Example :

    Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;user instance=true;Integrated Security=True;Initial Catalog=ASPNETDB;


    Cheers,
    Brent VanderMeide
    Software Architect, MCP
    brent@codeimpressions.com

    Code Impressions, LLC

     

    Tuesday, July 11, 2006 7:21 AM
  • Not in my case.

    For me, it occurs when my shared hosting server comes under load.

    Rest of the time it works fine.

    I think it must be some sort of connection limit or timeout issue, but I haven't been able to find a sonnection string parameter to correct it.

     

    Tuesday, July 11, 2006 9:03 PM
  • I'm curious if your hosting is using load balancing, and if the issue is that file is locked and in use by a copy procedure?

    just a thought to ponder

    Tuesday, July 11, 2006 9:31 PM
  • Possible.

    It hasn't been a consistent time of day that I've noted it.

    and it hasn't been mentioned by them.  I'll ask them.

     

    Thanks for the suggestion

    Tuesday, July 11, 2006 9:34 PM
  • I think i found a fix...

    Go to Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager

    - Right Click on your SQL server mine is (SQLEXPRESS)
    - Go to properties
    - Change the Built-in account from "Network Service" to "Local System"

    and it works!

    hope this helps.

    John

    Sunday, July 23, 2006 4:52 AM
  • If this resolved your issue then this would primarilly indicate that your SQL Express database did not have full permissions for the Network Service  account.  Running your SQL Server in Local System account gives that process pretty much full access to your system.  Where the Network Service is far more limited.

    It's a toss up between caring about security and the cost of maintaining securityIf your computer that the file and SQL Server resides on contains financial, account, and/or other highly confidential information, you may not want give SQL Server free reign on your hard drives (Local System account).

    I would recommend that you set the permissions back to Network Service, and add Network Service to have full permission to the SQL Express database file that you're using. 

    If your application does not work after giving the Network Service account full access to your SQL Express database file, make sure that the account running the application that is connecting to the file has full access to the file as well. 

    It's important to note that files can contain security permissions seperate from the folder they're contained in.  A big issue I've seen others run into is checking the folder's security not the file's security settings.  The permissions differences are often caused by copy/paste operations where the source location of the file has different security settings then the folder it's being placed into.  The security permissions will be kept from the source location if the src and destination folders are on local hard drives.  You can remove all file specific permissions in a folder by:

    1. Open the properties of the folder from Windows Explorer
    2. View the 'Security' tab ( may not be available, and if you want instructions on how to show these, ask )
    3. Click the 'Advanced' button in the security tab screen
    4. Place a check in the 'Replace permissions entries on all child entries...' checkbox
    5. Press 'OK'
    6. You will be prompted to confirm your choice of replacing permissions
    7. Press 'Yes'

    Good luck, and fear not asking questions,

    Sunday, July 23, 2006 6:26 AM
  • A local file database is attached to SQL Server Express dynamically through the use of a relative path connection string. The relative path ensures that the application can be moved to another location without breaking the connection to the database. A relative-path connection string in a Web application is specified as follows:


    "server=(local)\SQLExpress;AttachDbFileName=|DataDirectory|MyDatabase.mdf;Integrated Security=true;User Instance=true"

    There are two additional properties of the above connection string. The AttachDbFileName property specifies the location of the database file to attach to the server dynamically when the connection is opened. Although this property can accept a fully-qualified path to the database, this example uses the |DataDirectory| syntax, which will be substituted with the path to the Web application's App_Data directory at runtime. This is what allows the application to be moved to a different location without breaking the connection. Secondly, the User Instance=true property dictates the way in which SQL Server Express will attach to the database. In this case, SQL Server Express spawns a new process running as the user that opened the connection in order to attach the database to this new instance. In an ASP.NET application, this user will be the local ASPNET account or Network Service by default, depending on your operating system. A separate user instance of SQL Server is required to securely attach database files from a non-administrative account, such as the ASP.NET account.

    Regards,

    Nicolas

    Friday, September 08, 2006 10:08 PM
  • Hi Brent - and anyone else that is prepared to help me,

    This is response to your "fear not asking questions" comment.  I am at wits end, having tried to fix this numerous times over the last 4 months.

    I would be very grateful - in the most meaningful way - to get this one solved as it creates all sorts of problems for me.

    I have started again - for the 20th time - by:

    1. Creating a new file-based website on my (up to date) Windows XP machine, with SQL Server Express installed on it.
    2. Adding a SQL Server Express Database file in the App_Data folder.
    3. Creating and populating a table.
    4. Adding a Gridview to the form.
    5. Configuring the DataSource to point to the file and the table.
    6. Testing what I've done by running the application within VS 2005 (Team Suite Version) and Visual Web Developer.  All this works perfectly, first time - and the data displays in the gridview as expected. 
    7. Creating the IIS Application. 
    8. Assigning full rights to the folders and data files.
    9. Checking that SQL Express is running
    10. Checking the SQL Browser is running
    11. Checking that SQL Server TCP/IP and Named Pipes protocls are enabled under SQL Server Network Configuration / Protocols for SQL Server Express
    12. Checking that TCP/IP and Named Pipes client Protocols are enabled under SQL Native Client Configuration / Client Protcols
    13. Checking that the file isn't attached to SQL Server Management Studio Express.

    Non-data forms work perfectly from http:/localhost/foldername, but forms containing SQL Express Data don't work.  The error message I get seems to be the same as what everyone else is getting:

    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.

    Here is a list of threads I have tried to apply:

    http://forums.microsoft.com/msdn/showpost.aspx?postid=98346&siteid=1

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

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

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

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

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

    http://www.sqljunkies.com/WebLog/ktegels/archive/2005/11/15/17401.aspx

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

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

    My thoughts are that the problemis related to installing Visual Studio 2005 Team Suite Evaluation on a machine that already had SQL Server 2000 and SQL Server Express on it, because it was at that point that things stopped working.  I uninstalled SQL Server 2005 (with some difficulty) and reinstalled SQL Server Express - and the latest build version, to no avail.

    To be frank, I can hardly believe thta in this ay and age it can be so hard to get something like this working!

    Please help - anyone!

    Regards

    Gary

    Saturday, September 23, 2006 8:01 AM
  • This solution won't work for me because I do not want to use Windows Authentication. I want to use SQL Server Authentication i.e. sa. So if anyone has any insight, please share. (step by step if possible)

    I have tried the tricks listed above, include, reinstalling, checking folder level security, and mucking with the SQL Server Configuration Manger to use Local System. - No Dice Folks :(

    I don't have complete admin access to my box - fyi - so i have to use sa.

    Please help!

    Thanks,

    h.

    Monday, October 23, 2006 4:52 PM
  • I haven't read all of the thread but based on the first couple of pages alot of people seem to be having the same program as I am.  I tried sseutil, I tried deleting the sqlexpress folder in the data directory under my profile.  Then I deleted the ldf file located in the same folder as the mdf file and presto everything is ok now.  Maybe somebody already suggested this in previous replies, but I only read the first page. 

     

    Aaron

    Sunday, November 05, 2006 6:49 PM
  • I know it's been awhile since you posted your issue.  The one thing that seems to stand out, is that you have SQL Server 2000 & SQL Server 2005 Express installed on the same machine.  When you open SQL Server Management Studio, what server are you connecting to?

    If you're just connecting to 'localhost', then you're connecting to the SQL Server 2000 instance.  To access the SQL Server 2005 Express instance you need to connect to 'localhost\SQLExpress'

    Maybe you've been checking your SQL 2000 Server instance rather than the 2005 Express instance.

    Let me know.  I'll look into it more after I hear back from you.

    Cheers,
    Brent VanderMeide
    Software Architect, MCP
    brent@codeimpressions.com

    Code Impressions, LLC

    Monday, November 06, 2006 10:32 AM
  • When SSE was installed, did you go through the custom setup actions and ensure you had SA rights?  If you take the default install, you won't get these rights.

     

     

    Tuesday, November 07, 2006 1:57 AM
  • Has this problem actually been solved by anyone, perhaps Microsoft by any chance?

    I had this message recently when trying to connect to my database upon rebooting after a crash of my system and was a bit confused as to what was going on. After looking into this in more detail, I also found out that I was getting the same error message whenever I attempted to connect via sseutil in console mode [sseutil -c]. By examining the childlist [sseutil -childlist], I then found that the status of the child process was dead. From what I am guessing the error message is saying that the user instance exists, which it does, however, it cannot start the process anymore because it is dead.

    So, logically thinking, why not remove it and then a new user instance will be created. Ah, but yes, it is in a system table, sys.dm_os_child_instances to be precise and we are no longer allowed to update system tables (or are we??).

    Only logical conclusion I came to after all this, uninstall SQL Server and re-install and it works fine, until of course the next time I have a dead user instance process. So the question remains, has anyone got a solution for getting around this?

    Wednesday, November 15, 2006 3:26 AM
  • Unfortunately, your guess is incorrect. It is perfectly normal for sys.dm_os_child_instances to report a User Instance as Dead, that just means that the User Instance is not currently running. When a User Instances is running, it is reported as Alive. This has nothing to do with the ability to start a User Instance. It is the designed behavior of User Instances that they are automatically shut down if no connections are made to the User Instance within a specified timeout. The default time out is 60 minutes and can be configured via sp_configure and the 'user instance timeout' setting. You can find a broad overview of User Instances here.

    It is also incorrect that sys.dm_os_child_instances is a system table, it is not. This is a Dynamic Management View (DMV) and in the case of User Instances, the list is built dynamically out of memory when you make the call to the DMV. Information about User Instances is not stored in a system table and you do not need any special permissions, beyond file permissions, to delete the special directories related to a specific User Instance. Once warning I'll offer is that when you delete that directory, you are also deleting the User Instances specific system databases (master et. Al) and any specific configurations you have made to that User Instance. If you've put any user databases in that directory, you would be deleting those as well. I have here the "nuke it and start over" approach works in some cases, but not in all, so I generally don't recommend this as a troubleshooting step in solving User Instances problems, certainly not as the first troubleshooting step.

    There are a number of different causes of the error that titles this thread, I count 13 different error strings. Unfortunately, the sentance that describes the specific problem comes after the part that reads "Faild to generate a User Instance of SQL Server..." so from the title of this thread I can't tell you what the real problem is. The best advice I can give you is as follows:

    1. Read the whole error message as there are multiple error messages that look the same if you only read the first seven words. The second sentance often gives you more information.
    2. Check the error log for the main instances of SQL Express to check with issues that happen before the User Instance is even started.
    3. Check the error log for the specific User Instance for issues that cause a failure within the User Instance itself. You can find this log in the User Instance directory.

    With this information in had, you will have a better idea where the real problem lies. The most common "Failed to generate..." error comes when you move your application to production and you are now running your application under a different user who does not have an interactive login profile. This is common for web applications which will run in an Application Pool using either ASPNET or Network Service as the user (depending on which version of Windows and IIS you are using). Since these users typically have not logged in interactively, there is no User Profile, and thus no directoy structure for the User Instance directory to be placed in.

    I do not recomend User Instances for use with web applications, when you move a web application to production, you should really attach your database directly to the production server and change your connection string to remove the AttachDbfilename and User Instance keywords. User Instances are designed primarily for storing local data in single user, WinForm applications. User Instances provide some ease of use when you are developing web applications because they allow you to keep your database with your project and VWD has done the work to make User Instances work well with the builtin Casini web service, but you will have problems with them when you move to IIS. (As a side note: One of the best discussions on SQL connections and ASP.NET as they apply to using SQL Express is in chapter 11 of a book titles Professional ASP.NET 2.0: Security, Membership, and Role Management from Wrox. The discussion is centered around the aspnetbd database and membership, but there is plenty of information that can be more broadly applied.)

    The error mentioned about "An attempt to attach..." is related to the connection between the database name and the location of the database file. Again, there has been too much divergence of topic in this thread for me to give any specific analysis, and there are a couple different causes of this problem as well. In general I can tell you that User Instances provide functionality to automatically name a database when the database is attached. The name of the database is typically based on the location of the file. A couple reasons you get this error:

    • There the autogenerated name based on the file path is already listed in the User Instances' master database but is associated with a different file.
    • You have specified a database name in your connection string (i.e. Initial Catalog=foo) and that name is already associated with a different file.
    • Ocationally this can be cause when you try to switch a file back an forth between being used by the User Instance and attaching it to the parent instance to work with in using SSMS.

    The best way to resolve this issue is to use SSEUtil to detach the named database that is conflicting which should then allow your application to run and attach the database.

    I'm marking this thread as answered with this general guide. If these don't resolve the problems you're having, I recomend you submit you question as a new post on the SQL Express forum with specific information about you're failure including log findings and exact connection strings.

    Regards,

    Mike Wachal
    SQL Express

    Monday, November 20, 2006 10:01 PM
    Moderator
  • Without boring you with details.  Your suggestion did work for me!!  I had spent days looking at this problem.  I had to go back and cleanup all my mess, basically making sure that my data source was <servername>\SQLEXPRESS. This ran my project and I knew I was in luck when it took a little while to come up. When it finally did there was my app_data folder with it's corresponding aspnetdb as it should! 

    Thanks alot!!

     

    Tuesday, November 28, 2006 11:55 PM
  • I had a problem with getting this "An attempt to attach...failed" error when attempting to use User Management login controls and so forth on a remote box. I have a local install on dev laptop that worked perfectly fine, but no matter what I tried, even many of the things in this thread, it still would not work. Below is pretty much a derivation of many comments above, and from another blog on the topic located here: http://dotnetforum.lk/blogs/ammar/archive/2006/11/16/19574.aspx

    This is what fixed it for me. I cannot gaurentee this will help you all, but here goes.

    1.)  Added NETWORK SERVICE to owner list of App_Data.

    2.)  Renamed ASPNETDB.MDF to ASPNETDB2.MDF in App_Data folder.

    3.) Added following connection string to web.config.

    <remove name="LocalSqlServer"/>
    <add name="LocalSqlServer" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB2.MDF;user instance=false;Integrated Security=True;Initial Catalog=ASPNETDB2;" providerName="System.Data.SqlClient"/>



    Notice above that I set user instance to false, the other comments on this board say use true. I don't know why, but this works on my setup.

    My local pc is win xp pro, with SQL Express 2005 and Web Developer 2005 Express.

    My remote server is win2k, with SQL Express 2005 and Web Developer 2005 Express alsoinstalled.

    I moved my entire project files from the local pc to remote server using AllPrograms/Accessories/Communcations/Remote Desktop Connection
    to a folder that was setup as an ASP .NET 2.0.5.xxx site.

    Hope this helps. It took me over a week to figure this one out for my specific conditions. I would think that most people attempting to deploy to a remote server, using the express editions of the sql server and web developer programs would be in a similar boat.
    Thursday, December 28, 2006 4:37 PM
  • It is now January 5 2007 and I am getting the

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

    message, have tried all the fixes, but nothing works. I have NO folders called SQLExpress anywhere, and have re-installed countless times, and use only my own portable with only me logged on.   This is supposed to be for non-professionals?!   It isn;t a great introduction to .NET, maybe Open Source is the way to go, as I've been reading posts for days, dating back to 2005 all with similar error messages, but none with a fix that seems consistent nor reliable.

    Friday, January 05, 2007 1:43 AM
  • Here is something that worked for me.

    Using SSEUtil.exe to try and list the databases.  (SSEUtil.exe -l)   I found I got the same message, then I tried  SSEUtil.exe -l -m and it worked.  Hmm

    So it works if I state use the main instance.  So I figured it does not know where 'my instance' is located?  Even though I put the mdf (for time tracker starter kit)  right in the default Data location. 

     

    So I go back to my web.config and found the connect string was not specific enough. I changed the value to this: 

     

    <add name="aspnet_staterKits_TimeTracker" connectionString="Data Source=.\SQLExpress;Integrated Security=True;AttachDBFilename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TimeTracker.mdf';User Instance=false"/>

    <remove name="LocalSqlServer"/>

    <add name="LocalSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;AttachDBFilename=;Data Source=.\SQLExpress;Integrated Security=True;AttachDBFilename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TimeTracker.mdf';User Instance=false"/>

     

    And now it works fine.  It seems the default value of  |DataDirectory|  looks for a directory under where the app is running from.  If you expand this variable you can see where it is pointing to and put your mdf there is you want to also I would think.  But right now I just hardcode the directory for my developement purposes. In production this goes on a real server and gets a different connectstring anyway..

     

    Hope that helps some of you..

     

     

    Friday, April 06, 2007 4:09 PM
  • First of all I apologize for English. Your information was very useful, it helped me every time I got this infamous error message, except for this very last time. In this case, I delete the SQLExpress folder and reboot as pointed out by Lance but didn´t work. I have also SSE running as Local System, and set "User Instance = True" on my connection string. This happened on a testing server that it is only handled by me.

    After pulling the last hair off my head I came up with a solution. As in lots of other cases it was a permission subject. Somehow the "App_Data" folder of my project lost all the permissions it had except for "Administrators". So I added "SQLServer2005MSSQLUser" and "SQLServer2005MSSQLServerADHelperUser" with full permissions and "SQLServer2005SQLBrowserUser" with only Read&Execute, Read.

    Now it is working OK =)
    Wednesday, May 16, 2007 4:52 AM
  • failed to generate a user instance of sql server due to a failure in starting the process for the user instance

    I  encountered this error message at least 4 times sicne I started using SQL server express. I passed  a lot of time to understand the real cause of this error message. There are two commonly suggested solutions on different forums. But neither of them worked for me. For completeness of this post I include those suggestions and one more suggestion that worked for me.

    1.       Make sure that the sql express is running in default Local service account. To do that follow these steps.

    Open Configuration Manager of sql server

                All programs->Microsoft SQL server 2005->Configuration Manager

    Then click the SQL Server 2005 services node in the left and Double click the instance name which says   SQL Server(SQLEXPRESS). Then make sure if the server is running with Local services  built in account

    2.       the second suggestion that you maight have seen somewhere else is that to delete the following folder from your hadr drive.

    C:\Documents and Settings\[user]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

    the [user] should be replaced your windows login account name.

    though it didn't help me, this may be solution. if you can't locat the specified folder, leave this suggestion and think of something else like.....

    3. The 3rd solution that worked for me is to unstall some applications that may be competing with the sql server. In my case, I have installed MYSQL and I finally found that MYSQL have been competing with sql server  evnenthough I can't figure out why. any way I unstalled MYSQL server and restarted my pc. Every thing worked fine. In your case you may have some other program running on your machine that is blocking sql express to start the user instance version.

        good luvk

    Wednesday, May 16, 2007 2:59 PM
  • I think I found a solution at http://dotnettogo.com/blogs/emad/archive/2007/06/08/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_2E00_.aspx
    or if the link is too long click here

    Friday, June 08, 2007 2:00 PM
  •  

    when setting up sql express, when you get to the user name and organization
    dialog box uncheck hide advanced   configuration and select default instance,
    this error occurs alot when a named instance of sql server express is created.
    If a named isatnce is created it will have to be specified in the connection string,
    it is best to install sql server express with a default instance.
    Thursday, June 21, 2007 4:04 PM
  • Yes, after try many options in this thread your suggestion worked for me.
    Thanks.


    www.byteshift.com

    Sunday, September 09, 2007 3:58 PM
  • 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

    Friday, September 14, 2007 6:26 AM
  • For what it's worth there is a solution to this problem.  It might appear as a last ditch attempt, but it is much better than wasting a week trying to get to the bottom of this (as I have).

     

    I had VWD Express, VS 2005 Express, and SQL Server Express.  I recently purchased the VS 2005 Professional package, installed it - then ran into the problem that plagues oh so many.  I've tried every fix imaginable, none of which worked.  This is how I fixed it.

     

    step-by-step: (please read through entirely before attempting)

     

    1.) get *** Cleaner (CCleaner.com)

    2.) Uninstall VS 2005 Pro or whatever version you have. (Add/Remove Programs)

    3.) Uninstall EVERY SQL Server application that displays in the Add/Remove Program Window --> starting with SQL Server 2005

    4.) Delete the folder as mentioned in many posts.  For whatever reason, I had a hard time finding it - best idea is to run a search on your whole computer with the file name 'SQLEXPRESS'.  It is there - just need to poke around.

    5.)  During the uninstall process, take the time to make sure all Microsoft Studio/Sql applications are removed.

    6.) Run *** Cleaner.  Once this application loads, click the Issues button in the left panel.

    7.) Select the Scan for issues button.  This may take a few minutes.  When complete, select 'Fix Selelcted Issues...' button.  Make a backup of your registry then select fix all issues...

    8.) Download Visual Web Developer Express (http://msdn2.microsoft.com/en-us/express/aa975050.aspx) 2005 Edition.

    9.) Include Sql Server Express in the download.

    10.)  Download and install.  This will take awhile...

    11.) After Install, open VWD Express.  Create a New Web Site.

    12. Drag a login control on to your page as well as a LoginView Control.  Set the DestinationPageUrl, and the LoggedIn/Anonymous settings.

    13.) On Menu, select WebSite, ASP.NET Configuration.

    14.) On Security Page, select 'Select Authentication Type' then Internet

    15.  On Security page, Add a role, and a user then click done.

    16.) Save, Build Website

    17.) Run Debug mode - enter credentials on login page.

    18.) DOn't stop here.  Add a database to this project, with minimal data.  Make sure you can connect.

     

    Take the time to go through Sql COnfiguration Manager and Sql Server Surface Configuration Tools - and note the settings.

     

    Reinstall VS 2005 Full or Pro.  Do not delete the express edition, that is when my problems began.  Just make sure to change which Service your using when implementing Sql Server Developer Edition (in COnfiguration Manager) - if you decide to do so.

     

    ~javasource

     

    Thursday, September 27, 2007 4:28 PM
  • This is what fixed it for me (after hours of headbanging) 

     

    My original web.config:

    <connectionStrings>

    <add name="MySqlConnection" connectionString="Server=.\SQLEXPRESS;Integrated Security=false;Database=dbname;UID=username;Password=pwd;"/>

    </connectionStrings>

     

    Revised web.config:

    <connectionStrings>

    <clear/>

    <add name="MySqlConnection" connectionString="Server=.\SQLEXPRESS;Integrated Security=false;Database=dbname;UID=username;Password=pwd;"/>

    </connectionStrings>

     

    Note the added "clear" statement...

     

    Here is what i guess was causing the problem (how the connection string is fetched):

    {

    DbConn = new SqlConnection(ConfigurationManager.ConnectionStrings[0].ToString());

    DbConn.Open();

    }

     

    Note that the first connectionstring is retrieved (index zero)...

     

    Mr. Anton Klimov had this brilliant comment about checking machine.config in this other thread, which led me to the resolution...

     

    Hope this will save hours or days of frustration for someone...
    Tuesday, November 13, 2007 2:22 AM
  • Thanks, #1 worked for me.

     

    Friday, February 29, 2008 5:00 PM
  • Hi,

     

    I am trying to use the ASP.NET Website administration tool but I'm running into similar issues as a lot of you out there. The one thing that is different in my case is that I am using a sql server standard edition instead of sqlexpress. When I hit the sec tab, I get,

    "There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

    if I click to choose a new datastore, it gives me an option to choose only one provider, named, aspnetSqlProvider, which fails. It also suggests using the aspnet_regsql command-line utility to create and configure a database before using this utility.

     

    I am logged into sqlServer under local system and I dont have a Microsoft Sql Server data folder at location

    "C:\Documents and Settings\[user]\Local Settings\Application Data\Microsoft\

     

    I have looked under all the users that are on this machine and couldnt find the folder where sql server may be trying to create a directory for the users...

     

    Any suggestions anyone??

     

    Thanks,

     

    Ghazanfar

    Friday, May 30, 2008 3:38 PM
  • Thank you so much, this worked for me perfectly.
    Monday, July 21, 2008 8:32 PM
  • Yes!
    I added
    Initial Catalog=ASPNETDB to the connection string and also changed permissions of the .mdb and .ldb files and then it worked. many thanks
    Tuesday, November 04, 2008 8:22 PM
  • My project is not working there is following error ,,plzzhelp meeeeeeeeeeeeSystem.Data.SqlClient.SqlException was unhandled by user code
      Message="An attempt to attach an auto-named database for file H:\\NRLDC\\nrldc db\\dbNRLDC.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
      Source=".Net SqlClient Data Provider"
      ErrorCode=-2146232060
      Class=14
      LineNumber=65536
      Number=15350
      Procedure=""
      Server="\\\\.\\pipe\\31F19CC3-3C68-46\\tsql\\query"
      State=1
      StackTrace:
           at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
           at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
           at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
           at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
           at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
           at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
           at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
           at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
           at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
           at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
           at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
           at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
           at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
           at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
           at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
           at System.Data.SqlClient.SqlConnection.Open()
           at StateLogin.ImageButton1_Click(Object sender, ImageClickEventArgs e) in e:\NRLDC\nrldc\StateLogin.aspx.cs:line 31
           at System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e)
           at System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument)
           at System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
           at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
           at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
           at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
      InnerException:
    Tuesday, May 17, 2011 2:14 PM