Trusted Connections using Membership & Role Providers

Hi,

I've developed an application using VS.NET 2.0 with the ASP.NET Membership and Role Providers. These providers use the same connection string as my application -- that is, all of the aspnet_* tables, stored procedures, etc., are in the same database beside my application's tables, stored procedures, etc.

The application uses the standard ASP.NET 2.0 controls to access the Membership and Role Providers... Login, LoginStatus, etc, are used in the aspx pages and the standard Membership.GetUser() methods (as well as other methods in the Membership class) are used in the codebeside.

This application works fine in my personal development environment, while connected to SQLEXPRESS, or any other instance of SQL Server 2000 / 2005 with a copy of my data model. However, it has come time to move the application to a live development server, and a trusted connection is required for this move. Furthermore, identity impersonate must be used without hardcoding the userName and password into the web.config file.

Here is a sample of my web.config file the way it should look in the live development environment:

<connectionStrings>
    <add
      name="MyConn"
      connectionString="Server=MyServer;Database=MyDb;Trusted_Connection=True"
      providerName="System.Data.SqlClient"
    />
</connectionStrings>

<!--[code ommitted] -->

<system.web>
    <roleManager enabled="true" defaultProvider="MyProvider">
      <providers>
        <clear />
        <add connectionStringName="MyConn" applicationName="/MyApp"
          name="MyProvider" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
      </providers>
    </roleManager>
    <membership
      defaultProvider="MyProvider"
      userIsOnlineTimeWindow="15">
      <providers>
        <clear />
        <add
          name="MyProvider"
          type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
          connectionStringName="MyConn"
          applicationName="/MyAppc"
          enablePasswordRetrieval="false"
          enablePasswordReset="true"
          minRequiredNonalphanumericCharacters="0"
          requiresQuestionAndAnswer="true"
          requiresUniqueEmail="true"
          passwordFormat="Hashed"
          passwordStrengthRegularExpression="(?!^[0-9]*$)(?!^[a-zA-Z]*$)^([a-zA-Z0-9]{8,16})$"
        />
      </providers>
    </membership>
    <authentication mode="Forms" />

    <!--...[code ommitted]... -->

    <identity impersonate="true" />
</system.web>

When moved to the live development environment, there is unexpected behavior. This application should run as the default user of the web folder where it is published to in IIS, the same user that has datareader and datawriter access to my database on the live devlopment database server. And, when this user's userName and password are hard-coded into the identity tag in web.config, that's what happens.

When the web.config file is configured as above though, I get the following error:

Server Error in 'MyApp' Application.
Login failed for user 'MYDOMAIN\WebServer$'

(I have changed the name of the user to WebServer$, but I'm told by the DBA & SysAdmins that this user is the NT Authority on the web server Below is the stack trace, which is important).

<StackTrace>
[SqlException (0x80131904): Login failed for user 'MYDOMAIN\WebServer$'.]
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735203
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
   System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
   System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
   System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
   System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
   System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
   System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
   System.Data.SqlClient.SqlConnection.Open() +111
   System.Web.Caching.SqlCacheDependencyAdmin.GetEnabledTables(String connectionString) +114

[HttpException (0x80004005): Cannot get the tables enabled for SQL cache notification.]
   System.Web.Caching.SqlCacheDependencyAdmin.GetEnabledTables(String connectionString) +401
   System.Web.Caching.SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(String connectionString) +4
   ASP.global_asax.Application_Start(Object sender, EventArgs e) +164
</StackTrace>

Notice how this exception is raised from my global.asax.Application_Start() method. After commenting out all code from that method and republishing, I am able to get to the login page. After typing in a valid username and password that was added via the ASP.NET Membership Provider (to the aspnet_Users and aspnet_Membership tables) and clicking to login, I get the same error with the following stack trace:

<StackTrace>
[SqlException (0x80131904): Login failed for user 'MYDOMAIN\WebServer$'.]
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735203
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
   System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
   System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
   System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
   System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
   System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
   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) +84
   System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197
   System.Web.Security.SqlMembershipProvider.GetUser(String username, Boolean userIsOnline) +1491
   System.Web.Security.Membership.GetUser(String username, Boolean userIsOnline) +81
   System.Web.Security.Membership.GetUser(String username) +6
   login.cvUserNameLookup_ServerValidate(Object source, ServerValidateEventArgs args) +20
   System.Web.UI.WebControls.CustomValidator.OnServerValidate(String value) +132
   System.Web.UI.WebControls.CustomValidator.EvaluateIsValid() +111
   System.Web.UI.WebControls.BaseValidator.Validate() +86
   System.Web.UI.Page.Validate(String validationGroup) +143
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +81
   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) +5102
</StackTrace>

Before logging in the user, the method login.cvUserNameLookup_ServerValidate(Object, ServerValidateEventArgs) looks up the user-entered username in the databasee to make sure it exists (as a validation measure). Thus, part of this method calls Membership.GetUser(String) where String is the text entered by the user into the username text field (which is wrapped in the Value property of the ServerValidateEventArgs object).

I'm stating this because, when the overloaded Membership.GetUser() method is executed without any arguments, the same thing happens. When published to Local IIS on one of my fellow developer's computers, these same code segments trip up the same exception, however the error message indicates that login failed for user 'null'.

I really don't have any idea what's going on here. It seems like the user / process that is executing the web application is going out of scope, impersonating either NULL or the web server's NT Authority when it gets to a database call (which requires login). It also seems, sometimes, as if the application is impersonating the user logged in via the .NET 2.0 Membership Provider, which at first, is no one (can't get past the login page).

Can anyone tell me if the .NET 2.0 Membership Provider scope encompasses identity impersonate, and thus passes the currently-logged-in web application user as to the trusted connection string? If you have any questions, please post them and I will try to answer. In the meantime, I'm going to be doing some digging to see if I can isolate the problem.

Thanks!