Creating the Application Services Database for SQL Server
Several ASP.NET features 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 Microsoft 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)
By default, each SQL Server provider stores data in an automatically generated database using a local Microsoft SQL Server Express installation. 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 the membership feature.
Preconfigured SQL Server Providers
When you install ASP.NET, the machine configuration file and the root Web.config file for your server include 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 instance of SQL Server Express. If you plan to use only SQL Server Express with the default location specified in the connectionStrings section of the machine configuration file, then you do not need to perform any manual setup of the database. If you need to configure a database using a standard version of SQL Server running on the same server, or if you need to configure a remote SQL Server computer, then you will need to use this tool.
Note
The Aspnet_regsql tool should not be used with a local installation of SQL Server Express running in user instance mode (that is, the connection string contains User Instance=true).
Installing the Database using Aspnet_regsql.exe
ASP.NET includes a tool for installing the SQL Server database used by the SQL Server providers, named Aspnet_regsql.exe. The Aspnet_regsql.exe tool is located in the drive:\WINDOWS\Microsoft.NET\Framework\versionNumber folder on your Web server. Aspnet_regsql.exe is used to both create the SQL Server database and add or remove options from an existing database.
You can run Aspnet_regsql.exe without any command line arguments to run a wizard that will walk you through specifying connection information for the computer running SQL Server and installing or removing the database elements for all the 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.
Note
The database elements that are installed in the feature database will always be owned by the SQL Server database owner account (dbo). In order to install the feature database, a SQL Server login must be permitted to the db_ddladmin and dd_securityadmin roles for the SQL Server database. However, you do not need to be a system administrator for the SQL Server in order to install the feature database.
To run the Aspnet_regsql.exe wizard, run Aspnet_regsql.exe without any command line arguments, as shown in the following example:
C:\WINDOWS\Microsoft.NET\Framework\<versionNumber>\aspnet_regsql.exe
You can also run the Aspnet_regsql.exe tool as a command-line utility. For example, the following command installs the database elements for membership and role management on the local computer running SQL Server:
aspnet_regsql.exe -E -S localhost -A mr
The following table describes the command line options supported by the Aspnet_regsql.exe tool.
Option |
Description |
|---|---|
-? |
Prints Aspnet_regsql.exe tool 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 |
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 only specify the server (-S) and login (-U and -P, or -E) information. |
-S server |
The name of the computer running SQL Server where the database will be installed, or is already installed. The server name can also include an instance name, such as .\INSTANCENAME. |
-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 login id (-U) option. This option is not necessary if authenticating using Windows credentials (-E). |
-E |
Authenticates using the Windows credentials of the currently logged-in user. |
-d database |
The name of the database to create or modify. If the database is not specified, the default database name of "aspnetdb" is used. |
-sqlexportonly filename |
Generates a SQL script file that can be used to add or remove the specified features. The specified actions are not performed. |
-A all|m|r|p|c|w |
Adds support for one or more features. The following identifiers are used for ASP.NET features.
IdentifierAffects
all All features
m Membership
r Role management
p Profile
c Web Parts personalization
w Web events
Feature identifiers can be specified together or separately, as shown in the following examples. aspnet_regsql.exe -E -S localhost -A mp aspnet_regsql.exe -E -S localhost -A m -A p |
-R all|m|r|p|c|w |
Removes support for one or more features. The following identifiers are used for ASP.NET features.
IdentifierAffects
all All features
m Membership
r Role management
p Profile
c Web Parts personalization
w Web events
Feature identifiers can be specified together or separately, as shown in the following examples. aspnet_regsql.exe -E -S localhost -R mp aspnet_regsql.exe -E -S localhost -R m -R p |
-Q |
Runs the tool in quiet mode and does not confirm before removing a feature. |
See Also
Concepts
Accessing SQL Server from a Web Application
ASP.NET Site Navigation Providers
Other Resources
Creating and Configuring the Application Services Database for SQL Server