Setting up the default AspNetSqlProvider to point to remote database

When starting a new project that required the use of membership providers I found that I could not connect to a remote database that contained the membership database.

I ran aspnet_regsql and was able to create the membership database on the remote server but when I go to ASPNET Configuration (cassini development server) it will not connect to the remote server.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

After much searching I found that the default Membership Provider specified in machine.config (c:windowsMicrosoft.NETFrameworkv2.0.50727CONFIG) was always pointing to a SQL Server running on the localhost.

Instead of modifying machine.config there is a way to set it up in the projects web.config:

1) Setup the connection string to the remote database

    <connectionStrings>
      <add name="aspnet_membership" connectionString="<your_connection_string>"/>
    </connectionStrings>

2) In <system.web> redefine the default provider:

  <membership>
    <providers>
        <remove name="AspNetSqlMembershipProvider"/>
        <add name="AspNetSqlMembershipProvider" 
            type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" 
            connectionStringName="aspnet_membership"
            enablePasswordRetrieval="false" 
            enablePasswordReset="true" 
            requiresQuestionAndAnswer="true" 
            applicationName="/" 
            requiresUniqueEmail="false" 
            passwordFormat="Hashed" 
            maxInvalidPasswordAttempts="5" 
            minRequiredPasswordLength="7" 
            minRequiredNonalphanumericCharacters="1" 
            passwordAttemptWindow="10" 
            passwordStrengthRegularExpression=""/>
    </providers>
  </membership>

The <remove name="AspNetSqlMembershipProvider"/> is key! All the other key/values were taken directly from machine.config

Method 2

<membership>
       <providers>
           <clear/>
           <add name="AspNetSqlMembershipProvider"
               type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
               connectionStringName="aspnet_membership"
               enablePasswordRetrieval="false"
               enablePasswordReset="true"
               requiresQuestionAndAnswer="true"
               applicationName="/"
               requiresUniqueEmail="false"
               passwordFormat="Hashed"
               maxInvalidPasswordAttempts="5"
               minRequiredPasswordLength="7"
               minRequiredNonalphanumericCharacters="1"
               passwordAttemptWindow="10"
               passwordStrengthRegularExpression=""/>
       </providers>
   </membership>

I needed to add this lines to get my Roles and Profile staff based on Membership class working:

   <profile>
       <providers>
           <clear/>
           <add name="AspNetSqlProfileProvider"
           connectionStringName="aspnet_membership"
           applicationName="/"
           type="System.Web.Profile.SqlProfileProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
       </providers>
   </profile>

   <roleManager enabled="true">
       <providers>
           <clear/>
           <add name="AspNetSqlRoleProvider"
           connectionStringName="aspnet_membership"
           applicationName="/"
           type="System.Web.Security.SqlRoleProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
           <add name="AspNetWindowsTokenRoleProvider"
           applicationName="/"
           type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
       </providers>
   </roleManager>

Method 3

step 2: copy this:

<remove name="LocalSqlServer"/>

<add name="LocalSqlServer" connectionString="Data Source=VMKsqlexpress;Initial Catalog=commodity_exchange;Integrated Security=True" providerName="System.Data.SqlClient"/>

Method 4

Here is the solution:

step 1: Launch Visual Studio command prompt
Type: aspnet_regsql
Specify your server: if sqlexpress then server = hostnamesqlexpress
***********Use Windows Authentication

step 2: Copy this to web config.Dont specify username or password because ur connecting with windows authentication that’s why we have integrated security =true.

step 3 : Change security>Authentication type on web administration tool to “From the Internet”.

Enjoy now.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0