This project is read-only.

Where are users stored? Problems using MS SQL provider

Topics: ASP.NET 2.0, Business Logic Layer
Jan 22, 2014 at 8:29 PM
I apologize for the length but I felt it was all important. I hope someone will have the patience to help me. The second set up bullets is an exercise to help me try to debug why my hosted BE isn't allowing me to login using a SQL backend.

Here's what I did first:
  • Got BE for version 2.9
  • Deployed to my webserver (on Arvixe .NET hosting)
  • Successfully logged in as Admin user, created a post, created a new user, logged in as that user multiple times. Changed my theme to something other than standard.
  • Wanted to use MS SQL as the backend instead of XML files. So I ...
  • Created a database on my hosting provider, tested remotely using query analyzer and was able to connect
  • Used the file "MSSQLSetup2.9.0.0.sql" to successfully create and populate the database.
  • Followed instructions to alter the web.config (see end of this post for example) to use SQL rather than XML
  • Brought up my blog website in Firefox browser and could instantly see the theme had reverted back to standard - Good, I thought. It's really pointing to SQL now.
  • Was unable to login using ANY of the users I had previously used. I assumed SQL must have a user table that was reset to defaults. Made sense but I wondered why I couldn't log in using "admin" "admin".
  • Followed instructions on resetting the admin user in XML. Still couldn't login. Followed instructions on resetting the admin user in SQL. Still no luck.
  • Reverted the web.config back to when it was using XML and saw my other theme come up (good) and could log in again (also good). Put the web.config back to pointing to SQL and couldn't log in as admin (or any other user I had used with XML).
So, I figured I might as well look at the code.

From here on to the end of the post I'm working locally only.

I have loaded BE version 2.9 source code in Visual Studio 2012. Runs fine as long as I use chrome (IE bombs on a bunch of JSON stuff). That's fine. So, my local configuration (using Visual Studio's cassini, I'll add) is the default one where the web.config is using only XML for the backend. Works great. I logged in, created a user.

I then create a local MS SQL database for BE. I edit my local web.config to use MS SQL for the backend. I login as "admin" "admin" just fine. I create a new user named "testSqlUser1" with the "Edit" role. Here are the odd things I can't figure out:
  • I click on the "Users" button, thinking I'm only going to see "Admin". Because I'm pointing to SQL now. However, I also see the user I created when pointing to the XML backend in addition to "Admin" and "testSqlUser1" (not expected)
  • If I use query analyzer to select from dbo.be_Users I only get one row (for admin) and don't even see user "testSqlUser1". (not expected)
  • In query analyzer, I do however see my post I made when pointing to SQL (expected).
  • If I look in the App_Data folder's "users.xml" file I can see all users in that file - even the ones I added through BE when I was certain I was pointing to SQL (I know I was because the new post I created did in fact end up in SQL's dbo.be_Posts table and did not end up in an XML file). (not expected)
If I turn MS SQL profiler on I can see that when logging into BE (web.config still using SQL backend) in it's not querying SQL at all. Same for pulling up the initial page, for bringing up the list of users. When adding a new user it does access SQL but not to insert the user - but only to look up data for be_Rights and be_RightRoles.

Am I doing something wrong with my web.config or perhaps just not understanding how the application is supposed to use data? I initially expected that when the web.config was configured to use a MS SQL provider it would get and save everything to SQL except for attachments and images which would still be stored in the file system as XML. Here is part of my web.config when I thought I had it properly using a MS SQL backend:
    <blogProvider defaultProvider="DbBlogProvider" fileStoreProvider="XmlBlogProvider">
        <add description="Xml Blog Provider" name="XmlBlogProvider" type="BlogEngine.Core.Providers.XmlBlogProvider, BlogEngine.Core" />
        <add connectionStringName="BlogEngine" description="Sql Database Provider" name="DbBlogProvider" type="BlogEngine.Core.Providers.DbBlogProvider, BlogEngine.Core" />
    <blogFileSystemProvider defaultProvider="XmlBlogProvider">
        <add description="Xml Blog Provider" name="XmlBlogProvider" type="BlogEngine.Core.Providers.XmlFileSystemProvider, BlogEngine.Core" />
        <add storageVariable="BlogEngine" description="Sql Database Provider" name="DbBlogProvider" type="BlogEngine.Core.Providers.DbFileSystemProvider, BlogEngine.Core" />
    <clear />
      <add name="BlogEngine" connectionString="server=(local);database=blog;Trusted_Connection=True;" providerName="System.Data.SqlClient"/>
Jan 22, 2014 at 9:49 PM
Membership and roles have own providers.

You need to modify connection string to point to your database and switch from XML to DB provider in these 3 places:
  1. defaultProvider="XmlBlogProvider" -> defaultProvider="DbBlogProvider"
  2. membership defaultProvider="XmlMembershipProvider" -> membership defaultProvider="DbMembershipProvider"
  3. roleManager defaultProvider="XmlRoleProvider" -> roleManager defaultProvider="DbRoleProvider"
Jan 22, 2014 at 10:20 PM
Thanks very much! Cool. Sorry about my misunderstanding. That document spells it out perfectly. I was using some other instructions I found online and I don't think they mentioned the other 2 places.