Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> SQL Server Express

Sql Configuration Problems (Desperately in need of help)

Hi

I am currently working on my final year project for uni using vs 2005, sql server express and xp pro sp2.

Basically I created a website in asp.net and created a database in the app_data directory.

Everything was working fine up until last week until I downloaded sql server management studio. I was experimenting with the IDE and attached the database from the app_data directory of the website to the sqlserver management studio.

Ever since I havent been able to connect to the database and am very worried about it as I have spent a week tearing my hair out to no avail.

The error I am getting is:

Server Error in '/WebSite/Iftams Project' Application.

Cannot open user default database. Login failed.
Login failed for user 'RODNEY-DESK\ASPNET'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
Login failed for user 'RODNEY-DESK\ASPNET'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Cannot open user default database. Login failed. Login failed for user 'RODNEY-DESK\ASPNET'.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734963 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197 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) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.ImageButton.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +171 System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

I believe that the connection string in my web.config is looking for the database in the app_data folder however the default database (whatever this means) is not set to the database I have or else the aspnet user is not allowed to see the database because ssms has corrupted the settings.

Please help!

Regards

Rodney

[6140 byte] By [newbievsprogrammer] at [2008-2-5]
«« Syntax of VS 2005 autoexp.dat [Visualizers] section
»» New SQL Express Installation
# 1

Hi Rodney,

I'm trying to track down an answer for you. Clearly something to do with the permissions for the ASPNET user, but I'm more of a client guy so I'm asking some web guys.

Mike

MikeWachal-MSFT at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Server Express...
# 2

Hi Rodney,

Check the permissions on the database your are trying to connect to with your application. The short story is that if the ASPNET user doesn't have permissions in the database (i.e. Login to SQL and User in the database) you won't be able to open the database.

By default, all ASP.NET web sites run under the ASPNET account, so granting access in your database to the ASPNET user will effectively give every web app that uses this account access to your database. This is likely not the best solution. The web app team recommends two different ways to solve this:

  • Use Application Pools in IIS 6.0 to run each application as a different user and then grant database permissions to the approrpriate users for the application.
  • Use Mixed Mode authentication and create SQL Users to grant permissions to specific databases.

Hope this helps,

Mike Wachal - SQL Express team

MikeWachal-MSFT at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Server Express...
# 3

Mike

I think i found out the problem although I took a different route. It was either one of two things:

1) I messed up the configuration of SQL Server

2) Using Management Studio attached the DB and wouldnt let go of it even when it was closed as the process was still running in the background.

I think option 2 was the problem because I was talking to a friend of mine and he said that when Visual Web Developer is open along with Management Studio that managment studio opens the DB and keeps it open / attached even when you close it down as it showed in the process list. That meant that I could not get into the database under aspnet account when I tried to run the application as it was already open through managment studio even though the interface of management studio was closed down!

Im sorry if it doesnt make much sense but I am still not quite sure what was causing the problem.

Anyway i solved it by having to reinstall sql server express. Before doing this I would recommend you use ctrl+alt+del and end the managment studio app process to see if this solves the problem as it only started to appear when I attached a database to management studio. it was working fine before this.

Regards

Rodney

newbievsprogrammer at 2007-9-8 > top of Msdn Tech,SQL Server,SQL Server Express...
SQL Server
SQL Server Express

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified