locked
How to connect a local database to a web developer 2010 express project on a network drive? RRS feed

  • Question

  • I am a newbie with this product.  I downloaded Visual Web Developer 2010 Express which is running on a network drive.  I opened a default ASPX web site with this tool.  I also download SQL server 2008R2 Express which is running on my local C: drive.  It has a default database called 'Master'.   I am running Windows7 pro 32 bit and IIS 6.0

    This default web site has Database connection with a login script.   When I run this login script I got an 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.

    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.

    SQLExpress database file auto-creation error:

    The connection string specifies a local Sql Server Express instance using a database location within the application's App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:

    1. If the application is running on either Windows 7 or Windows Server 2008R2, special configuration steps are necessary to enable automatic creation of the provider database. Additional information is available at: http://go.microsoft.com/fwlink/?LinkId=160102. If the application's App_Data directory does not already exist, the web server account must have read and write access to the application's directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist.
    2. If the application's App_Data directory already exists, the web server account only requires read and write access to the application's App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the application's App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server account's credentials are used when creating the new database.
    3. Sql Server Express must be installed on the machine.
    4. The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts

     

    My Web.config file had the following:

    <connectionStrings>

        <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
      </connectionStrings>

     

    I tried the above solution http://go.microsoft.com/fwlink/?LinkId=160102 and this changed nothing.

     

    I then changed my Web.config file to the following:

    <add name="MyDbConn1"  
             connectionString="Server=RMOLTZWIN7\SQLEXPRESS; Database=C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\MASTER.MDF; Trusted_Connection=Yes;"/>
        </connectionStrings>

    I know get the following error:

     

    Cannot open database "C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\MASTER.MDF" requested by the login. The login failed.
    Login failed for user

    Where to go from here?

    Tuesday, May 18, 2010 9:08 PM

Answers

  • I don't know if the database was populated with the proper tables by aspnet_regsql tool, but would sure hope so.

    I think my comments about Trusted Connection might be a little bit confusing - you need to have some authentication mechanism in your connection string. So either use the Trusted Connection or user and password properties. In both cases make sure that the user has permissions to modify the data in the database.

    You may want to read more on connection strings in general - you could start on the following site:

    http://www.connectionstrings.com/sql-server-2008#p1

    This article looks like a good review of SQL Server security model:

    http://www.developer.com/db/article.php/721441/Microsoft-SQL-Servers-Security-Model.htm

    Good luck :-)


    Krzysztof Kozielczyk, Program Manager for SQL Server Express

    If this post answers your question, please mark it as an Answer - it will help others searching the forum. This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by elkhunta Thursday, May 20, 2010 12:47 PM
    Wednesday, May 19, 2010 11:25 PM

All replies

  • If you don't have a hard requirement that the aspnetdb.mdf must be in App_Data folder, I would suggest you just use the SQL Server Expres instance directly, as described in this article (use ".\SQLEXPRESS" as SQL_SERVER_ADDRESS ):

    http://www.studiocoast.com.au/knowledgebase/article-6-aspnet-using-sql-server-instead-of-aspnetdbmdf.aspx

    This way you won't be bothered by the User Instance problems.

     

    If you absolutely must have the aspnetdb.mdf in App_Data folder, we would need to have a little bit more data. First question would be if you are able to open this database from Visual Web Developer (just double click it in the Solution Explorer).

     


    Krzysztof Kozielczyk, Program Manager for SQL Server Express

    If this post answers your question, please mark it as an Answer - it will help others searching the forum. This posting is provided "AS IS" with no warranties, and confers no rights.
    • Edited by Krzysztof Kozielczyk Tuesday, May 18, 2010 9:39 PM Commented on the proper value for SQL_SERVER_ADDRESS
    Tuesday, May 18, 2010 9:38 PM
  • This helps some.   I created a new database named "Login" using the Aspnet_regsql tool.  I am able to see and connect to it in Visual Web Developer(Data Connections).

    Web Config file:

    <connectionStrings>
        <remove name="LocalSqlServer" />
        <add name="LocalSqlServer"
                               connectionString= "Server=RMOLTZWIN7\SQLEXPRESS,1433;
                               Database= C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Login.MDF;
                               Trusted_Connection=True;"/>
        </connectionStrings>

    When I run the application in Visual Web Developer I get an error that I cannot connect to the server:

    Exception Details: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)


    Where to correct this connection issue?  In SQL Server Management Studio?  I verified the port 1433 and windows firewall were not the issue. 

     

     

    Wednesday, May 19, 2010 5:07 PM
  • You're almost there. Just change the Database parameter to "Login", like that:

    <connectionStrings>
        <remove name="LocalSqlServer" />
        <add name="LocalSqlServer"
                               connectionString= "Server=RMOLTZWIN7\SQLEXPRESS,1433;
                               Database=Login ;
                               Trusted_Connection=True;"/>
        </connectionStrings>

    This is not a User Instance connection string, so you don't point to the database file, you must address the database with the name you gave it when it was created with the aspnet_regsql tool.

    Let us know if it worked!

     

    One thing that might be a problem in the future is that you are using Trusted Connection. This assumes that the account IIS runs under has modify permissions to the 'Login' database inside SQL Server. It may be true today, because IIS and SQL Server run as the same user (I suspect Network Service). There's nothing wrong with using Trusted Connections, just make sure the account that the IIS is using to run your site has permissions to read and modify the data in 'Login" database.

     


    Krzysztof Kozielczyk, Program Manager for SQL Server Express

    If this post answers your question, please mark it as an Answer - it will help others searching the forum. This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, May 19, 2010 5:36 PM
  • No change after:

      <connectionStrings>
               <remove name="LocalSqlServer" />
               <add name="LocalSqlServer"
                               connectionString= "Server=RMOLTZWIN7\SQLEXPRESS,1433;
                               Database=Login;"/>
        </connectionStrings>

    One thought, When I created the DB "Login",  do I need to explicitly create tables with correct columns and rows in the DB to match the login ASPX code?  Or does Visual Web Developer create those dynamically?

    Wednesday, May 19, 2010 8:35 PM
  • I don't know if the database was populated with the proper tables by aspnet_regsql tool, but would sure hope so.

    I think my comments about Trusted Connection might be a little bit confusing - you need to have some authentication mechanism in your connection string. So either use the Trusted Connection or user and password properties. In both cases make sure that the user has permissions to modify the data in the database.

    You may want to read more on connection strings in general - you could start on the following site:

    http://www.connectionstrings.com/sql-server-2008#p1

    This article looks like a good review of SQL Server security model:

    http://www.developer.com/db/article.php/721441/Microsoft-SQL-Servers-Security-Model.htm

    Good luck :-)


    Krzysztof Kozielczyk, Program Manager for SQL Server Express

    If this post answers your question, please mark it as an Answer - it will help others searching the forum. This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by elkhunta Thursday, May 20, 2010 12:47 PM
    Wednesday, May 19, 2010 11:25 PM