ASPNETDB.mdf "...already in use..." Error and Broader Questions

When you create and host an ASP.Net 2.0 site on your local computer and then configure .Net 2.0's drop-in user registration and management system it creates a SQL file named ASPNETDB.mdf with a whole bunch of tables and stored procedures (…as most of you know). If you add your own custom tables to that database file and then try to call those tables you get an error that says that ASPNETDB.mdf is already in use. One way to get around this is to create a separate database in the form of another ".mdf" file and then put all of your own custom tables in it. You avoid the "...already in use..." errors that way but all your user accounts and the primary key structure that identifies them are in the original ASPNETDB.mdf file. This makes it impossible to do primary/foreign key relationships between those tables and those in the new separate database you created. It's kind of a catch-22 situation...unless I'm missing something that relates to releasing the ASPNETDB.mdf file from use whenever it is called so that additional tables and queries against that database file can be made without the "…already in use…" error cropping up. I am wondering if this problem is because the ASPNETDB.mdf file is not a "real" SQL database and as such imposes multiple-connectivity limitations such as those I am seeing. If this is true, migrating to a real SQL database would alleviate this? Finally, from a broader security and scalability standpoint…what are the best practices relating to use of the ASPNETDB.mdf database for all your custom tables? Should an additional database be created for all my application's custom tables (leading to the primary/foreign key problems) or should the additional tables be put into the ASPNETDB.mdf file (with some way of working around the "…already in use…" error)? A long-winded and broad question…thanks in advance for any responses.

MDCragg

The default membership provider already has an open connection to the file, which seems like the reason why you can't make your own connection. Perhaps you could extend this provider to keep its current functionality, but add your own requirements to access the other tables, as well. The default provider is the System.Web.Security.SqlMembershipProvider class. After you create this, set it as the default provider in your web.config.

Sample on how to configure a membership provider in web.config:http://msdn2.microsoft.com/en-us/library/44w5aswa.aspx

|||

That article tells how to merge the membership provider tables and stored procedures into my own database. I will try this to see if it solves the problem. I am at least a little bit doubtful if it will though because it seems like it will just shift the same persistent and exclusive connectivity problem I am having from the current user account database to my own database.

|||

This seems to have worked. I installed SQL Server Management Studio Express. I wasn't able to navigate to the existing custom database that I had created so I copied it to the default directory that SQL Server MSE utilizes. I was able to connect to it there and thus "attach" it to my PC's SQL host. Once that was done I was able to use the aspnet_regsql.exe utility to populate that database with all of .Net's Membership tables, views, stored procedures, etc. I copied the database back to the App_Data folder. I adjusted all the Membership entries in the web.config folder to "point to" the custom database instead of the ASPNETDB.mdf file (which I removed). I did some additional tweaking with things such as connection strings. Then I launched the site and everything seemed to work. I am able to connect to the .Net Membership tables as well as all my own tables...all of which exist in the one custom database file.

So, this is fixed although I don't know what the difference was between the ASPNETDB.mdf file and the custom ".mdf" file that I created. I'm sure there is a setting or two somewhere in the database instance, the connection, or something or other that led to the difficulty.