locked
How To get Web Parts to work with SQL Server 2005 (not Express) RRS feed

  • Question

  • User519246680 posted

    One problem that took me a week or so to figure out is using Web Parts.  When you first attempt to use Web Parts, the Framework assumes you are also using SQL Server 2005 Express Edition.  If you do not have Express, you may find yourself lost trying to get anything to work.  This is a how-to guide to getting them to work.  This will work for all versions of SQL Server 2005, as well as SQL Server 2000.

    First, you should know that Web Parts use a Personalization Provider.  This is so users that visit the site get a different perspective, depending on their personalization.  One user might want a red background while another might want blue.  This is their personalization, and it is stored in a database.  Typically, with Express Edition, you would create an MDF file and put it in the App_Data folder of your project's website.  However, other versions of SQL Server 2005 do not support this, so you must rely on a local and attached version of a personalization database.  In order to make this work, there are several things you must do.

    First, create a database.  You may call it anything you wish.  By default, Web Parts looks for a database named 'aspnetdb', so I have created a database on my localhost with that name.

    Next, you will have to register the database as an application database.  In order to do this, you must use the 'aspnet_regsql.exe' wizard.  Browse out to '%windir%\Microsoft.NET\Framework\<framework version>' and run this command.  Once the wizard is up, you may choose to 'Configure SQL Server for application services'.  Select the correct server name (if using a named instance, be sure to include the server and named instance, such as 'ATL53\DEVELOPMENT').  If using SQL Server authentication, put in your username and password.  Select the database that will serve as your personalization database.  When you finish, your database will be set up for membership and personalization.

    *  Note:  For deployed databases, it may not be possible to run the 'aspnet_regsql.exe' wizard.  If you cannot get your hosting provider to run the command for you, or if it has been run, but does not work correctly due to database object ownership issues, there is a workaround.  If you have a local copy of your project, you can run the aspnet_regsql.exe wizard locally, then script export the database, changing the owner (if needed) in the resulting .sql file.  Then simply run the .sql file on your host.

    Finally, you will need to create a connection string to your personalization database.  I put my connection string in the machine.config, but you may not have access to this file.  If not, the web.config for the project will work fine.  The connection string looks like this:

    <connectionStrings>
        <
    add name="LocalSqlServer"
           
    connectionString="data source=ATL53\DEVELOPMENT;Integrated Security=SSPI;Initial Catalog=aspnetdb;User Instance=false"        providerName="System.Data.SqlClient"/>
    </
    connectionStrings>

    The name of the connection string is important, as Web Parts look by default for a connection string named 'LocalSqlServer'.  The data source is equally important and should reflect your database instance (be sure to include the name if using a named instance).  Since I have set up the 'aspnetdb' database as my personalization database, this will be what I use as the Initial Catalog.  Also, since only SQL Server 2005 Express Edition supports User Instances, I will set this property to 'false'.

    That's it!  Run your project and it should be error-free.

    If there are any errors or omissions, please comment.  Thanks.

    Monday, August 13, 2007 1:04 PM

Answers

  • User1501902092 posted

    While I have no idea why there isn't a way in the web.config to specify a connection string that already exists, you can do this in your web.config (someone correct me if this is a bad idea...):

    <remove name="LocalSqlServer"/>
    <add name="LocalSqlServer" <your connection info here> />

    This works for me. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 13, 2007 10:26 PM

All replies

  • User-1668974158 posted

     I've tried this on a fresh project but the the following error: 

    Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.
    Parser Error Message: The entry 'LocalSqlServer' has already been added.

    aspnetdb has been scripted and exists on the local sql 2005 server. If I run the project without the connection string then I just get 'sql server not found'. Seems like the LocalSqlServer may already be defined somewhere but its only defined once in the project web.config.

    Any ideas?

    Saturday, September 1, 2007 6:45 AM
  • User-1668974158 posted

    I had to change the entry in my machine.config. Putting an entry in the web.config for LocalSqlServer throws the error, i guess because its defined twice although I would have expected the web.config to override the machine.config.

    Sunday, September 2, 2007 7:36 PM
  • User1501902092 posted

    While I have no idea why there isn't a way in the web.config to specify a connection string that already exists, you can do this in your web.config (someone correct me if this is a bad idea...):

    <remove name="LocalSqlServer"/>
    <add name="LocalSqlServer" <your connection info here> />

    This works for me. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 13, 2007 10:26 PM
  • User519246680 posted

    Good catch.  Oftentimes, the connection string entry for LocalSqlServer has already been added to either the machine.config, the web.config (under %windir%/Microsoft.net/Framework/<version>/config.  Therefore, you should attempt to remove it first (no error will be thrown if it does not exist) then add it, as stated above.

    By default, the LocalSqlServer connection string is added assuming the developer will use a SQL Server 2005 Express database for each project.  This is why the entry in either machine.config or web.config uses the |datadirectory|/App_Data/aspnetdb.mdf file.

    Tuesday, November 27, 2007 10:16 AM
  • User1435192664 posted

    You can use the Clear tag to clear the connection string from the machine.config e.g

    <connectionStrings>
          <clear/>
            <add name="LocalSqlServer" connectionString="Data Source=.;Initial Catalog=aspnetdb;Integrated Security=True"
                providerName="System.Data.SqlClient" />
        </connectionStrings>


    Tuesday, December 14, 2010 12:32 PM