Solved

Error opening sqlexpress db

Posted on 2011-02-26
45
1,682 Views
Last Modified: 2012-05-11
Received this 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."

Environment: Windows 7, Visual Studio 2008; SQLExpress 2008

0
Comment
Question by:eehunter3
  • 24
  • 13
  • 7
  • +1
45 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
0
 

Author Comment

by:eehunter3
Comment Utility
I had already found that web page. However, Windows 7 doesn't have a directory "Documents and Settings". Is there a Windows 7 counterpart?  I have searched throughout the "Users" directory and found no references to SQLEXPRESS.
0
 
LVL 7

Expert Comment

by:mmr159
Comment Utility
In windows explorer, ensure you have the folder options set to view hidden files and folders.
0
 

Author Comment

by:eehunter3
Comment Utility
Found it as a hidden file ...Deleted it and searched for other occurrences ... found none, received same error
0
 

Author Comment

by:eehunter3
Comment Utility
Also noticed that sql service is running while sql agent is stopped (Start disabled)
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
>> Is there a Windows 7 counterpart?  I have searched throughout the "Users" directory and found no references to SQLEXPRESS.

Type %Appdata% in Run prompt (Win+R) and go to Microsoft -> Microsoft SQL Server and delete all contents inside it..

and have you executed the below code in a new query window..

exec sp_configure 'user instances enabled', 1.
Go
Reconfigure
0
 

Author Comment

by:eehunter3
Comment Utility
Just got your post as I was submitting this (I don't know if it makes a difference or not):

Although I installed SQLEXPRESS using application SQLEXPRWT_x64_ENU, after looking at properties for the server and browser it is shown that they are not running under a 64 bit OS (I'm running Windows 7 Professional). Instance ID is shown as MSSQL10.SQLEXPRESS.

I'll follow instructions in your post.
0
 

Author Comment

by:eehunter3
Comment Utility
Query ran successfully ... deleted 90 folder however was told I did have permission to delete the 100 folder ,,, Getting same error
0
 

Author Comment

by:eehunter3
Comment Utility
Finally able to delete the last folder but fouled up my access system (unrecognizable administratos password). Error still the same when opening database.
0
 
LVL 3

Expert Comment

by:KetGuru
Comment Utility

Check your sql server and SQL Agent serive and if possible change account from Network to Local or define other domain accound if you are in network and start service again. should resolve issue.
0
 

Author Comment

by:eehunter3
Comment Utility
Switched to Local and received following message:

"The service cannot be started, either because it is disabled or because it has no enabled devices associated with it. (0x80070422)"
0
 
LVL 3

Expert Comment

by:KetGuru
Comment Utility
have you applied sql server SP1
0
 
LVL 3

Expert Comment

by:KetGuru
Comment Utility
sorry if it is silly question, did you have check that you do not have multiple instance of sql server express installed on your machine ? I have checked at my end i donot see any issue to be honest.
0
 

Author Comment

by:eehunter3
Comment Utility
I initially tried to install SQL Server Express 2008 SP1 but install would not continue past the "features" point as there was nothing to add. I found another forum post that recommended SQL Server 2008 R2 (SQLServer2008SP1-KB968369-x64-ENU). It installed OK and gave me SQL Management Studio.
0
 

Author Comment

by:eehunter3
Comment Utility
I think I Do:

SQL Server 2008 and
SQL Server 2008 R2

The last install (R2) was the SP1
Can I get rid of the other?
0
 
LVL 3

Expert Comment

by:KetGuru
Comment Utility
aha. Yes please get rid of SQL Server 2008.
0
 

Author Comment

by:eehunter3
Comment Utility
This is list of SQL Server installs. Do I remove the first one or the first three?.

Microsoft SQL Server 2008 (64-bit)
Microsoft SQL Server 2008 Browser
Microsoft SQL Server 2008 Management Objects

Microsoft SQL Server 2008 R2 (64-bit)
Microsoft SQL Server 2008 R2 Native Client
Microsoft SQL Server 2008 R2 Policies
Microsoft SQL Server 2008 R2 Setup (English)
Microsoft SQL Server 2008 R2 Setup Support Files

Microsoft SQL Server Compact 3.5 for Devices ENU
Microsoft SQL Server Compact 3.5 SP1 Design Tools English
Microsoft SQL Server Compact 3.5 SP2 ENU
Microsoft SQL Server Compact 3.5 SP2 Query Tools ENU
Microsoft SQL Server Database Publishing Wizard 1.3
Microsoft SQL Server VSS Writer
0
 
LVL 3

Expert Comment

by:KetGuru
Comment Utility
First 3 only
0
 

Author Comment

by:eehunter3
Comment Utility
Making progress ... However could not start server. Contents of Server Configuration Manager are:

SQL Server Services
SQL Server Network Configuration (32bit)
SQL Server Native Client 10.0 Configuration (32bit)
SQL Server Network Configuration
SQL Server Native Client 10.0 Configuration

Only the Native Client entrirs have content .... Others are empty.

Should I remove and reinstall SQL Server 2008 R2?
0
 
LVL 3

Expert Comment

by:KetGuru
Comment Utility
confuse now. what is windows 7 32 bit or 64 bit ?
0
 

Author Comment

by:eehunter3
Comment Utility
64-bit
0
 
LVL 3

Expert Comment

by:KetGuru
Comment Utility
ok now please make sure SQL Server 2008 R2 64 bit version installed successfully now means reinstall or repair whichever is convenient to you. services should start automatically at the end of installation.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:eehunter3
Comment Utility
Deleted and reinstalled SQL Server 2008 R2 64 ... Looks the same as before so I removed it. All I want to do is instal a 64-bit SQL Server 2008 Express to access my database.

Currently my setup is as follows:

Microsoft Visual Studio 2008
Version 9.0.30729.4462 QFE

Microsoft .NET Framework
Version 3.5 SP1

Windows 7 Professional (64bit)

Hewlett Packard Pavilion Elite HPE-410Y

I apologize for being so unknowledgeable but this is driving me crazy. Do you know what software elements  and installation order I need to get to where I'm trying to go.
0
 

Author Comment

by:eehunter3
Comment Utility
No suggestions received have really worked for me so I have reverted to SQL server 2005 (A configuration that ran successfully on XP) and still getting the same error message. Are there any SPs that should be installed?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Apply SP4 for SQL Server 2005 and try once..
0
 

Author Comment

by:eehunter3
Comment Utility
SP4 installation was successful ... same error message. Why is this so hard? :-) Also, I don't have Management Studio.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Kindly let me know what is the startup account of your SQL Server service..
Also let me know which edition of SQL Server you are trying to install either Express or Express with Advanced Services like that..
0
 

Author Comment

by:eehunter3
Comment Utility
If I'm understanding your question, The start-up account is C:\Users\Ellis Hunter

SQL Server 2005 was installed as a component of the VS2008 install:

Microsoft Visual Studio 2008
Version 9.0.30729.4462 QFE
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
>> The start-up account is C:\Users\Ellis Hunter

Hope your Startup account user Ellis Hunter is a domain user and has admin privilege on the local machine as well..

>> SQL Server 2005 was installed as a component of the VS2008 install:

Can you uninstall that and install SQL Server 2008 or 2005 given below:

2005:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b448b0d0-ee79-48f6-b50a-7c4f028c2e3d

2008:
http://www.microsoft.com/downloads/en/details.aspx?FamilyId=B5D1B8C3-FDA5-4508-B0D0-1311D670E336&displaylang=en
followed by SP1
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=66AB3DBB-BF3E-4F46-9559-CCC6A4F9DC19
0
 

Author Comment

by:eehunter3
Comment Utility
The initial attempt at installing sql server failed pending sp1 update for vs2008. After updating vs2008, sql server adv and sql server sp1 installed successfully. Same error message.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Ok, check for any additional errors in SQL Server event logs and in Event viewer..
Kindly check whether you are able to access/open that database in SSMS. If the database is already attached to SQL Server instance and able to connect to that via SSMSE, then you should not face this issue.
Kindly confirm.
0
 

Author Comment

by:eehunter3
Comment Utility
Iwas able to access my database from SQL Server Management Studio; added a new login; viewed data etc. .

The following is the only error log I found in Event Viewer relative to SQL Server:

Log Name:      Application
Source:        MSSQL$SQLEXPRESS
Date:          3/3/2011 12:24:04 PM
Event ID:      15372
Task Category: Logon
Level:         Error
Keywords:      Classic
User:          MEDICOMP\Ellis Hunter
Computer:      MEDICOMP
Description:
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>]
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="MSSQL$SQLEXPRESS" />
    <EventID Qualifiers="49152">15372</EventID>
    <Level>2</Level>
    <Task>4</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2011-03-03T17:24:04.000000000Z" />
    <EventRecordID>15994</EventRecordID>
    <Channel>Application</Channel>
    <Computer>MEDICOMP</Computer>
    <Security UserID="S-1-5-21-683544578-968592212-123672289-1007" />
  </System>
  <EventData>
    <Data> [CLIENT: &lt;local machine&gt;]</Data>
    <Binary>0C3C000010000000140000004D0045004400490043004F004D0050005C00530051004C0045005800500052004500530053000000070000006D00610073007400650072000000</Binary>
  </EventData>
</Event>
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
>> Iwas able to access my database from SQL Server Management Studio; added a new login; viewed data etc. .

Ok, then I think you are trying to attach db in your connection string.
Since your database is already attached to SQL Server, you can simply connect to it..
Can you post your connection string you are using to confirm..
0
 

Author Comment

by:eehunter3
Comment Utility
This is the Connection String:

            <add name="MedicompEMRSQLConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MedicompEMRSQL.mdf;Integrated Security=True;User Instance=false" providerName="System.Data.SqlClient"/>
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Instead of the above connection string, try using this

connectionString="Provider=SQLNCLI;Server=ur_machine_name\SQLEXPRESS;Database=MedicompEMRSQL; Trusted_Connection=yes;"

Just replace ur_machine_name with your machine name to get it work..
0
 

Author Comment

by:eehunter3
Comment Utility
REPLACED

 <add name="MedicompEMRSQLConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MedicompEMRSQL.mdf;Integrated Security=True;User Instance=false" providerName="System.Data.SqlClient"/>

WITH

            <add name="MedicompEMRSQLConnectionString" connectionString="Provider=SQLNCLI;MEDICOMP\SQLEXPRESS;Database=MedicompEMRSQL; Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>

IN web.config ... Same error
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
What is the kind of authentication you are using either Windows or SQL Authentication..
Hope you should receive some other error..
0
 

Author Comment

by:eehunter3
Comment Utility
Windows Authentication.

I even performed a previous instruction you gave me and deleted folders that I found"

Type %Appdata% in Run prompt (Win+R) and go to Microsoft -> Microsoft SQL Server and delete all contents inside it..
'Where else can I look for errors that occur?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Check for any errors in Event viewer related to SQL Server..
Also check for any errors in SQL Server error logs..
0
 

Author Comment

by:eehunter3
Comment Utility
These are the only non-informational messages generated:



Date            3/6/2011 3:33:31 AM
Log            Windows NT (Application)

Source            Report Server Windows Service (SQLEXPRESS)
Category            Management
Event            107
Computer            MEDICOMP

Message
Report Server Windows Service (SQLEXPRESS) cannot connect to the report server database.


Date            3/6/2011 3:33:48 AM
Log            Windows NT (Application)

Source            MSSQL$SQLEXPRESS
Category            Logon
Event            3221240844
User            MEDICOMP\Ellis Hunter
Computer            MEDICOMP

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. [CLIENT: <local machine>]


Date            3/6/2011 3:33:48 AM
Log            Windows NT (Application)

Source            ASP.NET 2.0.50727.0
Category            Web Event
Event            2147484957
Computer            MEDICOMP

Message
Event code: 3005

Event message: An unhandled exception has occurred.

Event time: 3/6/2011 3:33:48 AM

Event time (UTC): 3/6/2011 8:33:48 AM

Event ID: 65b476cef1a844ed817e0aa6ce1adc5e

Event sequence: 6

Event occurrence: 1

Event detail code: 0



Application information:

    Application domain: f04da98f-2-129438740177719456

    Trust level: Full

    Application Virtual Path: /Medicomp_Development

    Application Path: E:\Medicomp_Development\

    Machine name: MEDICOMP



Process information:

    Process ID: 8176

    Process name: WebDev.WebServer.EXE

    Account name: MEDICOMP\Ellis Hunter



Exception information:

    Exception type: SqlException

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



Request information:

    Request URL: http://localhost:51036/Medicomp_Development/login.aspx?ReturnUrl=%2fMedicomp_Development%2fSecure_Admin%2fAdmin_Menu.aspx

    Request path: /Medicomp_Development/login.aspx

    User host address: 127.0.0.1

    User:

    Is authenticated: False

    Authentication Type:

    Thread account name: MEDICOMP\Ellis Hunter



Thread information:

    Thread ID: 10

    Thread account name: MEDICOMP\Ellis Hunter

    Is impersonating: False

    Stack trace:    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 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate)
   at System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation)
   at System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate)
   at System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat)
   at System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved)
   at System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password)
   at System.Web.UI.WebControls.Login.AuthenticateUsingMembershipProvider(AuthenticateEventArgs e)
   at System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e)
   at System.Web.UI.WebControls.Login.AttemptLogin()
   at System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e)
   at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
   at System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e)
   at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
   at System.Web.UI.WebControls.Button.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)


Date            3/6/2011 3:33:48 AM
Log            SQL Server (Current - 3/6/2011 3:33:00 AM)

Source            Logon

Message
Error: 15372, Severity: 16, State: 1.



Date            3/6/2011 3:33:48 AM
Log            SQL Server (Current - 3/6/2011 3:33:00 AM)

Source            Logon

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. [CLIENT: <local machine>]
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
Comment Utility
Try changing the startup account of SQL Server Express to Local System and delete all contents available in the below folder:

C:\Users\[user]\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

Restart your Machine and try again..
0
 

Author Closing Comment

by:eehunter3
Comment Utility
Thanks for your persistence and patience as some of this is quite confusing to me.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
eehunter3,

Can you kindly confirm whether you have resolved your issue or not..
If you have any doubts or clarifications, then kindly let me know so that I can explain those..
0
 

Author Comment

by:eehunter3
Comment Utility
This specific question has been resolved. You suggestions were proper and correct. I have run into other issues regarding transition to Windows 7 but will pose another question to the forum.

Thanks
0
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
Sure..
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now