HELP: ASP.NET won''t connect to SQLexpress after deploying to IIS, try a lot of things...

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!

These two links will take you in the right direction but you still have the ASPNETDB as a User Instance database which is not supported in other editions of SQL Server so you need to correct that also. In Asp.net deployment takes careful planning or any part of the application can stop the deployment. Hope this helps.

http://geekswithblogs.net/ranganh/archive/2005/04/25/37613.aspx

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

|||

Caddre wrote:

These two links will take you in the right direction but you still have the ASPNETDB as a User Instance database which is not supported in other editions of SQL Server so you need to correct that also. In Asp.net deployment takes careful planning or any part of the application can stop the deployment. Hope this helps.

http://geekswithblogs.net/ranganh/archive/2005/04/25/37613.aspx

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

Thanks! This is the answer I needed!

|||

I am glad I could help.