aspnetdb.mdf on Hosting Server ?

I have established roles to restrict access to the dataentry pages. The database is SQL Server which I've established on the hosting server. Security was previously maintained through~/app_data/aspnetdb.mdb (MS Access). It works, but isn't too stable, so I have modified the security to useSQL Server with an aspnetdb.mdf file, also located in the app_datafolder. Is this appropriate? Since it doesn't work, I assume not.

It's now my assumption that I need to setup a SQL Serverdatabase to support the security/roles. I have seen some reference tousing a script to build the aspnet.mdf on the hosting server. IfI do create such a database, is there anything special I need to do tohave my application read the aspnetdb? Do I need a connection stringin the membership and/or roleManager sections of my web.config.

A little guidance would be appreciated.

Thanks

http://www.eggheadcafe.com/articles/20060529.asp

|||

Thank you Dr. Bromberg for the extensive material you've pointed me to. I have downloaded the code and created a web site on my local Visual Studio server. I was able to run the SQL to create the two databases, Articles and aspnetdb (I think that's what I was suppose to do?). I have modifed the web.config to reflect the localhost information. When I run the Default.aspx I receive the following error:

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection.

OK, something's not setup properly. I attempted to run the SetupASPNetDatabase.aspx page and get absolutely nowhere, most likely because I have no idea what values to submit in the query boxes.

Bottom line is, I am not clear on what I need to do to get started. I am sure there is considerable information to glean from your article and sample web site. I would genuinely like to learn from it. The problem I seem to be having through is getting the security configured, which takes me back to square one with my initial question. I feel as if I am in the proverbial Catch 22. I need to get my own hosted security working and/or get your sample working before I can get either of them working.

|||

To try and answer your question about setting up security with SQL Server...

1. Install the security schema (tables, procedures etc) on the SQL Server database. You can do this on your local machine by running aspnetregsql.exe (it's an installation wizard which comes with the ASP.Net framwork). If you have full control on your hosted server, then install the same way, otherwise your hosting provider will have to assist (some offer the installation as an option via their Control Panel). Note that the database doesn't have to be called ASPNETDB, you can install the security schema into an existing database of a different name.

2. Set up a connection string that points explicitly at the SQL Server database i.e. server name, database name, user id and password.

3. Point the Membership provider at this connection string.

I've put a specific example from one of my own web.config files using SQL Server - this is the connection string from mydevelopment environment (so uses localhost for the server name). Can you see that the Membership provider points at the "MainDB" connection string - which is pointing at the SQL Server?

The live web.config settings are very similar, except that instead of localhost I have the name of the server. Hope this helps!

<connectionStrings>

<addname="MainDB"connectionString="Server=localhost;Database=DB_138621;User ID=*****;Password=*****"providerName="System.Data.SqlClient" />

</connectionStrings>

--- snip -----

<membershipdefaultProvider="AspNetSqlMembershipProvider">

<providers>

<clear/>

<addname="AspNetSqlMembershipProvider"type="System.Web.Security.SqlMembershipProvider"connectionStringName="MainDB"minRequiredPasswordLength="5"minRequiredNonalphanumericCharacters="0"requiresQuestionAndAnswer="false"enablePasswordRetrieval="true"enablePasswordReset="false" applicationName="/"/>

</providers>

|||

Thank you Salmon. Using your example and wading through my web.config with fat fingers, I was finally able to make this work.