Last post Apr 15, 2009 05:22 PM by harafeh
Mar 19, 2009 12:25 AM|harafeh|LINK
I have an asp:PasswordRecovery control that stopped responding to "Forgot your password". I enter a valid user name (I know it's valid because I used it to login) and press next (this is normally where one gets their password mailed to them) and I get the
error "Your attempt to retrieve your password was not successful, please try again".
So I fired up SQL profiler and found that the code was calling first aspnet_Membership_GetUserByName() and then aspnet_Membership_GetPassword(). I tried the same calls in
SQL Query and I got the error: "Error converting data type varchar to datetime". The problem turned out to be that GetUser() in SQLMembershipProvider.cs was passing the following parameter: @CurrentTimeUtc='2009-03-18 17:57:48.0770000'. The problem is in the
extra zeros on the right. If I take them out, the query succeeds.
Does anyone know what's going on? This is all Microsoft provider
code and Microsoft ASP (asp:PasswordRecovery) code that has not changed as far as I know. I'm running .Net 3.5 on the web site.
Mar 20, 2009 08:11 AM|Wencui Qian - MSFT|LINK
Could you please show us the SQL query in SQL Server Profiler? Please show us more details so that we can help you better.
Apr 15, 2009 05:31 AM|Jameslock|LINK
Not sure if this helps but i had the same error when running Membership.CreateUser. I was only passing username and password, and it gave me the abovce error. I
then changed it to also provide the email address (I use email address as username so username and email are always the same), and it worked.
As i said not sure if this helps, but it might help track down where the issue is comming from.
BTW, im now having a similar problem when calling Membership.ValidateUser and thus 'aspnet_Membership_GetPasswordWithFormat'. The only thing i've done
differently is install SQL2008 and attached my sql2005 database. I'm debugging so i'll let you know if i find anything
UPDATE: Turns out the issue causing the error was something else, BUT this is what was throwing me: Profiling with SQL2008 must assume that dates passed into procedures which are declared with datetime are interpreted as datetime2 by default i.e.:
(Profiler output snip) ,@CurrentTimeUtc='2009-04-15 06:40:17.5730000'
So when i copy and paste into sql analyser it gives the "Error converting data type varchar to datetime" error because the procedure is declared with: '@CurrentTimeUtc
datetime,' and thus causing an overflow as datetime2 is more precise (up to 7 decimal places compared
to 3 for datetime). So the error is only caused by a difference in interpretation by profiler 2008 and procedures declares declared with 2005 datetime object. The initial issue probobly has nothing to do with the varchar conversion error (at least in my
Apr 15, 2009 05:22 PM|harafeh|LINK
My real problem turned out to be that when we first created our membership provider we chose hashed passwords. Later on, we changed to encrypted passwords. We thought we recreated all the old accounts but this one user account, at least, was not so it kept
its hashed password. So even though that user account can do just about anything, changing password was not one of them. The error message from the membership provider was completely useless.
To add insult to injury, the SQL shown by the profiler was not what exactly what the provider generated. It turns out that *all* SQL statements generated by the provider had the same fake problem. I don't know what the exact SQL generated looks like but
it all executes correctly and the error shown above is a red herring. I'm just guessing here, but I think the profiler was reformatting the sql and adding the extra zeros to the end.
To solve the "cannot change password" problem, we simply recreated that user account so it now has the correct type of password (encypted instead of hashed).
Many thanks to those who tried to help.