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!

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