How do I add ASP.net 2.0 Membership Profile remotely in SQL2005/SQL2000 and then remotely manage ASP.net 2.0 users?

Update 03/2008: Many of our customers who have worked on getting this ASP.net feature to work as their membership management system, have found that much of this is already built into Content Management Systems such as Dotnetnuke and that it is more difficult to get this ASP.net function to work properly. 

DISCUSSION

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)

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. When migrating your application to your hosting account, SQL Server Express is not installed locally on the server and you must add the Application Services Database schema to your SQL2005 / SQL2000 database on our database servers.

This article discusses how to add the Application Services Database schema to your database and then remotely manage the ASP.net Membership roles.

Installing the Database schema 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:WINDOWSMicrosoft.NETFrameworkversionNumber 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.

To run the Aspnet_regsql.exe wizard, run Aspnet_regsql.exe without any command line arguments, as shown in the following example:

C:WINDOWSMicrosoft.NETFrameworkv2.0.50727aspnet_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 -S <database server> -U <database user> -P <database password> -d <database name> -A all

Please note you’ll need to replace the sections in <> with the information for your specific database. If you do not readily have this information available it is accessible from your control panel at https://control.appliedi.net/,

Remotely managing the Membership database

Once you’ve created the Application Database schema in your database you’ll want to remotely manage the membership database. Unfortunately, there is not remote management interface available this task. The best way to manage the membership database is by using Visual Studio or Visual Web Developer.

1. Open your web application in Visual Studio or Visual Web Developer and open the web.config file.

2. The membership provider uses a default connection string named "LocalSqlServer". So we’ll want to edit the section:

<connectionStrings>
….
</connectionStrings>

and add between the <connectionStrings> and </connectionStrings the following two lines:

 <remove name="LocalSqlServer" />
<add name="LocalSqlServer" connectionString="Data Source=[database server];Integrated Security=false;Initial Catalog=[database name];User ID=[database username];Password=[database password]" providerName="System.Data.SqlClient" />

Take care to replace the sections between brackets and the brackets as well.

3. Save your web.config.

4. Select ASP.NET Configuration from the "website" menu at the top of your development studio (either Visual Web Developer or Visual Studio). This will open the ASP.net Website administration tools

5. Select the security tab.

6. click on "Select Authentication type" and select "From the Internet" and click the done button.

You are now able to remotely manage your membership users and roles for your website as well several other application settings that would normally not be available remotely.