Using Web Parts with SQL Server 2000

Many features of ASP.NET rely on intrinsic application services such as membership, roles, profiles, and personalization. The application services use providers, objects that persist application service data in long-term storage. For example, the ASP.NET personalization service uses a personalization provider to save personalized user settings on Web pages.

Each application service uses one provider to persist the service's data in a particular kind of data store. For each service, a SQL provider is included and configured as the default provider to persist the data in a Microsoft SQL Server database.

Note:
To persist ASP.NET application services data in another data store besides SQL Server (such as a Microsoft Access database, XML files, or other RDBMS systems), you must create a separate provider for each kind of data store. To create a custom provider, you can inherit from the base provider for a particular application service, and extend it to handle the data for the service in whatever type of data store you plan to use. For example, to create an Access database provider for the membership service, you could inherit from the MembershipProvider base class, and enable it to persist membership data in Access.

A configured personalization provider and database. Web Parts personalization is enabled by default, and it uses the SQL personalization provider (SqlPersonalizationProvider) with the Microsoft SQL Server Standard Edition to store personalization data. This walkthrough uses SSE and the default SQL provider. If you have SSE installed, no configuration is needed. SSE is available with Microsoft Visual Studio 2005 as an optional part of the installation, or as a free download. For details, see the Microsoft SQL Server 2005 Express Edition Web page. To use one of the full versions of SQL Server, you must install and configure an ASP.NET application services database, and configure the SQL personalization provider to connect to that database. For details, see Creating and Configuring the Application Services Database for SQL Server. You can also create and configure a custom provider to use with other, non-SQL databases or storage solutions. For details and a code example see Implementing a Membership Provider.

Reference : http://msdn2.microsoft.com/en-us/library/2fx93s7w.aspx

By default, Web Parts in ASP.NET 2.0 are configured to use the default SQL Express Personalization Provider for persisting Web Parts information. If you wish to use SQL Server 2000 together with Web Parts, you need to perform the following steps:

1. Run the aspnet_regsql.exe tool located in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50215\ to add the aspnetdb database into SQL Server 2000.

The aspnet_regsql utility is a mixed mode (both graphical- and command-line-based) tool that lets you configure SQL Server for use with your ASP.NET application.

You run the aspnet_regsql tool like this:


C:\WINDOWS\Microsoft.NET\Framework\v2.0.50215\>aspnet_regsql
If you invoke aspnet_regsql without any command options, you will see a wizard guiding you through the steps. Select the "Configure SQL Server for application services" option in the wizard window. Click Next and then specify your SQL Server 2000 name. Click Next and then Close.

2. In Web.config, add the following to change the default SQL Express Personalization Provider to the new SQL Server Personalization Provider:

Note: In the ConnectionString section user name and password can be changed accordingly.

< connectionStrings >
< add name="SQLConnString"
connectionString="Data Source=your_server_name;Initial
Catalog=aspnetdb;Integrated Security=false;uid=sa;pwd=kkak"
providerName="System.Data.SqlClient" / >
< /connectionStrings >

< system.web >
..
..
< webParts >
< personalization
defaultProvider="SqlPersonalizationProvider" >
< providers>
< add name="SqlPersonalizationProvider"
type="System.Web.UI.WebControls.
WebParts.SqlPersonalizationProvider"
connectionStringName="SQLConnString"
applicationName="/" / >
< /providers >
< authorization >
< deny users="*" verbs="enterSharedScope" / >
< allow users="*" verbs="modifyState" / >
< /authorization >
< /personalization >
< /webParts >
< /system.web >

The Web Parts information will now be stored in SQL Server 2000 rather than the ASPNETDB.MDF database.