Migrating asp.net web site breaks Membership Provider

Question

I have a asp.net web site that was developed on the .Net Framework v2 connecting to sql server 2000. I am trying to migrate it to a new server that has the .Net Framework v3.5 on it along with sql server 2008. I backed up the database and restored it to the new database server. I moved the web site and updated the web.config. Now however I cannot login to the website. I ran sql profiler to see what was going on and this is the stored proc that gets run when I attempt to login.

exec dbo.aspnet_Membership_GetPasswordWithFormat @ApplicationName=N'dev',
@UserName=N'AffiliateBob', @UpdateLastLoginActivityDate=1,
@CurrentTimeUtc='2009-10-26 20:43:23.7130000'

Notice the format of the @CurrentTimeUtc parameter. When I put this into sql management studio and run it I get the following error message.

Msg 8114, Level 16, State 1, Procedure aspnet_Membership_GetPasswordWithFormat, Line 0
Error converting data type varchar to datetime.

Here is the membership section from my web.config.

<membership>
 <providers>
  <clear/>
  <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="false" 
     requiresUniqueEmail="true" 
     passwordFormat="Hashed" 
     maxInvalidPasswordAttempts="999" 
     minRequiredPasswordLength="6" 
     minRequiredNonalphanumericCharacters="0" 
     passwordAttemptWindow="999" 
     passwordStrengthRegularExpression="" 
     applicationName="dev"/>
 </providers>
</membership>

Answer 1

Does it work OK if you change the date to 2009-10-10? If so, you have a language/locale issue. ASP.NET is sending the date parameter in MM/DD and SQL Server is expecting DD/MM or vice-versa. Check this setting in ASP.NET and for your SQL Server login.

Answer 2

This turned out to be a web.config issue. That I resolved by creating a new clean web.config file and adding sections from the old web.config.

Answer 3

Do you know what changed in your web.config? I am having the same problem, but would rather not re-create my web.config from scratch.

Answer 4

A very related problem:

Hi,

This bug I have has been dragging for 2 days already. I had my application running on a Windows 7 with IIS 7.5 and connected to a main database server running Sql 2008. Now I moved my application to a staging environment (where basically it is a virtual machine) running Win 2008 R2 Server. Connection remained to the same SQL 2008 server.

I moved the web config, set up IIS and taught I'm ready to go. But BUMP!

No users can log in from FTP. I am monitoring using SQL Profiler, Process Monitor and Log Parser.

This is the section of the machine.config which holds the connection strings:

==========================

<system.data>
    <DbProviderFactories>
       <add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
       <add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
       <add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
       <add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
    </DbProviderFactories>
</system.data>


<system.web>
    <processModel autoConfig="true"/>

    <httpHandlers/>

    <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="Clear" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="3" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" passwordStrengthRegularExpression=""/>
        </providers>
    </membership>

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

    <roleManager>
        <providers>
            <add name="AspNetSqlRoleProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
            <add name="AspNetWindowsTokenRoleProvider" applicationName="/" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
        </providers>
    </roleManager>
</system.web>

=====================

I also changed the Username the COM+ FTP Service use to the username ASPNET which is the username we run our IIS on the live environment.

This is what FTP clients say:

Status: Connection established, waiting for welcome message... Response: 220 Microsoft FTP Service Command: USER [email protected] Response: 331 Password required for [email protected]. Command: PASS ** Response: 530 User cannot log in, home directory inaccessible. Error: Critical error Error: Could not connect to server

This is what the FTP log says:

2010-09-30 15:47:31 192.168.80.1 52867 [email protected] FTPSVC1 WIN2008R2 - 192.168.80.128 21 PASS * 530 2148734208 1 54 11 16 19e7d79d-490c-46bd-975e-1dcd9e2ef31d / Authorization+rules+denied+the+access. 2010-09-30 15:47:31 192.168.80.1 52867 - FTPSVC1 WIN2008R2 - 192.168.80.128 21 ControlChannelClosed - - 0 0 126 34 32 19e7d79d-490c-46bd-975e-1dcd9e2ef31d - -

This is what SQL Profiler only see:

exec dbo.aspnet_Membership_GetPasswordWithFormat @ApplicationName=N'/',@UserName=N'[email protected]',@UpdateLastLoginActivityDate=1,@CurrentTimeUtc='2010-09-30 15:39:25.3370000'

I already changed the exec dbo.aspnet_Membership_GetPasswordWithFormat to accept a DATETIME2 since the provider was sending a DATETIME. Running this line in SQL query works fine and returns my password.

From IIS manager I can get the ROLES and USERS of my system without any problem.

The log parser gives the below info:

=================

StartCtrlChannel SessionId={d01995d8-e581-4790-8e05-90150ec82f2e} | PreliminarySiteName=OurWeb | ClientIPAddress=192.168.80.1 | ClientPort=55527 | ServerIPAddress=192.168.80.128 | ServerPort=21 QueueFtpCommand SessionId={d01995d8-e581-4790-8e05-90150ec82f2e} | Command=USER | [email protected] StartFtpCommand SessionId={d01995d8-e581-4790-8e05-90150ec82f2e} | Command=USER | [email protected] QueueFtpCommand SessionId={b9c82b39-683e-43e4-bf55-8a3cbd9c9115} | Command=PASS | Parameter=* StartFtpCommand SessionId={b9c82b39-683e-43e4-bf55-8a3cbd9c9115} | Command=PASS | Parameter=* StartAuthentication SessionId={b9c82b39-683e-43e4-bf55-8a3cbd9c9115} EndAuthentication SessionId={b9c82b39-683e-43e4-bf55-8a3cbd9c9115} | AuthType=CustomAuth | [email protected] | [email protected] | TokenImpersonationLevel=ImpersonationNone StartReadUrlConfig SessionId={b9c82b39-683e-43e4-bf55-8a3cbd9c9115} | Url=/LocalUser/[email protected] EndReadUrlConfig SessionId={b9c82b39-683e-43e4-bf55-8a3cbd9c9115} | UniqueConfigPath=MACHINE/WEBROOT/APPHOST/NewgroveWeb | UseCachedConfig=1 FailedCreateFile SessionId={b9c82b39-683e-43e4-bf55-8a3cbd9c9115} | FileName= | ReturnValue=0x80131500 FailFtpCommand SessionId={b9c82b39-683e-43e4-bf55-8a3cbd9c9115} | ReturnValue=0x80131500 | SubStatus=DENIED_BY_AUTHORIZATION QueueFtpCommand SessionId={b9c82b39-683e-43e4-bf55-8a3cbd9c9115} | Command= | Parameter= CloseCtrlChannel SessionId={b9c82b39-683e-43e4-bf55-8a3cbd9c9115} EndFtpCommand SessionId={b9c82b39-683e-43e4-bf55-8a3cbd9c9115}

================

Using process monitor I realised that the folder /LocalUser/[email protected] IS NEVER asked for. It fails before that.

With regards to user authorisation I have two groups - one with Read access only and one with Read + Write. I have also an authorization on [email protected] user with RW access.

ASPNET user has OWNERSHIP + Full Control on /LocalUser/ and all its children.

The website works fine - creating users / modifying / deleting etc.

The FTP is a publishing service on that same website. If I try to login with pass 123 instead of 1234 it fails - SO authentication is BEING done.

PS I have .NET 4 installed on the server. The application is running on .NET 2.0

Any help is extremely appreciated because I'm really out of ideas and this bug is really bugging me big time!

Many many thanks for reading this post!