SQL Server and configuring ASP.NET 2.0 Membership/Roles Provider - KB article 10413

Knowledge Base article 10413 - some comments and suggestions for improvement.


Once the process is understood, the article becomes much more clear, but the instructions would be better if written for those new to the subject (like me for example!).


1a) In a few placesthe article is ambiguous; for example, on which server the actions are being performed.


1b) The instructions don't really differentiate between an existing database on the DASP server, an existing database on a user's local server, or a new database to be created by the user on the local server (following instructionsin the article).


2) The instructions in the opening part reference creating a "schema" - I have added some more on this below at ***. I read somewhere (maybe the help desk told me) something about this creating a "path" to the database on the DASP server. However, schema and path are different concepts. This could be explained better.


3) The instructions present 2 scenarios but don't say whether both are required, or whether they are "either/or" scenarios.


For example, in the DETAILS section, if I have installed database membership and roles already on my database, on my local server, [edit: before uploading it and attaching it] do I need to run aspnet_regsql.exe on the DASP server?


4) The instructions don't specify that when you connect to DASP using aspnet_regsql.exe, whether the DASP server name needs "http://" in the path. I tried both - with and without - and get different results.


5) The instructions say there is no graphical tool to use to construct the membership schema, and that the command-line interface is required. This is not true if one has SQL Server 2005 on one's local server. There is a wizard that will walk you through the procedure.


6) The instructions in "how to configure Visual Web Developer ... " were clear as for creating a new database from scratch. I did this because I'm only working with test data. But this will not be possible, of course, if you have an existing database.


7) The article is silent on many issues: for example, anything that must be done with the web.config file you modify when following the instructions, but maybe this is covered in a different KB article (since permissions are file-system based and don't depend on the location of the database).


8) [on edit:Igotthe false impression on reading the article, maybe it was another KB article,that as I added users and roles, I needed to create a login name in my local development database identical to my DASP login name for the database. I did so, and then got locked out after I uploaded and attached the database. Apparently the permission sets conflict, and this required a permission reset by DASP. The DASP login must be different from any other user names on the uploaded database.]


I haven't fully finished the process, so anyone, please feel to correct my comments if necessary.


HELPFUL HINTS


A) Personally I created a batch (.bat) file that contains the aspnret_regsql.exe command instructions. This helps avoid spelling errors, saves you time if you have to repeat the process. And you can paste the command into an email (remove the password please) so the help desk can see exactly the command you used.


B) I found a great article on the web that helped me with the process: You can see it here:


Examining ASP.NET 2.0's Membership, Roles, and Profile - Part 3 - By Scott Mitchell - "How to add the membership-related schemas to an existing database using the ASP.NET SQL Server Registration Tool (aspnet_regsql.exe)".


Link: http://aspnet.4guysfromrolla.com/articles/040506-1.aspx


At that page there is a list with links to all 7 articles in this series (explains the provider model, etc).


(Their instructions are a bit different from the DASP instructions, so use DASP's to be sure.)


*** This is what the article says about Schema: "The membership and roles providers used by ASP.NET by default are the SqlMembershipProvider and SqlRoleProvider, respectively, which serialize membership and roles information to a SQL Server database. Specifically, this information is stored in a variety of pre-defined tables and accessed through a number of pre-defined stored procedures. In order to use membership or roles with your application using the default providers you need to apply this pre-defined schema to your application's database."


C. [on edit: If you're building a database from scratch following the instructions in the article, you will get a database of about 10 Megs if you let Visual Studio create the database for you (ASPNETDB.MDF), which it will do if you "add" roles etc when there is no database present. However, you will get a much smaller database (3 megs) if youFIRST create a new database using Server Explorer, add a few working tables and then use the Asp.Net Configuration tool to add users and permission. This will save you time when you upload the database.]


THANK YOU DASP for all your great support!

Post Edited (Steve Harnish) : 12/7/2006 11:04:28 PM GMT

 

I'm impressed. . .Good community discussion and well handled.

 

Hi,
Been away from the forum for a while, and saw your message when trying to catch up. Though I haven't looked at the KB article you were refering too, your comments about it were very well expressed.
Sounds like you were thrown a bit by the term schema. If you are not familiar with the term, it just refers to the underlying structure of a database. In most cases (as with MS SQL Server), this is the logical structure for a Relational Database. The logical schema is the collection and connection of tables and supporting procedures that determine how the data is to be stored in the physical implementation of the database. For the membership features of ASP.Net 2.0, they have a predefined schema, and your application has to use and refer to that schema in order to work. Every object in a SQL Server database schema has an identifier (name), and you'll need to use the identifiers for the objects in the predefined schema to utilize the membership features. The data types are predefined too, so you need to look at what they are in order to make your your application is suing the same data type for a given column.
I'll take a look at the article and see how your other points fit in. I'll also take a look at the site you mentioned. Hope it helps. BRN..

 

There were several issueswith the article. One of the essential questions is pretty simple. Let's say I createa brand new website on my local server, open the web confuguration tool toset up users and roles - so Asp.Net automatically creates the ASPNETDB.MDF file in the App_Data directory and sets up some profiles in the web.config file. Then I upload and attach the database to my website. I upload the web.config file and set the connection string in it to point to my database. OK: What else do I need to do on the DASP server in order to use my database? Do still need to use the aspnet_regsql.exe tool or no?This isone question everyone seems to be dancing around, and I can't seem to get ananswer to it. But maybe that's because I haven't expressed the question clearly enough. Thanks for your help.

Post Edited (Steve Harnish) : 12/7/2006 5:25:28 AM GMT

 

Hi,
http://www.codeplex.com/Wiki/View.aspx?ProjectName=sqlhost&title=Database Publishing Wizard
(Should be a link to a tool to upload a SQL Server DB to a shared hosting environment)
Take a look at the link above. Can't say it will give you the specific answers to your questions, but it looks like it (supporting tutorials, as well as the tool), might cover the same ground. I came across it, though I haven't had the time to try the tool or read all the stuff that goes with it. If the link doesn't help you now, it may be something that will help in a future project. If you try it and find it useful, let me know. Good luck. BRN..

 

Steve;</o>
You've made some excellent points about KB Q10413. This is my third day in a row working at trying to get aspnetdb.mdf (SQL Express) to work with SQL 2005 and although I've made some headway, things are still not working.</o>
I'm finding information in bits and pieces but have been unable to find a single "loading your SQL Express db into SQL 2005 for dummies" article.</o>
What DASP needs is a step-by-step process, for the novice user, that not only explains what to do, how to do it, but why you're doing it. Sure would make the process a lot more painless. Another would be a list of common error messages and what the likely source of the problem would be. However, if there was the former article, the latter might not be needed.</o>
Best regards,</o>


Lar

 

That's easy, if you're talking about local host. From SQL Server 2005 just "connect" to your server, right click on Databases and select "attach [database]," then click Add, wait for the "locate database files" dialog box and navigate to the database file on your local machine. Onced it is "attached"- you see it in your database list -right click on it and rename it so you get rid of the lengthy file path in the database name. You need to exit Visual Studio before you do this because otherwise the file will be locked. When you're done manipulating the file in SQL 2005 remember to unattach the database before you go back to Visual Studio.

These are just the technical instructions but they don't address what you might be trying to accomplish, which may or may not be related to the topic of this thread. SQL Server is a very complicated piece of software and is baffling to a novice (like I was once) but after a little while things become obvious. But there's no point in wandering around in the wilderness.I'd recommend getting a good book and walking through some chapters step by step. I used Wrox "Beginning SQL Server 2005 Programming" by Robert Vieira. It's very clear. DASP couldn't possibly address all you need to know via KB articles.

 

Steve &amp; Bruce;


You both make excellent points. I think I'll buy a book.


Thanks for your help,


Lar

 

You can use aspnet_regsql.exe against ANY database, should it be EMPTY or NOT EMPTY.
For example, if you use an SQL DB in your application, say it 'Application.mdf', where you store all your DB stuff for your application, you can run aspnet_regsql.exe against 'Application.mdf'.
This will create into 'Application.mdf' all the tables needed by the providers (membership, roles, etc.) depending on the options you set in aspnet_regsql.exe, the same way as it creates them in a separate ASPNETDB.mdf.
This WILL NOT ERASE your application tables present into 'Application.mdf', it only ADDS the ASPNETDB.mdf stuff into 'Application.mdf'.

This way, you only need one database ('Application.mdf') that contains your application tables AND the ASPNETDB.mdf tables.
Then you only have to change into 'Web.config' the LocalSqlServer connection string to point to 'Application.mdf'.

All of this (aspnet_regsql.exe and Web.config/LocalSqlServer change) can be done at your developpement PC and then uploaded (Web.config + Application.mdf) to your DiscountASP account, or directly running aspnet_regsql.exe against the DiscountASP/Application.mdf and changing DiscountASP/Web.config.

Hope this clarify some points.