Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Express Edition and Compact Edition (2005)
 HELP: ASP.NET won't connect to SQLexpress after de

Author  Topic 

MINGZCAN
Starting Member

9 Posts
Posted - 2007-07-31 : 18:16:21
Hello,

I have finish building my ASP.NET site using VS 2005 and it compile just fine. Than I try hosting it to the web via IIS v5.1 and I am getting permission error from SQL express.

I have posted a detail question in experts-exchange.com and since you need a user account to even view the thread, I have taken the liberty to save the thread as .html file and host it to my ISP site. You can see the detail problem I am having here: http://users.accesscomm.ca/mm/EEdetail.html

I am going to list out some stuff I have done:

1. I have two database that need access, the ASPNETDB.mdf for user login and another database (PhotoDataBase.mdf) to store information relating to the photo I uploaded to my ASP.NET site.

2. I have already follow this How to site: http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx and setup:
- TCP/IP enable, as shown here:
- I have started SQL Browser service
- The firewall part I am not so sure about. I am currently using Zonealarm....

3. I have installed SQL Server Management Studio Express(SSMSE) and attach the two database to it.

4. I have set SSMSE server properties to accept both SQL and window authentication.

5. I have made sure that both database "read only" setting to false

6. I have setup SQL login with User ID = SQLLOGIN

Here is the original connection in web.config:
<add name="ConnectionString"
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|Da
taDirectory|\PhotoDataBase.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />

and here is the new connection I just type in web.config that replace the original one:
<add name="ConnectionString"
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PhotoDataBase.mdf; Server=MINGDESKTOP; Integrated Security=False; uid=SQLLOGIN; Password=XXXXXX;"
providerName="System.Data.SqlClient" />

and the error i get are the follow:

Server Error in '/' Application.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
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: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

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.

7. I did haven't done much to ASPNETDB.mdf yet and the error i get right now are the following:

________________________________________
__________________
Server Error in '/' Application.
Cannot open user default database. Login failed.
Login failed for user 'MINGDESKTOP\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 'MINGDESKTOP\ASPNET'.
_______________________________________________


Please help! I am really running out of ideas.... and I need to have this setup in a few days....

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-07-31 : 18:50:38
Your connection string is incorrect. Get rid of Server and put the correct information in Data Source. Do not use attachdbfilename. That option is not for SQL Server. Never refer to file names in connection strings for SQL Server. The engine knows where the files are located, so you just need to connect to the server. I'm very surprised that your original connection string even worked with the file name information in it. You'll also need to add Initial Catalog so that it knows which database to go to.

Check out www.connectionstrings.com for valid connection strings.

You'll need to fix the default database for the MINGDESKTOP\ASPNET account too. Double click on this login in SSMS in the Logins portion. Switch it to whichever database you want it to be defaulted too. When connecting to SSMS, connect with sa or a sysadmin account so that it has the permissions to fix the ASPNET account.

And don't refer to the names of the files (ASPNETDB.mdf). Don't worry about that at all. Just tell us that the database name is ASPNETDB. You will never touch the mdf file when designing your database. The only time you care about the file name is when you want to move it.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

MINGZCAN
Starting Member

9 Posts
Posted - 2007-07-31 : 23:18:39
Hello Tara,

You came and save the day!!! After change my web.config to the following, I was able to connect with PhotoDataBase.mdf.

<add name="ConnectionString"
connectionString="Data Source=MINGDESKTOP\SQLEXPRESS; User ID=SQLLOGIN; Password=XXXXXXXX;" />

Now I am able to browse and upload without error!

Now witht he ASPNETDB, still having problem with that...

In SSMS, I have change the password associated with the "sa" account. As well, I change the web.config file to the following:

<add name="ConnectionString"
connectionString="Data Source=MINGDESKTOP\SQLEXPRESS; User ID=sa; Password=XXXXXXXX;" />

The error i get are:
Server Error in '/' Application.
Cannot open user default database. Login failed.
Login failed for user 'MINGDESKTOP\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 'MINGDESKTOP\ASPNET'.
_______________________________________________

Than I realize in the "sa" account properties, the "user mapping" to access both ASPNETDB.mdf and PhotoDataBase.mdf wasn't selected, so I try select both database but only to get this error message: http://farm2.static.flickr.com/1110/968724805_98865739f0_o.jpg

I also try setting SQLLOGIN server role to be systemadmin option, but same thing, couldn't login to ASPNETDB...

You did mention about "And don't refer to the names of the files (ASPNETDB.mdf). Don't worry about that at all. Just tell us that the database name is ASPNETDB. You will never touch the mdf file when designing your database. The only time you care about the file name is when you want to move it."

I am not sure what you mean by that... Where should I change the database name to ASPNETDB and not ASPNETDB.mdf?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-08-01 : 01:51:45
You don't need to change anything for the ASPNETDB versus the file name. My point is that in your posts, you need to refer to the database and not the file name. The file name has nothing to do with this. As a developer, you'll never need to know the file name.

You still need to fix the default database as indicated by your error message. I gave brief instructions on this in my last post.

You also didn't need to check anything for sa in user mapping as sa is sysadmin and therefore has access to absolutely everything in SQL Server. sa is God.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

MINGZCAN
Starting Member

9 Posts
Posted - 2007-08-02 : 18:01:05
I got hang up at work and now back to it...

Anyways, I have set my sa account to default database to PhotoDataBase.mdf (not ASPNETDB.MDF) and it just doesn't seem to allow me to connect to ASPNETDB. Here are some screen shoot of the sa account configuration in SSMSS:
http://farm2.static.flickr.com/1125/989244911_52772150c2_o.jpg
http://farm2.static.flickr.com/1030/989244783_271da21222_o.jpg
^^Perhaps you can spot something that is an error in my sa account

One more question, does it matter if I had my two database outside the System Database? will that affect anything?
You can see what I mean here: http://farm2.static.flickr.com/1073/989205111_95d7951cd0_o.jpg

and the connection string is just like before:
<add name="ConnectionString"
connectionString="Data Source=MINGDESKTOP\SQLEXPRESS; User ID=sa; Password=XXXXXX;" />

^^I was able to connect to PhotoDataBase.mdf using the above connection string, but not to ASPNETDB.mdf.

thx!
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts
Posted - 2007-08-02 : 23:25:41
SSMS places user dbs separate from system dbs, will not affect anything. But why you name your db that way?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-08-03 : 11:20:56
Your database names need to be changed. They should just say ASPNETDB, not D:\...\ASPNETDB.MDF. You don't specify the path or the filename in the database name. You specify that information in the properties when you are creating the database.

Your default database should just say master for the sa account.

Stop using paths and filenames for database names!

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

MINGZCAN
Starting Member

9 Posts
Posted - 2007-08-03 : 14:01:49
I have no rename database to ASPNETDB and PhotoDataBase, as shown here: http://farm2.static.flickr.com/1305/998432547_dffd8deb37_o.jpg

My current connection string remain the same:
<add name="ConnectionString"
connectionString="Data Source=MINGDESKTOP\SQLEXPRESS; User ID=sa; Password=XXXXXX;" />

The problem seem to be this:
1. If I set the default database for sa to PhotoDatabase, it will connect to PhotodataBase fine but not ASPNETDB.
2. If I set the default database for sa to Master, I couldn't connect to both PhotodataBase or ASPNETDB.
3. If I set the default database for sa to ASPNETDB, I couldn't connect to both PhotodataBase or ASPNETDB.

It look like the sa account isn't realy the system admin account at all, could login to Photodatabase without pointing it's default database to it. Perhaps something is configure incorrectly within sa. The following are some screenshoot of sa account:
http://farm2.static.flickr.com/1426/998432479_a610f9c3e0_o.jpg
http://farm2.static.flickr.com/1065/998432513_66171415b6_o.jpg
http://farm2.static.flickr.com/1078/998432527_e266702060_o.jpg
http://farm2.static.flickr.com/1214/998432537_f566dea131_o.jpg
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-08-03 : 14:06:40
You need to specify Initial Catalog in your connection strings as I mentioned before. You are going to need two connection strings for your application. One for each database you want to connect to.

Set the default database for sa to master. Do not change this! You just need to fix your config to get everything to work.

sa is configured correctly as sysadmin. It has access to all databases as it is God! Do not modify anything for sa account anymore.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts
Posted - 2007-08-03 : 14:07:32
It is and you shouldn't use it in any app. By the way, you can add db name in your connection string.
Go to Top of Page

MINGZCAN
Starting Member

9 Posts
Posted - 2007-08-03 : 15:01:03
Thank you guys! i am really new at this, I will try it right now!
Go to Top of Page

MINGZCAN
Starting Member

9 Posts
Posted - 2007-08-03 : 15:37:00
Here my updated web.config file:
<add name="PhotoDataBase"
connectionString="Data Source=MINGDESKTOP\SQLEXPRESS; Initial Catalog=PhotoDataBase;User ID=SQLLOGIN; Password=XXXXX;" />

<add name="ASPNETDB"
connectionString="Data Source=MINGDESKTOP\SQLEXPRESS; Initial Catalog=ASPNETDB; User ID=sa; Password=XXXXXX;" />

In the .aspx that i need to connect with PhotoDataBase, I also change to the following:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PhotoDataBase %>"
SelectCommand="SELECT [PictureID], [UploadedByMemberName], [Notes], [PictureURL] FROM [Gallery] ORDER BY [PictureID] DESC">
</asp:SqlDataSource>

When I change my connection for ASPNETDB.NET to ASPNETDB... Do I need to change the ConnectionString where I call it? Something like the above code... I am not sure where it is located...

Also, I have set the sa default database back to Master.

Still unable to connect to ASPNETDB...
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-08-03 : 15:45:27
What is the error?

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

MINGZCAN
Starting Member

9 Posts
Posted - 2007-08-03 : 16:23:49
Same as before:

Server Error in '/' Application.
Cannot open user default database. Login failed.
Login failed for user 'MINGDESKTOP\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 'MINGDESKTOP\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 'MINGDESKTOP\ASPNET'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
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) +359
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.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.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.832; ASP.NET Version:2.0.50727.832
Go to Top of Page

MINGZCAN
Starting Member

9 Posts
Posted - 2007-08-03 : 18:33:09
Is all working now!!!!!!! I am so happy!

The problem was due to ASPNETDB itself, not any configuration problems.

The problem is discussed here: http://weblogs.asp.net/scottgu/archive/2006/04/22/Always-set-the-_2200_applicationName_2200_-property-when-configuring-ASP.NET-2.0-Membership-and-other-Providers.aspx

Thank you for everyone's help! Especially Tara!

I think I gain alot from this :)
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts
Posted - 2007-08-03 : 23:29:29
I think the problem is sql login 'MINGDESKTOP\ASPNET' based on error message.
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2007-08-04 : 08:34:23
FYI Tara and others --

When using SQL Express, you can specify the filename of the MDF file you wish to use. It kind of "dynamically" attaches the MDF file to the server. That's how things work when you use the App_Data folder in an asp.net 2.0 application. It can be handy since all of your code and your data is in one folder and is nice and portable during development.

When you deploy, you can leave the MDF file there (if you are using SQL Express) and reference it by name, or you can formally attach the MDF like normal to SQL Express/Server and reference it through the usual combination of server and databasename.

A little more here: http://msdn2.microsoft.com/en-us/library/ms165716.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts
Posted - 2007-08-04 : 20:32:49
Glad to know, thanks for the info.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site