Using an existing aspnet user database with postgreSql

[Uhanalainen]

So I've been battling with this for a couple of days now, and I can't figure out what's wrong. The project is running on MVC3 and Visual Studio 2012.

We have an existing user database in MS SQL Server, which I have converted to postgres using Spectral Core Full Convert-tool. Right now my goal is to manage to get the project running locally.

Here's parts of my web.config file

Code: Select all

<add name="PgSqlServices" connectionString="host=localhost;user id=postgres;password=abc123!;database=AspNetDb" providerName="Devart.Data.PostgreSql"/>

<system.web>
<membership defaultProvider="PgSqlMembershipProvider">
      <providers>
        <clear />
        <add name="PgSqlMembershipProvider" type="Devart.Data.PostgreSql.Web.Providers.PgSqlMembershipProvider, Devart.Data.PostgreSql.Web, Version=7.4.497.4, Culture=neutral, PublicKeyToken=09af7300eec23701" connectionStringName="PgSqlServices" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/"/>
        <!--add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" /-->
      </providers>
    </membership>
    <profile>
      <providers>
        <clear />
        <add name="AspNetPgSqlProfileProvider" type="Devart.Data.PostgreSql.Web.Providers.PgSqlProfileProvider, Devart.Data.PostgreSql.Web, Version=7.4.497.4, Culture=neutral, PublicKeyToken=09af7300eec23701" connectionStringName="PgSqlServices" applicationName="/" />
        <!--add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/" /-->
      </providers>
    </profile>
    <roleManager defaultProvider="AspNetPgSqlRoleProvider" enabled="true">
      <providers>
        <clear />
        <add name="AspNetPgSqlRoleProvider" type="Devart.Data.PostgreSql.Web.Providers.PgSqlRoleProvider, Devart.Data.PostgreSql.Web, Version=7.4.497.4, Culture=neutral, PublicKeyToken=09af7300eec23701" connectionStringName="PgSqlServices" applicationName="/" />
        <!--add connectionStringName="ApplicationServices" applicationName="/" name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" /-->
        <add applicationName="/" name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" />
      </providers>
    </roleManager>

If I run this right now, I get the error "relation \"aspnet_membership\" does not exist" in our BaseController.

Code: Select all

        protected BaseController(IUserFeatures featureData, ILoggingService loggingService)
        {
            this.FeatureService = featureData;
            user = Membership.GetUser(true);
            this.LoggingService = loggingService;  
        }

The error points to user = Membership.GetUser(true);

If I open the inner exception, there's this ErrorSql that says the following:

"SELECT username, u.userid, email, passwordquestion, comments, isapproved, islockedout, creationdate, lastlogindate, lastactivitydate, lastpasswordchangeddate, lastlockoutdate FROM aspnet_membership m, aspnet_users u WHERE u.userid=m.userid AND LOWER(u.applicationname) = $1 AND LOWER(username) = $2"

Any help would be greatly appreciated.

[Pinturiccio]

You define the Membership provider in the following way in your web.config:

Code: Select all

<add name="PgSqlMembershipProvider" type="Devart.Data.PostgreSql.Web.Providers.PgSqlMembershipProvider, Devart.Data.PostgreSql.Web, Version=7.4.497.4, Culture=neutral, PublicKeyToken=09af7300eec23701" connectionStringName="PgSqlServices" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/"/>

This definition is taken from the tutorial: https://www.devart.com/dotconnect/postg ... iders.html
However the provider version ends with *.4. According to the tutorial, you should use version 7.4.497.0 instead of 7.4.497.4. Additionally, the assembly of this 7.4.497.0 version must be present in the project references.

You also need to create the corresponding tables in the database. The DDL script of these tables is located in the folder where the provider is installed; by default it is C:\Program Files (x86)\Devart\dotConnect\PostgreSQL\Web\ASP.NET 2\InstallWebTables.sql . This script also creates the aspnet_membership table, which is mentioned in the error text.


There also is a tutorial for an MVC application that uses the Devart.Data.PostgreSql.Web.dll assembly of version *.4: https://www.devart.com/dotconnect/postg ... orial.html
In this tutorial the record in web.config is different, it is like the one from your another post: http://forums.devart.com/viewtopic.php?t=32525

The DDL script for the ExtendedMembership provider tables also is located in the provider folder: C:\Program Files (x86)\Devart\dotConnect\PostgreSQL\Web\ASP.NET 4\InstallExtendedWebTables.sql . However, it is not necessary to create them manually. They are automatically created if they are not present in the database.

[Uhanalainen]

Okay, I'll try changing the references, then, but it will not solve anything to create the tables if I can't get the data from the existing tables to the newly created ones. As I said, I have already converted our existing user database to postgres from MS SQL, so the tables are definitely there. It's just that the driver seems to be expecting all lowercased names on tables and fields. They're not capitalized like that in the original asp.net user database.

Edit: And, as expected, changing the references to .0 instead of .4 didn't work any better. I made a conversion of the existing user database with all lowercase characters instead. Now, the same line (user = Membership.GetUser) is throwing "Column /"comments"/ not found". there indeed is no "comments" column in aspnet_membership table, but there IS a column named "comment", why does it expect a pluralized version of this all of a sudden? And, if I change that column name to comments, the next error is "Column /"creationdate"/ not found", the closest I can find is a "createdate"-column.

Membership.GetUser is a method which, I would believe, should work consistently and it works well as long as it get's the information from the original SQL Server, but not from postgres.

[Uhanalainen]

I did create the table now with the .sql file that comes with the dotConnect driver, and now it's working.

But upon trying to add a new user, like this:

Code: Select all

Membership.CreateUser("juha", "abc123");

I get the following error: "Default Membership Provider could not be found"

My web config looks like this now:

Code: Select all

<system.web>
...
<membership defaultProvider="AspNetPgSqlMembershipProvider">
      <providers>
        <clear />
        <add
          name="AspNetPgSqlMembershipProvider" 
          type="Devart.Data.PostgreSql.Web.Providers.PgSqlMembershipProvider, Devart.Data.PostgreSql.Web, Version=7.4.497.0, Culture=neutral, PublicKeyToken=09af7300eec23701" 
          connectionStringName="PgSqlServices" 
          enablePasswordRetrieval="false" 
          enablePasswordReset="true" 
          requiresQuestionAndAnswer="false" 
          requiresUniqueEmail="false" 
          maxInvalidPasswordAttempts="5" 
          minRequiredPasswordLength="6" 
          minRequiredNonalphanumericCharacters="0" 
          passwordAttemptWindow="10" 
          applicationName="/" />

[Pinturiccio]

Now, the same line (user = Membership.GetUser) is throwing "Column /"comments"/ not found". there indeed is no "comments" column in aspnet_membership table, but there IS a column named "comment", why does it expect a pluralized version of this all of a sudden? And, if I change that column name to comments, the next error is "Column /"creationdate"/ not found", the closest I can find is a "createdate"-column.

Table and column names are hardcoded in our PgSqlMembershipProvider. If you want to use your tables with a Membership provider, you need to implement your own Membership provider.

I get the following error: "Default Membership Provider could not be found"

We could not reproduce the issue. Besides, the errors "Column /"creationdate"/ not found", "Column /"comments"/ not found" mean that Default Membership Provider was found, but there were errors when accessing non-existing columns.

Please create and send us a small test project for reproducing the issue. This can be a simple MVC 3 application with the web.config file customized for using dotConnect for PostgreSQL and executing the only line of code:

Code: Select all

Membership.CreateUser("juha", "abc123");