Solved

Error opening sqlexpress db

Posted on 2011-02-26
45
1,725 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
ID: 34987076
0
 

Author Comment

by:eehunter3
ID: 34987108
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
ID: 34987299
In windows explorer, ensure you have the folder options set to view hidden files and folders.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

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

Author Comment

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

Expert Comment

by:Raja Jegan R
ID: 34987926
>> 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
ID: 34987974
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
ID: 34988147
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
ID: 34988881
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
ID: 34996639

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
ID: 34997079
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
ID: 34997283
have you applied sql server SP1
0
 
LVL 3

Expert Comment

by:KetGuru
ID: 34997396
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
ID: 34997430
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
ID: 34997593
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
ID: 34997642
aha. Yes please get rid of SQL Server 2008.
0
 

Author Comment

by:eehunter3
ID: 34997931
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
ID: 34997949
First 3 only
0
 

Author Comment

by:eehunter3
ID: 34998816
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
ID: 34998847
confuse now. what is windows 7 32 bit or 64 bit ?
0
 

Author Comment

by:eehunter3
ID: 34998894
64-bit
0
 
LVL 3

Expert Comment

by:KetGuru
ID: 34999036
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
 

Author Comment

by:eehunter3
ID: 34999915
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
ID: 35023478
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
ID: 35023823
Apply SP4 for SQL Server 2005 and try once..
0
 

Author Comment

by:eehunter3
ID: 35026742
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
ID: 35027009
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
ID: 35027293
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
ID: 35027450
>> 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
ID: 35029185
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
ID: 35032962
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
ID: 35033213
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
ID: 35033342
>> 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
ID: 35033651
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
ID: 35034329
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
ID: 35034521
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
ID: 35035702
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
ID: 35037623
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
ID: 35037961
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
ID: 35045239
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
ID: 35046144
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
ID: 35046741
Thanks for your persistence and patience as some of this is quite confusing to me.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35051376
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
ID: 35056379
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
ID: 35056604
Sure..
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

856 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