SharePoint, .Net, SQL, MS BI, Collaboration and Gadgets

There are conditions where we have our development environment on a SQL express (vs 2005) machine and we are trying to get the same code to run on a SQL Server 2005 machine. But, we cannot connect to the ASP.NET provider db even after registering a sql db in asp_net_regsql or something like that. By default, it uses whatever settings are in the machine.config file. So, that file usually has the settings that point to using a LocalSqlServer connection string, which is also defined in the machine.config as a user instance DB in the DataDirectory.

There are 2 ways to make it works:

1. Removing the LocalSqlServer connection string and then adding one to your point to your DB in web.config. The connection strings section of your web.config file should look something like this:

   <connectionStrings>
   <remove name=”LocalSqlServer”/>
   <add name=”LocalSqlServer”
   connectionString=”Data Source=Server\Instance; Integrated Security=SSPI; Initial Catalog=database;”
   providerName=”System.Data.SqlClient”/>
   </connectionStrings>

2. Provide your own <membership> section and specify the specific connection & provider type to use for membership.

   <configuration>
   <connectionStrings>
   <add name=”MySqlConnection” connectionString=”Data
   Source=MySqlServer;Initial Catalog=aspnetdb;Integrated
   Security=SSPI;” />
   </connectionStrings>
   <system.web>
   <membership defaultProvider=”SqlProvider” userIsOnlineTimeWindow=”15″>
   <providers>
   <clear />
   <add
   name=”SqlProvider”
   type=”System.Web.Security.SqlMembershipProvider”
   connectionStringName=”MySqlConnection”
   applicationName=”MyApplication”
   enablePasswordRetrieval=”false”
   enablePasswordReset=”true”
   requiresQuestionAndAnswer=”true”
   requiresUniqueEmail=”true”
   passwordFormat=”Hashed” />
   </providers>
   </membership>
   </system.web>
   </configuration>

* Thanks to Shawn Cicoria and George McKee for the solution.