authentication problem with SQL 2000

Hello guys... I have been struggling for quite some time trying to have the authentication page on my website work :-( and I'm now desperate for some help.

I have used VWD to create a website and I have a login.aspx page that authentiocate users based on their credential... all of it created with the web admin tool.
Now guess what! I have an instance of SQL 2005 running on my PC and it all works smoothly (users are screened and redirected to pages according to their status) but when I'm uploading the website to the company's server which is only authorized to run on SQL 200 I get the following error message:

Server Error in '/newmedical/webtools' Application.

---

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +684883 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +207 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +890 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +601 System.Data.SqlClient.SqlInternalConnectionTds..ctor(SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +159 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +108 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +445 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +304 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +85 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +126 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +239 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved) +815 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +80 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +78 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +161 System.Web.UI.WebControls.Login.AttemptLogin() +94 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +101 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +134 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5670

Does ASP.net website management (for roles and membership) create a db: ASPNETDB.mdf ( for user credentials) only accessible by SQL server 2005? If that's the case then no wonder the connection is timing out since the company's server runs on Sql 2000 Entreprise. I can't install SQL server 2005 on the server. Any idea to correct that problem?

Thanks in advance for your tips and advices
JY

Jeewai:

Does ASP.net website management (for roles and membership) create a db: ASPNETDB.mdf ( for user credentials) only accessible by SQL server 2005? If that's the case then no wonder the connection is timing out since the company's server runs on Sql 2000 Entreprise. I can't install SQL server 2005 on the server. Any idea to correct that problem?

No, you can use aspnet_regsql.exe (should be in C:\WINDOWS\Microsoft.NET\Framework\v2.0.xxxx\) to created the database objects required by the membership in any SQL database. Certaintly you can use SQL2000, however you have to do some configuration. Please take a look at this article:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

|||

Thanks so muchIori_Jay for your link to Scott's weblog. I really appreciate the help.
OK, I have run the aspnet_regsql to change my Application Services to use Sql Server 2000 (not Express) on the company's webserver and modified the web.config file to point the machine config file look up the SQL 2000 db for user credentials. I run VWD locally and the application runs fine and is able to connect to the remote company's server to authenticate users.(the newly created tables...aspnet_member and so on... in the webserver db are getting the credentials I've set up using the asp.net web admin tool)

Now after ftping the app to the company's webserver (running SQl 2000) and trying to login on the browser this time I get :

Server Error in '/newmedical' Application.

---

Procedure 'aspnet_Membership_GetPasswordWithFormat' expects parameter '@.UpdateLastLoginActivityDate', which was not supplied.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Procedure 'aspnet_Membership_GetPasswordWithFormat' expects parameter '@.UpdateLastLoginActivityDate', which was not supplied.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Procedure 'aspnet_Membership_GetPasswordWithFormat' expects parameter '@.UpdateLastLoginActivityDate', which was not supplied.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +786258 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +684870 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +207 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1751 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +37 System.Data.SqlClient.SqlDataReader.get_MetaData() +58 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +213 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +570 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +134 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +62 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved) +815 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +80 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +78 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +161 System.Web.UI.WebControls.Login.AttemptLogin() +94 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +101 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +134 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5670

Do I have to runaspnet_regsql.exe on the web server or check the .NET framework version which my may not be the same as the one in my development environment? I'm at a loss and any tips would be greatly welcome.

Thanks again in advance for your help. I do believe that others may have encountered the same error and then any help would benefit us all.

Jeewai

|||The new error is a .NET error, not related to the database schema. It indicates to excute the 'aspnet_Membership_GetPasswordWithFormat' stored procedure, you need to give values to the parameters. Can you find where you call the 'aspnet_Membership_GetPasswordWithFormat' procedure? Or it is implictly called by some control?|||

Success ! I found out the technical glitch that was preventing the authentication on the company's webserver. Indeed I updated the webserver's framework to version :v 2.0.50727 which was the one used in my development environment. Then I ran the aspnet_reg utility on the webserver to update the tables and voila...the faulty aspnet_Membership_GetPassword... was fixed and now the application is running wonderfully.

So I hope this short explanation will help a lot of struggling programmers regarding the aforementioned issue. Thanks again to Iori_Jay.

JY

Attribute Hierachies

Hi there. I have a problem that I have been struggling to understand. Here is the situation. I will use a time dimension just as an example.

If I build a time dimension containing 2 Years of dates from 2005 to 2006 with 3 attributes: Year, Month, Date and set my attribute relationships as Date ->Month and Month -> Year. In management studio I can drag over Month and then Year (in that particular order ) and it will give me the correct results. For example if I expand the Month of January 2006 the year column will show 2006. Now If I filter the year column for the year of 2006, you would expect that the month column would only contain the months of 2006, but it doesn't. It still has months for 2005.

Now if I rebuild that dimension setting the attribute relationships to the key. I.e. Date ->Year, Month. And no attribute relation to month. I can rebuild the cube and drag over the same 2 colums, Month and Year (in that order) and then filter on the the year column for 2006 and then the Month column will show only the months for 2006.

Whats going on here?

I would check to make sure you have the total heirarchy structure as part of your key.

IE: Date should have a key of (Year --> Month --> Date).

|||

I'm not quite sure what you mean since date is the key.

My user hierarchy for the first scenario is:

Year

Month

Year (year is the attribute relationship)|||

I was able to recreate this scenario using Adventure Works. Seems to me this should work, but it behaves as you described. Here's what I did:

First, I created a new dimension called Date Test 1 that had three attributes -- Date, Month, Year based on the DimTime dimension table. For this dimension, I left Month and Year related to Date (the default). The Month attribute had members like "January 2002", "January 2003", etc. while the Year attribute had members like "CY 2002", "CY 2003", etc. I included this dimension in the Adventure Works cube, relating it to the Internet Sales measure group (joining from the Date attribute to the OrderDateKey column in the underlying fact table). After deploying and processing the updated project, I browsed the cube. From the Date Test 1 dimension, I put Month on rows and got each month listed. I then added Year to the filter area (not the subcube filter area, but the normal page filter area. I selected only "CY 2003" from the list of years and the list of months was indeed filtered to just those in 2003. Note that this was done without putting any measures in the data area -- just the dimension data. This works as I would expect it to.

Second, I created another new dimension called Date Test 2 that had the same three attributes with the same definitions. However, in this test, I moved the attribute relationship for Year from the Date attribute to the Month attribute. Thus, Date had a single attribute relationship to Month and Month had a single attribute relationship to Year. I again added this new dimension to the Adventure Works cube, relating it in the same manner as Date Test 2. Then I did the same browsing test. In this case, adding Month on rows to begin with gives the same list of all months across years. But, adding Year to the filter area and selecting "CY 2003" did NOT filter the list of months displayed on rows. Again, this was done without any measures in the data area (as was the first test above).

A few interesting observations:

1. I turned on SQL Server Profiler and captured the MDX queries being generated. As far as I can tell, they look exactly the same. They query in the browser window results in a few different queries that look something like this:

CREATE SESSION
SET [Adventure Works].[{39001120-29B1-4733-BBBA-C2BA3B63A05B}Pivot21Axis1Set0] AS
'
{
{ [Date Test X].[Month].[All] },
AddCalculatedMembers([Date Test X].[Month].[Month].MEMBERS)
}
'

SELECT
NON EMPTY [{39001120-29B1-4733-BBBA-C2BA3B63A05B}Pivot21Axis1Set0]
FROM [Adventure Works]
WHERE
(
[Date Test X].[Year].&[2003]
)

2. In the browser window, if I instead add the Year attribute to the subcube filter area (instead of the page filter area in the pivot area itself), things work as expected. For both dimension tests, the list of months displayed on the rows after adding the Month attribute is filtered when a specific year is selected for the Year attribute filter.

I suspect that this has something to do with how the query engine determines what attribute combinations actually exist (ie, the autoexists feature of the engine). It seems to be able to determine that the months of a given year are the only months that exist when both attributes are related to the key attribute for the dimension. But it doesn't seem to be able to determine this in a scenario where the relationship between the attributes is above the key attribute for the dimension.

I would consider this to be a bug myself. Anyone from Microsoft care to comment (Mosha?)

HTH,

Dave Fackler

|||

Thanks for taking the time to go through this. Another thing to note is that the same situation occurs when both attributes are related to the key attribute and then you try to filter the dates by the month.

I suspect that this is a bug as well. Any other comments?

Thanks.