ASP.NET: Set Up SQL Server for ASP.NET Provider Databases

Out of the box the ASP.NET provider databases works with the local database file call aspnetdb.mdf, however in a production environment you cannot use this file because you need to work with the production database. To create the provider databases on a full version of SQL Server perform the following steps.

1. Navigate to the .NET Framework version that you want to use. Version 4.5 and Version 2 has the provider database wizard. I am using version 4.5 so the path is C:\Windows\Microsoft.NET\Framework\v4.0.30319

2. Double click on the file aspnet_regsql.exe, the ASP.NET SQL Server Setup Wizard will apear, click "Next" to continue






3.  Select "Configure SQL Server for application services" radio button, then click next


4. Type in the database server name in the "Server:" field.  Make sure you type in the actual host name because if you type in (local) or localhost the database creation process will fail.   Leave the option as "Windows authentication" checked, unless you want to use SQL Server authentication.  Then click "Next" to continue.


5.  Click next on the confirmation screen



Now you have the databases necessary to work with the ASPNET provider services.  However, you still need to change the configuration file maching.config in the .NET folder in order for .NET to recognize that the database is available in the SQL Server database now or the web.config file in your web application.  The best practice is to change it in your web application so that it only affects your web application.  If you change the configuration in machine.config file you will change to for all the applications.

This example shows you how to use the AspNetSqlMembershipProvider

1. First add the connection string to the database, make sure you have the <clear/> tag right below the <connectionStrings> opening tag

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

2. Now above the </system.web> tag type in the following

       <membership defaultProvider ="AspNetSqlMembershipProvider">
          <providers>
            <add name="AspNetSqlProfileProvider" connectionStringName="aspnetdbConnectionString" 
applicationName="/" type="System.Web.Profile.SqlProfileProvider, 
System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
          </providers> 
      </membership>

Note: To make sure you have the right provider settings copy the declaration from your machine.config file and then just change the "connectionStringName" attribute.