What happens behind the scenes when running aspnet_regsql?

Question

I didn't get any responses from the asp.net forum, so I thought I'd try here.

I had an issue with a client where they were having trouble logging into our application that they are hosting. It was working fine for the first day or so after they initially installed our app, but then authentication broke on the following day (5/29/2009). Then they said it started working the following Monday, but only for a few hours. There were no configuration changes, or database changes that they knew of.

We were able to fix the issue by running the aspnet_regsql command with the parameters below. We had verified that all the "aspnet_" tables and stored procedures were in the database. My question is, where was the disconnect with our app and the database where authentication was failing, but was fixed after running this script? The command didn't overwrite any of the "aspnet_" tables, because all the existing users were still in the database, and they were able to once again log in with the same existing credentials.

aspnet_regsql -E -S <SERVERNAME> -d <DATABASENAME> -A all

A little background on our app. This is a .NET 3.5 SP1 ASP.NET application, using the membership provider for authentication. I've installed the membership features in our database (which I now regret doing). The application uses a SQL Server login for both the ADO.NET connection and the membership connection. This current setup has the login permissions set as the db_owner for this database.

The entry below was getting entered into the Windows event log.

 Event Type: Information
 Event Source: ASP.NET 2.0.50727.0
 Event Category: Web Event 
 Event ID: 1315
 Date:  5/29/2009
 Time:  10:52:04 AM
 User:  N/A
 Computer: * * * * * *
 Description:
 Event code: 4005 
 Event message: Forms authentication failed for the request. Reason:
 The ticket supplied has expired. 
 Event time: 5/29/2009 10:52:04 AM 
 Event time (UTC): 5/29/2009 2:52:04 PM 
 Event ID: 5ca57075c5464bdfbee4dbaa1365f8d9 
 Event sequence: 2 
 Event occurrence: 1 
 Event detail code: 50202 

 Application information: 
     Application domain:
 /LM/W3SVC/1/Root/5500Client-1-128880823233521436 
     Trust level: Full 
     Application Virtual Path: /5500Client 
     Application Path: D:\www\5500Client\ 
     Machine name: * * * * * *

Answer 1

It appears to be a problem with using the built-in Windows credentials for connecting to the SQL Server. Any chance your client could run this with e.g. the sa account or some other SQL Server account that has enough privileges to create database and tables??

aspnet_regsql -S <SERVERNAME> -d <DATABASENAME> -A all -U (login id) -P (password)

Marc