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

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 2

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).