Questions: How to configure the ASP.NET 2.0 Membership/Roles Provider to use SQL 2000 or SQL 2005

[jekain314]

I failed on my first attempt to follow these directions.
Let me ask a few questions before I begin again.
///////////////////////////////////////////////////////////</BLOCKQUOTE>
How to configure the ASP.NET 2.0 Membership/Roles Provider to use SQL 2000 or SQL 2005?






The information in this article applies to:

ASP.NET 2.0
MS SQL 2000
MS SQL 2005



[b]SUMMARY

This article describes how toconfigure the ASP.NET 2.0 Membership/Rolesprovider to use SQL 2000 or SQL 2005.





(1) I assume this also works for the 90 day free trialVS 2008 beta which I believe uses MS SQL 2005?DETAILS



The following steps create the full Application Servicesdatabase schema on our SQL Server database.


I'm not sure what the above statement means. On my local app (that works), I have a database.mdf and a ASPNETDB.MDF databases/tables.


Note that I have already set the security for my site at this point to test it. This gave me the ASPNETDB.MDF database.


(2) Do the following steps merge these databases into a single schema?


(3) After I complete the command, will I see anything different locally?


(4) At what point do I copy my local website to the DASP FTP drop?

Open the command prompt on your local computer, and navigate to: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
Execute the command: aspnet_regsql.exe -S DBServerName -U DBLogin -P DBPassword -A all -d DBName


(5) Can I use the more GUI-friendly aspnet_regsql.exe to achieve the same thing as the command line?


The various DBServerName, DBLogin, DBPassword, DBName are as assigned by DASP when I purchased/registered mySQL databasewith DASP.


Obviously this suggests that the registration onto DASPhas already occurred.


Currently, there is no management interface to manage the membership database besides using Visual Web Developer or creating your own application using the membership provider class.


I assume this means that I must use the Web Site Administration Tool that is provided with VS 2008 to add members and roles.


(6) After I execute the aspnet_regsql command, is the admin tool now directing the DASP copy of the site?


(7) Is there any way to revert so that the Admin Tool applies to the local website?





Below is an example of how to configure Visual Web Developer to manage the membership database.




Create a web application in Visual Web Developer or Visual Studio 2005.
Open the web.config.
The default membership provider uses a connection string called "LocalSqlServer". Therefore, replace: <connectionStrings/>



with <connectionStrings>
<remove name="LocalSqlServer" />
<add name="LocalSqlServer" connectionString="Data Source=DBServerName;Integrated Security=false;Initial Catalog=DBName;User ID=DBLogin;Password=DBPassword" providerName="System.Data.SqlClient" />
</connectionStrings>I believe the above connection implies that there is now only one database -- the ASPNETDB.MDF and database.mdf were merged into a single schema. (8) Have I erred by already setting the security up in my local web site so that database.mdf and ASPNET.MDF are separated?


Save and close the web.config.
Go to Website menu, and run the ASP.NET Configuration tool. This will open the Web Site Administration tool.
In the Web Site Administration tool, go to the Security tab.
Click on "Select authentication type".
Select "From the internet". Click the Done button.
Create your admin roles and users.
Then create access rules.
Create a rule that applies to the "Anonymous users" with "Deny" permissions.
Create another rule that applies to the admin role you created with "Allow" permissions.
Your application is now ready to use the membership provider, and you can begin creating your login forms.


In my case, I already went through 1-10 above with my local web site to test it.


(9) Was this a mistake?


(10) If so, is there any way to correct this?


Thanks for your patience ...


---Jim

[raymondp]

(1) I assume this also works for the 90 day free trial VS 2008 beta which I believe uses MS SQL 2005?

Yes it should.

(2) Do the following steps merge these databases into a single schema?

Not sure what you mean by this question, but the steps shows you how to connect to our server to create the schema. Since you are specifying our server, it will only create the schema on the db hosted on our side.

(3) After I complete the command, will I see anything different locally?

Again I'm not quite sure I fully understand this question. If you use the command and point it to your local server then you will see all the tables created. But you will not see it on the db that is on our server.

(4) At what point do I copy my local website to the DASP FTP drop?

Once you have the database on our side configured and you have set the appropriate roles on the ASP.Net Configuration Tool, you should be ready to upload all your forms to our server.

(5) Can I use the more GUI-friendly aspnet_regsql.exe to achieve the same thing as the command line?

I was not aware that there is a GUI-Friendly version for aspnet_reqsql.exe. If there is one, I don't see why it can't be used. But as far as I know there's only the command line version.

(6) After I execute the aspnet_regsql command, is the admin tool now directing the DASP copy of the site?

No, the aspnet_regsql command does nothing more then create the schema in the db. You will still need to use the ASP.Net Configuration Tool to create the memberships and roles. Then the application itself will have to be manually published/uploaded to our servers.

(7) Is there any way to revert so that the Admin Tool applies to the local website?

What Admin Tool are you referring to? I'm assuming you mean ASP.Net Configuration Tool? By default it is already pointing to the local database on your pc.

(8) Have I erred by already setting the security up in my local web site so that database.mdf and ASPNET.MDF are separated?

No, not really. You can use the SQL Management Tool in your control panel to upload the database.mdf database. The ASPNET.mdf is typically the database the holds all the schema for the membership/roles provider. Just rerun the aspnet_regsql.exe and this time point it to our db server.

(9) Was this a mistake?
(10) If so, is there any way to correct this?

This seems to be a common mistake where the membership roles was setup on the local machine. You may get some issues where you create a form that uses membership/roles but the user you created does not have the correct permission. The best way to fix this is to quickly create a web app that uses membership roles on your local pc. Have it set to connect to our db server. This should activate ASP.Net Configuration Tool. This tool will then be linked to our db server. Then use this tool to create the roles and members on our db server.

rcp
DiscountASP.NET
www.DiscountASP.NET (http://www.DiscountASP.NET)

[jekain314]

(2) Do the following steps merge these databases into a single schema?

Not sure what you mean by this question, but the steps shows you how to connect to our server to create the schema. Since you are specifying our server, it will only create the schema on the db hosted on our side.



What I meant here was that when I work locally (following addition of establishing members/roles), I see locallytwo distinct SQL databases: ASPNETDB.MDF and my custom database, database.mdf --Each with separate schemas. By schema, I refer to the structure &amp; record formatsof the various tables and primary/foreign key relationships among tables.The ASPNETDB.MDF is a default database that is automatically established as soon as you run the "Web Site Administration Tool->security" tool that is provided with VS2008. If I move my web site to DASP using the VS Copy utility, thenI see the ASPNETDB.MDF there on the DASP FTP site.When I run aspnet_regsql.exe, do I end up with a single database at the DASP sitethat has the tables from ASPNETDB.MDF and database.mdf merged into a single database.



(3) After I complete the command, will I see anything different locally?

Again I'm not quite sure I fully understand this question. If you use the command and point it to your local server then you will see all the tables created. But you will not see it on the db that is on our server.


This may answer my concern. If I point aspnet_regsql at my local site, then I can observe the result of creating the schema locally.

(4) At what point do I copy my local website to the DASP FTP drop?

Once you have the database on our side configured and you have set the appropriate roles on the ASP.Net Configuration Tool, you should be ready to upload all your forms to our server.


I believe the steps you refer to are use of the DASP"SQL Tool Suite" tab -> "attach Data File". To use this, the database file (database.mdf in my case) would already have been moved to the FTP site. I point to this file on the DASP FTP siteand the DASP app attachesmy database to the DASP SQL server.Next you refer to "set the appropriate roles on the ASP.Net Configuration Tool" -- I am assuming you mean the"Web Site Administration Tool->security" tool that is provided with VS2008 and not the tool this is available at DASP. The VS2008 tool is now pointed at the DASP site so it can be used to do the members/role assignment.



(5) Can I use the more GUI-friendly aspnet_regsql.exe to achieve the same thing as the command line?


On my system, If i double click the aspnet_regsql file, i get a GUI that has most of the switches (but not all). With my fat-fingers, i find it very hard to type all the info and not screw it up several times. When i screw up the typing, i seem to blow away the aspnet_regsql.exe appand have to go find another copy. (It shows as a zero byte file). The GUI bypassess this messytyping but doesnt seem to have all the flexibility of the command line.

I was not aware that there is a GUI-Friendly version for aspnet_reqsql.exe. If there is one, I don't see why it can't be used. But as far as I know there's only the command line version.

(6) After I execute the aspnet_regsql command, is the admin tool now directing the DASP copy of the site?

No, the aspnet_regsql command does nothing more then create the schema in the db. You will still need to use the ASP.Net Configuration Tool to create the memberships and roles. Then the application itself will have to be manually published/uploaded to our servers.


Again, I would take the phrase "create the schema in the db" to mean create the various tables/relationships in the ASPNETDB.MDF file autocreated locally when you are running locally. I assume by "ASP.Net Configuration tool" you mean the "Web Site Administration Tool->security" tool that is provided with VS2008. The semantics of the various events going on (and possible terminology difference betweenASP 2.0 an ASP 3.5)when I develop locally and then transfer all to DASP is what is confusing me!! "Then the application itself will have to be manually published/uploaded to our servers." -- but in my case, I have a complete developed site running locally and copy everything at once (using the VS2008 Copy Utility)-- database.mdf and all the forms and code behind. You seem to suggest a more sequential upload process --

(7) Is there any way to revert so that the Admin Tool applies to the local website?

What Admin Tool are you referring to? I'm assuming you mean ASP.Net Configuration Tool? By default it is already pointing to the local database on your pc.


Yea -- I mean the "Web Site Administration Tool->security" tool that is provided with VS2008. But i thought when I ran aspnet_regsql that the action was revectored and applied to the DASP version of the database.

(8) Have I erred by already setting the security up in my local web site so that database.mdf and ASPNET.MDF are separated?

No, not really. You can use the SQL Management Tool in your control panel to upload the database.mdf database. The ASPNET.mdf is typically the database the holds all the schema for the membership/roles provider. Just rerun the aspnet_regsql.exe and this time point it to our db server.

(9) Was this a mistake?
(10) If so, is there any way to correct this?

This seems to be a common mistake where the membership roles was setup on the local machine. You may get some issues where you create a form that uses membership/roles but the user you created does not have the correct permission. The best way to fix this is to quickly create a web app that uses membership roles on your local pc. Have it set to connect to our db server. This should activate ASP.Net Configuration Tool. This tool will then be linked to our db server. Then use this tool to create the roles and members on our db server.


Please forgive my fumbling -- but i am a senior 20 yr C/C++ programmer yet new to ASP and web design. Microsoft paints VS2008 as the web enabler for the rest of us so my guess is i am amongst a lot of others stumbling into web programming using the "free" MS tools. If I can get this right, I'll attempt to publish (here) a doc describing my learning experience.


At this point i am restarting with a single web form and database with single table and then adding the membership/roles. I have struggled with screwing this up and find that i cannot backup to unscrew the screwups.


Thanks for you patience.





--Jim