SqlRoleProvider fails to connect to DB.

Question

I have the following config for roles using standard SqlRoleProvider, but I get an error when I try and open the Security page in the wweb site Admin tool:

  <roleManager enabled="true" defaultProvider="AspNetSqlRoleProvider">
    <providers>
      <remove name="AspNetSqlRoleProvider"/>
      <add name="AspNetSqlRoleProvider"
           type="System.Web.Security.SqlRoleProvider"
           connectionStringName="PoyntingInstallationConnectionString"
           applicationName="/" />
    </providers>
  </roleManager>

The DB has all the role tables etc.

Answer 1

Ensure that PoyntingInstallationConnectionString is in your web.config, is configured correctly, and points at the database that contains your tables.

The roles would be entries in the aspnet_Roles table, not database roles. And there are no default roles, so the table should be empty when you first run the script.

Edit: Can you provide more details on how you created the auth DB? I.e. did you run the aspnet_regsql tool directly, or did you generate and run the scripts separately?

Edit 2: The aspnet_regsql tool is easy enough to run - it is in the .NET Framework v2.0 folder. (Default location C:\Windows\Microsoft.NET\Framework\v2.0.50727)

There are a bunch of parameters that can be used to be run from the command line, or if you run it without parameters it will open a wizard that steps you through it. Note that, using the wizard, you get support for all of the membership provider features (profiles, personalization, web events...) even if you don't intend to use them.

I'd suggest that you run it and then compare the results with your existing database. Did someone run the tool and then just copy the tables over to another database without realizing that there was a bunch of other stuff required as well? I just tried running the tool both from the command line and wizard, and it generated all of the roles, procs, etc.

When run correctly, you should have a bunch of tables (at least 6), stored procedures (a couple dozen or so - mine has 35) and DB roles (at least 6) with names starting with aspnet_, and a bunch of views (at least 5) with names starting with vw_aspnet_. If you're missing any part of these, the provider won't work.

I typically run it from the command line so that I only get the membership and role stuff. That will cut down on the noise quite a bit, but is also why my numbers above are a little fuzzy. :)