1. Introduction
ASP.NET 2.0 brings an important concept called provider model which is used for many new feature i.e. Membership Management, Role Management, Profiles, Web Parts Personalization etc. By default VS 2005 ships with provider for SQL Express however it provides necessary interfaces for writing custom providers. In this article we will see how to enable provider for SQL Server 2005 without creating a custom provider.
2. Enabling Membership Provider
In this article we will demonstrate the approach to enable the membership provider for SQL Server 2005 however the approach will remain same for other providers i.e. Roles, Profile etc. By default the membership provider (not only membership provider but other providers also) uses SQL Express database. To use your own SQL server database instead of the SQL Express database, you need to create the same database in your SQL Server using aspnet_regsql tool.
After running this utility you can view the aspnetdb database created by opening the SQL server management studio. Please notice various tables created for storing various configuration settings, membership, roles, profile data and personalization data etc.
Now you just need to point the connection string of exiting provider to your database instead of SQL Express database.
Refer the machine.config file stored in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50215\CONFIG folder.
If you search for membership you will find an entry similar to following
<membership>
<providers>
<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />
</providers>
</membership>
If you observe the above, you will notice that the membership provider above "AspNetSqlMembershipProvider" uses a connection string LocalServer. If you search for LocalServer in the machine.config file you can see that the same points to the local SQL Express database.
<connectionStrings>
<add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />
</connectionStrings>>
Now the easiest thing will be to change the settings of LocalServer and point it to your database instead of SQL Express and that will solve your problem. If you change the settings for the LocalServer connection string, all your providers will point to the SQL Server instead of SQL Express.
Instead of changing the machine.config you can also try following.
Add a connection string in your web.config file, add a provider entry in the web.config file and then point the provider to the connection string as shown below.
<connectionStrings>
<add name="MySqlProviderConnection" connectionString="server=localhost;integrated security = true;Initial Catalog=aspnetdb" />
</connectionStrings>
<system.web>
<membership defaultProvider="MySqlMembershipProvider">
<providers>
<add connectionStringName="MySqlProviderConnection" name="MySqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers>
</membership>
</system.web>
You can use above approach if you want to use SQL server only for a specific provider and continue using SQL Express for others.