Misfit Geek

ScottGu forwarded me an email from a developer this weekend who wanted to use ASP.NET Membership in an application deployed on a shared hosting account that allows only one SQL Server database.

It’s not all that difficult to add ASP.NET membership (as well as other ASP.NET services) to your existing database.

ASP.NET doesn’t really care where the information repository for it’s built in services live as long as they are complete.

The database that contains the ASP.NET Application Services repository is resolved via a standard connection string in the application’s web.config file.

   1:     <connectionStrings>

   2:      <add name="ApplicationServices"

   3:           connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;
AttachDBFilename=|DataDirectory|\aspnetdb.mdf;
User Instance=true"

   4:           providerName="System.Data.SqlClient" />

   5:    </connectionStrings>

In the entry above the connection points to the a default database of aspnetdb.mdf in the local application’s App_Data directory.

Note that the default is determined by settings in the machine.config file. If you have run the aspnet_regsql.exe utility on your developer machine to configure a SQL Server database to house the membership / ASP.NET Application Services tables than your machine.config defaults probably point to a SQL Server instance instead of a SQL Express database.

In any event, you can use a connection sting like the one above and simply change the .mdf file name to the one that you need to use in your App_Data folder.

If you are doing this on your shared host that you would specify the connection string to your hosting provider’s database and they would have provided you with the necessary connection information when you signed up for the account.

Once our application can connect to the database we’ve chosen to house out membership information we need to configure our database for use by the Membership Provider.

The ASP.NET installation provides some .SQL script files that we can use to make this process easier.

In Windows Explorer, navigate to your .NET Framework install directory.

On my development machine it was located here.

C:\Windows\Microsoft.NET\Framework\v4.0.30319

Note the highlighted .SQL files in the figure below

Though all may be useful to you in setting up the various ASP.NET Application Services, for the purposes of this post, we’re only going to set up the ASP.NET Membership service.

We will need the first two .sql files…..

InstallCommon.sql

InstallMembership.sql

Make separate copies of these as we’ll need to edit them before we run them.

Next you need to determine how you will execute T-SQL against your SQL Server instance.

If you are working on your own machine you can simply run SQL Server Management Studio on your machine (or against your SQL Server instance.)

If you’re host does not support direct connections with SQL Server Management Studio then they will likely have provided some web based administration mechanism and you will need to use that to execute the T-SQL.

In my sample application I created an empty SQL Express database named AddedMembershipDemo.mdf

First open the copy of InstallCommon.sql  that you made.

You will have to make a couple of edits to the code. You will note several references to the database name “”aspnetdb”……

Here are a few examples:

SET @dbname = N’aspnetdb’

USE [aspnetdb]
GO

You must change all references to “aspnetdb” to the name of the database that you are adding the ASP.NET Application Services to.

Since the database already exists you will want to comment out the code bock that looks like this:

   1: IF (NOT EXISTS (SELECT name

   2:                 FROM master.dbo.sysdatabases

   3:                 WHERE name = @dbname))

   4: BEGIN

   5:   PRINT 'Creating the ' + @dbname + ' database...'

   6:   DECLARE @cmd nvarchar(500)

   7:   SET @cmd = 'CREATE DATABASE [' + @dbname + '] ' + @dboptions

   8:   EXEC(@cmd)

   9: END

  10: GO

Once you have made those changes you should be able to run the resulting script successfully (though you may get a warning about some permission assignments.)

Next open InstallMembership.sql and change the references to aspnetdb to whatever your new database name is and run that script as well.

On success you’re database will contain Tables, Views, Stored Procedures, etc like this.

If you started your application by creating from the default ASP.NET application template, your web.config file should already have the configuration section for ASP.NET Membership. If not you will need to add it manually as below.

   1: <membership>

   2:   <providers>

   3:     <clear/>

   4:     <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices"

   5:          enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false"

   6:          maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10"

   7:          applicationName="/" />

   8:   </providers>

   9: </membership>

Now you should be able to open your site and start adding users to the membership repository.

Similar Posts:

None Found