BIG shout out to fellow ASPInsider Paul Glavich who saved my bacon on this one.

A while back I ran into an issue where membership info that I had saved to the ASPNETDB.mdf in my local site just didn't work after I moved the site to another location (i.e., to a different box). Turns out that since I had made some changes to provider details, and failed to explicitly specify an applicationName attribute, my Providers were all checking for logins/etc against a different appliation_id in the SQLEXPRESS database.

Scott Guthrie posted about it a while back.

So, imagine my surprise today when I ran into what seemed like the same thing: I moved my site, fired it up, tried to login, and bupkiss. Each time I'd log in, the page would just blink. Seemed to be EXACTLY the same behavior you'd get if your providers didn't explicitly specify an applicationName attribute. Only you can bet your sweet bippy I had explicitly specified that.

After yelling at my computer ("you lie!!!!1111oneoneone"), I posted an email. (Stepping in to the debugger I could see that the ApplicationName was indeed being handled correctly, but Membership.ValidateUser() was just never returning true. Worse, I could run copies of this site in VS 2005 and Cassini - I just couldn't run a copy of the site in a different IIS site listening for different headers.) Paul asked if both sites in IIS had the same perms. Well, duh... there it was. I had been so busy focusing on the ApplicationName goodness, that I had totally missed that.

The moral of the story? Actually, there are two:
1) Step away from the computer - rethink, it may not be what you think it is, etc.
2) The ASP.NET SQL Providers need to be SLAPPED for swallowing something HUGE like: "hey, dammit, I can't connect to the friggin' db because access to the 'attachable' SQLEXPRESS db is denied to NETWORK SERVICE."