Problem connecting to ASP.NET Membership services database

Question

I have been making some changes to an application that uses an ASP.NET Membership services database for authentication.

I can access the database locally using windows authentication, and using my SA user.

However, after moving the application to the prod. server, I can't access the DB at all.

I wrote a simple test-app to test my conn-strings.

AuthenticationConnectionString fails. AppDBConnString connects successfully.

I can log into management studio using the same user that I'm using in the conn-strings, can open both databases and execute queries successfully.

<add connectionString="Server=****\**;Database=FNAuthentication;User ID=***;Password=***" name="AuthenticationConnectionString" />
<add connectionString="Server=****\**;Database=AppDB;User ID=***;Password=***" name="AppDBConnString" />

The error message I'm getting is:

[SqlException (0x80131904): Cannot open database "FNAuthentication" requested by the login. The login failed.
Login failed for user '***'.]
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +6244425
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +245
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2811
   System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +53
   System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +248
   System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +6260362
   System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +6260328
   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +354
   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +703
   System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +54
   System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +6261592
   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +81
   System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +1657
   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +88
   System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +6265031
   System.Data.SqlClient.SqlConnection.Open() +258
   System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +82
   System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +3986458
   System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +3053172
   System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +213
   System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +164
   System.Web.UI.WebControls.Login.AuthenticateUsingMembershipProvider(AuthenticateEventArgs e) +75
   System.Web.UI.WebControls.Login.AttemptLogin() +152
   System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +124
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +70
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981

The error message leads me to think that there is something wrong with the permissions on the database, but I have no idea as to what needs changing.

We tried enabling all roles, but to no avail.

All help is much appreciated!

Regards

Francis

Answer 1

I think you should check the membership provider and role manager provider in your web.config and set it to your connection string

<membership defaultProvider="MyMembershipProvider" userIsOnlineTimeWindow="30">
        <providers>
            <add connectionStringName="MyConnectionString" applicationName="MyApp" enablePasswordRetrieval="true" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" passwordAttemptWindow="5" minRequiredNonalphanumericCharacters="0" minRequiredPasswordLength="5" passwordFormat="Clear" name="MyMembershipProvider" type="DBIndependent.Providers.SqlMembershipProvider"/>
        </providers>
    </membership>
    <roleManager enabled="true" defaultProvider="MyRoleProvider">
        <providers>
            <add connectionStringName="MyConnectionString" applicationName="MyApp" name="MyRoleProvider" type="DBIndependent.Providers.SqlRoleProvider"/>
        </providers>
    </roleManager>

Answer 2

Thanks!

The connectionstring is already set for membership and roleManager.

<membership defaultProvider="ASPSqlMembershipProvider" userIsOnlineTimeWindow="15">
            <providers>
                <remove name="AspNetSqlMembershipProvider" />
                <add name="ASPSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="AuthenticationConnectionString" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" passwordAttemptWindow="10" minRequiredPasswordLength="5" minRequiredNonalphanumericCharacters="0" passwordStrengthRegularExpression="" />
            </providers>
        </membership>
        <roleManager defaultProvider="ASPSqlRoleProvider" enabled="true" cacheRolesInCookie="true" cookieName=".ASPXROLES" cookiePath="/" cookieTimeout="30" cookieRequireSSL="false" cookieSlidingExpiration="true" createPersistentCookie="false" cookieProtection="All">
            <providers>
                <remove name="AspNetSqlRoleProvider" />
                <remove name="AspNetWindowsTokenRoleProvider" />
                <add connectionStringName="AuthenticationConnectionString" applicationName="/" name="ASPSqlRoleProvider" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
            </providers>
        </roleManager> 

My problem is that I can't log onto the db at all.

I wrote the following test-app to test the connections:

namespace TestConnStrings
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            using (SqlConnection testConn0 = new SqlConnection())
            {
                testConn0.ConnectionString = ConfigurationManager.ConnectionStrings["AuthenticationConnectionString"].ConnectionString;
                try
                {
                    testConn0.Open();
                }
                catch(Exception ex)
                {
                    Response.Write(ex.ToString());
                }

                if (testConn0.State == System.Data.ConnectionState.Open)
                {
                    Response.Write("AuthenticationConnectionString works");
                    testConn0.Close();
                }   
            }
            Response.Write("<br /><br /><br /><br /><br />");
            using (SqlConnection testConn1 = new SqlConnection())
            {
                testConn1.ConnectionString = ConfigurationManager.ConnectionStrings["AppDBConnString"].ConnectionString;
                try
                {
                    testConn1.Open();
                }
                catch (Exception ex)
                {
                    Response.Write(ex.ToString());
                }

                if (testConn1.State == System.Data.ConnectionState.Open)
                {
                    Response.Write("AppDBConnString works");
                    testConn1.Close();
                }
            }
        }
    }
}

AppDBConnString connects successfully, AuthenticationConnectionString does not.

Regards

Francis :)