Using SQL Server instead of ASPNETDB.mdf 

Was this helpful?    (43) (8)

When developing applications in ASP.NET 2.0, the default option for roles and users is to use a local SQL database in the App_Data directory.

This works fine locally, but will bring up an error when uploaded to a production server.
To fix this the ASP.NET membership information needs to be stored in a dedicated SQL Server database.

You can create a new Database yourself through hostControl, you can also use any existing content database to store the ASP.NET information.

1. Setting up the database to work with ASP.NET

Option A: If you are starting from scratch you will need to create an SQL Server database and then configure the tables on that database using a utility.

ASP.NET includes a program called Aspnet_regsql.exe which you can run locally to configure your database.
More information on the program can be found here:

http://msdn2.microsoft.com/en-us/library/ms229862.aspx

Option B: If you already have the aspnetdb.mdf file, you can restore this database from hostControl. 


2. Modifying the web.config to support the new membership provider

Setup the connection string in your web.config file something like below.
Remember to change the SQL_SERVER_ADDRESS, DATABASE_NAME, SQL_USERNAME, SQL_PASSWORD to the details you were given.

 <connectionStrings>
<remove name="LocalSqlServer" />
<add name="LocalSqlServer"
    connectionString= "Server=SQL_SERVER_ADDRESS,1433;Database= DATABASE_NAME;User=SQL_USERNAME;Password=SQL_PASSWORD;"
    providerName="System.Data.SqlClient" />
</connectionStrings>

 
3. Membership / Role Provider Settings

Add the following sections within the <system.web> section of the configuration.
Note the "connectionStringName" if you use one different to the one above.

The roleManager section below can be left out if you do not use roles on the site.

    <membership>
        <providers>
            <clear/>
            <add name="AspNetSqlMembershipProvider"
                connectionStringName="LocalSqlServer" 
                applicationName="/"
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
        </providers>
    </membership>

<roleManager enabled="true">
  <providers>
    <clear/>
    <add name="AspNetSqlRoleProvider"
        connectionStringName="LocalSqlServer"
        applicationName="/"
        type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
  </providers>
</roleManager>


4. Done


Once the web.config has been changed and the database setup you can begin to add users and roles by using the ASP.NET admin tool included with Visual Web Developer 2005.