Using your own sql database for ASP.NET 2.0 membership

I seem to be getting this question through my blog and elsewhere often enough to justify a post about it even though there are many great (and more detailed) articles on this topic.

How do you use your own SQL database for ASP.NET 2.0 Membership instead of the default SQLExpress database.

The default db is configured under the covers as a membership provider. What you need to do is override this in web.config. Note that you will have to do this for roles and personalization as well if you are using that.

Start by ensuring that your connection string is set up in web.config.

    <connectionStrings>
        <add name=”MySQLConnection” connectionString=”server=MyServer;Trusted_Connection=true;database=myDB”/>
    </connectionStrings>

Inside of the <system.web> section, you need a membership provider section. I am using code from this msdn document as a base. The membership section is told to use the provider named SqlProvider as the default. This provider is defined inside of the inner section “providers”. The “remove name” element gets rid of the that AspNetSqlProvider that is set up by default when you start configuring membership in the ASPNet Web Site Tool. Then the provider with the name SqlProvider is created. You can create many providers if you like. 

Drilling further in, notice hat this provider is pointing to the System.Web.Security.SqlMembershipProvider class. That tells the provider what implemenation to follow. That is where everything abou memebership happens such as logging in, getting users, etc. You can even extend the existing providers or just write your own. In that case, you would have your own class as the value of the type for the provider.

Next you will see a ConnectionStringName parameter. This is where you plug in the name of the ConnectionString that you created above.

        <membership
             defaultProvider=”SqlProvider”
             userIsOnlineTimeWindow=”20″>
             <providers>
                <remove name=”AspNetSqlProvider” />
                <add name=”SqlProvider”
                    type=”System.Web.Security.SqlMembershipProvider”
                    connectionStringName=”MySQLConnection”
                    enablePasswordRetrieval=”false”
                    enablePasswordReset=”true”
                    requiresQuestionAndAnswer=”true”
                    passwordFormat=”Hashed”
                    applicationName=”/” />
            </providers>
        </membership>
    </system.web>
</configuration>

Note that if you are using roles or personalization in your site and you want that to be tied to your database as well, you will need to similarly configure

<roleManager>
  <providers …>
</roles>

and

<webParts>
  <personalization>
     <providers …>
  </personalization>
</webParts>

That should get you started!



Don’t Forget: www.acehaid.org

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!  

2 thoughts on “Using your own sql database for ASP.NET 2.0 membership

  1. An easier way to do this is go directly to machine.config and change the connection string there.This way you can leave all the provider stuff as default.Go to machine.configChange the connection string as below (my SQL server is named sql2005, yours will be different.<connectionStrings><add name="LocalSqlServer" connectionString="SERVER=sql2005;Integrated Security=SSPI;DATABASE=aspnetdb" providerName="System.Data.SqlClient"/></connectionStrings>This is much faster and easier.

  2. Iam pretty new to Membership and Role Management in ASP.Net, hope you will be right person in helping me out.

    As per my knowledge, assigning permissions to roles can be done at folder level.

    I have to build a custom Website administrator tool which uses the ASPNetDB database(available with .net framework), it should have an option to create roles and edit them in such a way that the users should be able to assign permissions to the roles at page level and also at functionality level within that Page for each role.

    Pls mail me at [email protected]

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.