Posted in:

One of the first things I tried to get my head around while learning ASP.NET 2.0 recently was the user and membership system. Its a nice idea - Visual Studio can go off and create a database for you with all the tables ready to go. It certainly saves a lot of design and implementation time by providing a solution for a common problem.

The only strange thing is where it puts this data. As far as I can tell, it never asks you where you want to store the data. It simply creates a new SQL Express file called ASPNETDB.MDF. In fact, it uses a connection string that is hidden away in your machine.config file, which I found quite confusing at first, as I couldn't work out how my web application knew how to connect to this database.

The trouble with this is obvious. If you already have a database, then you now have two databases. This not only is a problem for those whose web hosting provider charges them for multiple databases, but more seriously doesn't allow you to have a foreign key in one of your data tables pointing at the user table.

Now you might say that I am making a fuss about nothing. Surely I can just move these tables into my existing database. And I probably will. But I noticed that the SubSonic starter site has its data still separated into two databases. Why is this? The CMS database simply stores "user name" and has no hard link to the ASPNETDB database. Of course, you could write some code to look up user details (such as display name, email etc) from the user name, but you have lost the ability to get all your data with one request. What's worse, if a user is allowed to change their user name, all relationships will be broken.

So my question is, why are people doing this? Is there any benefit to having the databases separate? Or is it just because that's what Visual Studio does by default and it is a pain to change?

(not that I'm expecting an answer. I don't think this blog actually has any readers yet!)

Comments

Comment by Anonymous

I support your idea. I think this is a shortcoming of Visual Studio. It makes things complecated.

Anonymous
Comment by Heath

In regards to integrating membership into an existing SQL database this is an inherent part of the membership design. There is a handy tool made available: %windir%\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe (where v2.0.50727 is the version of .NET you are using) that will run you through a wizard that allows you to plug in the information for the SQL server you wish to specify. This creates the aspnetdb database on the server and prepares the table for use.

To prepare your application for use with this setup, setup a connection string that will connect to your SQL server with aspnetdb as its initial catalog. With your database setup and your connection string to the database ready all you need to do now is notify your application you will be using an independent datasource for storing this information by specifying a Membership Provider in the < system.web > portion of your web.config file. A basic membership provider looks like this:

< membership defaultProvider="samplemembership" >
< providers >
< add name="samplemembership" type="System.Web.Security.SqlMembershipProvider" connectionStringName="LocalSqlServer" applicationName="/"/ >
< /providers >
< /membership >

Make sure to specify your application name, else when you deploy it you could see some errors or the entire login system not work. There are a lot of fields in the membership provider that are very useful. Things that specify password length and complexity. Set the requirement of unique email addresses, etc.

There is a whole lot under the hood and the entire system is very configurable. It's also very complex and confusing, so try not to get discouraged if it bucks during the course of development.

Good luck!

Comment by yyilmaz

Hi;
I read and found you succesfull.
Keep on writing
Regards from Türkiye

Comment by Thomas

Hi,

I now have a better understanding of how Visual Studio implements usernames and password for .asp applications.

I have a small question, i have locked myself out and was wondering how i can reset the usernames/password or get back in?

Comment by colinkng

Thanks for the original article, and for Heath's helpful instructions. This solved my problem!

Comment by Anonymous

You cannot unlock the account by code (could be wrong on this).
It has to be done through "ASP.NET Configuration" also reffered as "Web Site Administration Tool" (the online system where you make user and roles).
----------------
Original blog is talking about "Membership Provider".
by Default it makes a seperate ASPNETDB.MDF , you can integrate it into the existing one if you want. Google for step by step instructions.
Beauty of ASP2.0 is that doesn't matter how it is setup (in web.config) file, you access it the exact same way and it will know automatically (through web.config) file how / what to do.
Hope this helps.

Anonymous
Comment by Anonymous

Hi,

Thanks for this, worked straight away. Used the method to create the database on an SQL Express instance.

ta
Tim

Anonymous