ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe)
The ASP.NET SQL Server Registration tool is used to create a Microsoft SQL Server database for use by the SQL Server providers in ASP.NET, or to add or remove options from an existing database. The Aspnet_regsql.exe file is located in the [drive:]\%windir%\Microsoft.NET\Framework\version folder on your Web server.
You can run Aspnet_regsql.exe without any command-line arguments to run a wizard that will walk you through specifying connection information for your SQL Server installation, and installing or removing the database elements for the membership, role management, profile, Web Parts personalization, and health monitoring features. (Setting session state and SQL cache dependency are not covered by the wizard.) You can also run Aspnet_regsql.exe as a command-line tool to specify database elements for individual features to add or remove, using the options listed in the table below.
Aspnet_regsql.exe <options>
SQL Connection Options
| Option | Description |
|---|---|
-? |
Prints Aspnet_regsql.exe Help text in the command window. |
-W |
Runs the tool in wizard mode. This is the default if no command-line arguments are specified. |
-C <connection string> |
Specifies the connection string to the computer running SQL Server where the database will be installed, or is already installed. This option is not necessary if you specify only the server (-S) and login (-U and -P, or -E) information. |
-S <server> |
Specifies the name of the computer running SQL Server where the database will be installed, or is already installed. |
-U <login ID> |
The SQL Server user ID to log in with. This option also requires the password (-P) option. This option is not necessary if you are authenticating using Windows credentials (-E). |
-P <password> |
The SQL Server password to log in with. This option also requires the user ID (-U) option. This option is not necessary if you are authenticating using Windows credentials (-E). |
-E |
Authenticates using the Windows credentials of the currently logged-on user. |
-sqlexportlonly <filename> |
Generates a SQL script file that can be used to add or remove the specified features. The specified actions are not performed. |
Application Services Options
| Option | Description |
|---|---|
-A all|m|r|p|c|w |
Adds support for one or more ASP.NET application services. Service identifiers can be specified together or separately. The following identifiers are used for ASP.NET application services: all - All services, including common tables and stored procedures shared by the services m - Membership r - Role Manager p - Profile c - Web Parts Personalization w - Web Events |
-R all|m|r|p|c|w |
Removes support for one or more application services. Service identifiers can be specified together or separately. The following identifiers are used for ASP.NET application services: all - All services, including common tables and stored procedures shared by the services m - Membership r - Role Manager p - Profile c - Web Parts Personalization w - Web Events |
-Q |
Runs the tool in quiet mode and does not confirm before removing an application service. |
-d <database> |
Specifies the name of the database to create or modify for use with application services. If the database is not specified, the default database name of "aspnetdb" is used. |
Note
The database elements that are installed in the application services database will always be owned by the SQL Server database owner account (dbo). You do not need to be a system administrator for the computer running SQL Server to install the application services database. To install the application services database, a SQL Server login must be permitted to the db_ddladmin and dd_securityadmin roles for the SQL Server database.
SQL Cache Dependency Options (for Microsoft SQL Server 7.0, Microsoft SQL Server 2000 and SQL Server 2005)
| Option | Description |
|---|---|
-d <database> |
Specifies the name of the database to use with SQL cache dependency. The database can optionally be specified using the connection string option, -C. |
-ed |
Enables a database for SQL cache dependency. |
-dd |
Disables a database for SQL cache dependency. |
-et |
Enables a table for SQL cache dependency. The -t option must also be included in the parameter string. |
-dt |
Disables a table for SQL cache dependency. The -t option must also be included in the parameter string. |
-t <table> |
Specifies the table name to enable or disable for use with SQL cache dependency. This option must be used with the -et or -dt options. |
-lt |
Lists all tables enabled for SQL cache dependency. |
Session State Options
| Option | Description |
|---|---|
-d <database> |
Specifies the name of the database to store session state. This option must be used if -sstype is set to c. |
-ssadd |
Adds support for SQL Server mode session state. |
-ssremove |
Removes support for SQL Server mode session state. |
-sstype t|p|c |
Specifies the type of session state to use: t - Temporary. Session state data is stored in the SQL Server tempdb database. Stored procedures for managing session state are installed in the SQL Server ASPState database. Data is not persisted if you restart SQL. This is the default. p - Persisted. Both session state data and stored procedures are stored in the SQL Server ASPState database. c - Custom. Both session state data and stored procedures are stored in a custom database. The database name must be specified using the -d option. |
Remarks
You can set several types of options with the ASP.NET SQL Server Registration tool. You can specify a SQL connection, specify which ASP.NET application services use SQL Server to manage information, indicate which database or table is used for SQL cache dependency, and add or remove support for using SQL Server to store procedures and session state.
Several ASP.NET application services rely on a provider to manage storing and retrieving data from a data source. Each provider is specific to the data source. ASP.NET includes a SQL Server provider for the following ASP.NET features:
Membership (the SqlMembershipProvider class).
Role management (the SqlRoleProvider class).
Profile (the SqlProfileProvider class).
Web Parts personalization (the SqlPersonalizationProvider class).
Web events (the SqlWebEventProvider class).
When you install ASP.NET, the Machine.config file for your server includes configuration elements that specify SQL Server providers for each of the ASP.NET features that rely on a provider. These providers are configured, by default, to connect to a local user instance of SQL Server Express 2005. If you change the default connection string used by the providers, then before you can use any of the ASP.NET features configured in the machine configuration, you must install the SQL Server database and the database elements for your chosen feature using Aspnet_regsql.exe. If the database that you specify with Aspnet_regsql.exe does not already exist (aspnetdb will be the default database if one is not specified on the command line), then the current user must have rights to create databases in SQL Server as well as to create schema elements within a database.
Each SQL Server provider can use the same SQL Server database to store the data for that particular feature. Each feature can be used individually, or in conjunction with other features. For example, you could use role management by itself or in conjunction with the user information managed by membership.
For more information about SQL Server providers and ASP.NET, see Implementing a Membership Provider, Implementing a Profile Provider, and Implementing a Role Provider.
SQL Cache Dependency
An advanced feature of ASP.NET output caching is SQL cache dependency. SQL cache dependency supports two different modes of operation: one that uses an ASP.NET implementation of table polling and a second mode that uses the query notification features of SQL Server 2005. Aspnet_regsql.exe can be used to configure the table-polling mode of operation. SQL cache dependency enables you to cache pages that are dependent on data from SQL Server tables. You can configure SQL Server and ASP.NET to cache page requests, reducing server workload, until the data on which the page depends has been updated in SQL Server. SQL cache dependency is useful for data such as product catalogs or customer registration information that remains comparatively static. Note that you must configure SQL Server to provide proper notification to ASP.NET about changes in dependent data when you use the ASP.NET table-polling mode of SQL cache dependency. Thus, you will need administrative privileges to configure the server. For more information about SQL cache dependency, see Walkthrough: Using ASP.NET Output Caching with SQL Server.
Session State
ASP.NET session state is designed to enable you to easily store user session data in different sources for your ASP.NET applications. By default, session state values and information are stored in memory within the ASP.NET process. Alternatively, you can store session data in a SQL Server database, where it can be shared by multiple Web servers. For more information about session state, see Implementing a Session-State Store Provider and Session-State Modes.
If the database that you specify for session state with Aspnet_regsql.exe does not already exist, then the current user must have rights to create databases in SQL Server as well as to create schema elements within a database. If the database does exist, then the current user must have rights to create schema elements in the existing database.
To install the session state database on SQL Server, run Aspnet_regsql.exe tool supply the following information with the command:
The name of the SQL Server instance, using the -S option.
The logon credentials for an account that has permission to create a database on a computer running SQL Server. Use the -E option to use the currently logged-on user, or use the -U option to specify a user ID along with the -P option to specify a password.
The -ssadd command-line option to add the session state database.
By default, you cannot use Aspnet_regsql.exe to install the session state database on a computer running SQL Server 2005 Express Edition. For more information about using SQL Server 2005 Express Edition to store session state, see Session-State Modes.
Examples
You can run Aspnet_regsql.exe without any command-line arguments to run a wizard that will walk you through specifying connection information for your SQL Server database and installing or removing the database elements for supported features. You can also run Aspnet_regsql.exe as a command-line tool to specify database elements for individual features to add or remove. To specify SQL cache dependency settings or set up session state, you must use the command-line tool.
To run the wizard, run Aspnet_regsql.exe without any command-line arguments, as shown in the following example.
C:\%windir%\Microsoft.NET\Framework\<versionNumber>\aspnet_regsql.exe
The ASP.NET SQL Registration tool is commonly used with the -A or -R option to specify which features use a SQL Server provider. The -A option allows you to add support for one or more features, whereas the -R option allows you to remove a feature. The following command installs the database elements for membership and role management on the local computer running SQL Server using Windows authentication.
aspnet_regsql.exe -E -S localhost -A mr
To set up SQL cache dependency, you need administrative privileges or the administrative account and password. The following command enables SQL cache dependency for the Employees table in the Northwind database.
aspnet_regsql.exe -S <Server> -U <Username> -P <Password> -ed -d Northwind -et -t Employees
The following command creates a database named ASPState on a SQL Server instance named "SampleSqlServer" and specifies that session data is also stored in the ASPState database.
aspnet_regsql.exe -S SampleSqlServer -E -ssadd -sstype p
See Also
Concepts
Implementing a Membership Provider
Implementing a Profile Provider
Implementing a Role Provider