Asp.Net membership via ASP.NET Website Administrator Tool

Question

I created a database with aspnet_regsql, the database was created in sql sever 2008 and not in data folder in my project (do I need to move it to the folder manually?).
Next, in Web Site Administration Tool I went to provider section and clicked don Test button.

I got an error:

Could not establish a connection to the database. If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider.

Maybe I need to set something in a web.config, like membership settings or connection strings (or ASP.NET Website Administrator Tool should create those settings for me)?

Update:
Maybe it happens because I am using SQL server 2008 full and not express?

Update 2:

After setting membership section and connection string to my aspnetdb database in Web Site Administration Tool I've opened security->Security Setup Wizard->Define Roles (stage 4) I got this error:

An error was encountered. Please return to the previous page and try again.

The following message may help in diagnosing the problem: Unable to connect to SQL Server database. at System.Web.Administration.WebAdminPage.CallWebAdminHelperMethod(Boolean isMembership, String methodName, Object[] parameters, Type[] paramTypes) at ASP.security_wizard_wizardpermission_ascx.OnInit(EventArgs e) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Control.InitRecursive(Control namingContainer) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Answer 1

Yes, you'll need to modify your connection strings in the web.config.

Look in the web.config, you'll probably see something like this in the <membership> element:

<membership>
  <providers>
    <clear />
    <add connectionStringName="LocalSqlServer" 
         name="AspNetSqlMembershipProvider"
         [...]
         type="System.Web.Security.SqlMembershipProvider, 
               System.Web, 
               Version=2.0.0.0,
               Culture=neutral,
               PublicKeyToken=b03f5f7f11d50a3a" />
  </providers>
</membership>

The connectionStringName Attribute tells the framework to look in your <connectionStrings> element for a connection string called "LocalSqlServer" - this may not be defined in your web.config as it can be inherited from the Machine.Config in the .Net Frameworks core config directory.

You should probably pick a new name for the connection string, and update your membership, roles and profile (if you're using them all) providers to use the new name.

Then in the <connectionStrings> element have the following:

<connectionStrings>
  <remove name="LocalSqlServer" />
  <add name="MyConnectionString"
       connectionString="Data Source=[ServerName];Initial Catalog=[DatabaseName];Integrated Security=True"
       providerName="System.Data.SqlClient" />
</connectionStrings>

Adjust the values to suit your systems - I'd recommend Integrated Security over storing your database login and password in the web.config, but YMMV (for example, on my shared host, I can't use Integrated Security, so have to supply user/pass details).

The Web Site admin tool will honour these connection strings as well.

Edit to respond to comment

Have you successfully logged in at all? Or created a user? I just want to rule out that you've the connection string configured and working?

Do you have the Roles Provider configured and turned on as well? Have you updated the "connectionStringName" attribute in there?

Note that as before, the machine.config specifies a default role provider for you that attempts to point to a local SqlExpress instance - which is probably where the connection error is coming from.

<!-- Note enabled="true" - you need to turn this one on! -->
<roleManager enabled="true">
  <providers>
    <clear />
    <add connectionStringName="MyConnectionString"
         [...]
         type="System.Web.Security.SqlRoleProvider, 
               System.Web,
               Version=2.0.0.0,
               Culture=neutral,
               PublicKeyToken=b03f5f7f11d50a3a" />
  </providers>
</roleManager>

For more details on the elided attributes, I'd recommend the documentation:

• roleManager Element

• providers Element for membership

Once you've configured that, it should all work.

If you've not been able to create a user/log in, I'd check:

1. The connection string - make sure the names match, make sure the data source matches your server instance (not always just the name of your machine, it might be something like "MachineName\SqlServer").
2. If you are using integrated security, you will need to ensure that you've given the account your site runs under the appropriate rights in the database: You should find that your database has a number of roles in it starting with aspnet_, to ensure that you can create users (which you'll need if you have a registration form) you should add the account to the aspnet_XXXX_FullAccess roles (one each for Membership, Personalization, Profile and Roles).

Answer 2

Open sql management studio and open the master database, make a new query and type

sp_configure 'user instances enabled','1'

execute this, then type

reconfigure 

and execute this too so the changes take effect. For more information on sp_configure you can read http://msdn2.microsoft.com/en-us/library/ms188787.aspx if you wish to list all the configuration then execute sp_configure without any parameters.

Answer 3

Follow the below steps For proper Membership integration to your website(if you see the above error like

"Could not establish a connection to the database. If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider.").

step 1:

Run aspnet_regsql.exe utility from C:\windows\Microsoft.NET\Framework\v2.0.50727 folder on your machine. Selecting or double clicking the aspnet_regsql.exe utility opens the wizard as below.

Selecting Next button on above window opens next window where you can either configure SQL Server for Application services or remove Application services information from an existing database.

step 2:

Select configure SQL Server for application services radio button to install new database and select Next button.

Step 3

Enter your SQL Express server name as as shown below. Note that you can change the application services database name to your desired name (aspnetdb by default).(or) you can select Sql Server authentication (user name,password,respective database name is needed)

Note that i named the database to ASPNETServices

Step 4:

Select Next button and confirm that your settings are correct to go further with installation (shown in below image) and select Next button.

Step 5

Selecting Next button on above window takes you to confirmation window as below after successful installation of your application services database for ASP.NET.Finally press Finish.

Step 6

You will see installed ASPNET Services database ready to use within your ASP.NET application

Answer 4

I've been having similar issues as this is my first time working with asp.net membership. If you set the <roleManager Enabled="False" /> you will be able to get through to creating users.

I'm guessing here, but I think that an additional provider in the web.config for the AspNetSqlRoleProvider and giving it a connection string that points to the asp services database with a user that is setup to access role management information on the SQL side (view the sql user's properties for "schemas owned by this user" and "Database role membership") will allow the tool to get into the DB correctly.

Are you using windows authentication or SQL authentication on your SQL Server?

PS -

The more I read and understand Zhaph's answer, the more I like it. He gave a link to the msdn schema for the AspNetSqlRoleProvider element (don't be fooled by microsoft saying the document doesn't exist, just select an appropriate version from the "other versions" drop down).

Update I did a bad thing in posting here before actually testing what I was writing, but I did follow through and adding the following to the web.config allowed me to both continue editing users and add/manage roles.

<roleManager enabled="true">
  <providers>
    <clear/>
    <add name="AspNetSqlRoleProvider"
         type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
         connectionStringName="MyConnectionString"/>
  </providers>
</roleManager>

as i mentioned earlier, the user specified in my connection string required the aspnet_Roles_...Access properties as well as aspnet_Membership_...Access unless you're using integrated security=true along with the <identity impersonate="true" userName="windowsUser" password="winUsrPassword" /> in which case your windows user needs those properties.