Using AspNetSqlMembershipProvider with BlogEngine.NET?

Topics: ASP.NET 2.0
Jan 3, 2008 at 11:05 PM
Edited Jan 3, 2008 at 11:07 PM
I'm looking at using BlogEngine and plan to use the SQL Server Blog provider as for this particular application we aren't allowed to write files to the server.

Am I correct that the simply using the SQL Server Blog provideer does not mean that membership data is also stored in the database? I don't see any user tables being built in the provided SQL setup scripts. It appears that the membership provider would still be XmlMembershipProvider as specified in the Web.Config.

This is a problem for us as our application won't be able to write to a users.xml file either.

If this is the case, how easy/difficult would it be to use the AspNetSqlMembershipProvider with BlogEngine? Has anyone done this? Would I need to make any changes to the BlogEngine code or simply set up the AspNetSqlMembershipProvider as I would normally do and configure Web.config accordingly?

Does anyone have any direct experience with this? How about any educated guesses?

Thanks.

One other question. If we do use SQL Server Blog provider and AspNetSqlMembershipProvider does this eliminate all writing to xml files on the server? Or is there some other piece of this application that needs to write to xml files?








Jan 4, 2008 at 1:31 AM
Hats off to mads and the team. Yes it is possible to change to the ASP.NET Membership Provider. You just need to comment out these lines in your web.config :

<membership defaultProvider="XmlMembershipProvider" >
<providers>
<clear />
<add name="XmlMembershipProvider" type="BlogEngine.Core.Providers.XmlMembershipProvider, BlogEngine.Core" description="XML membership provider" xmlFileName="~/App_Data/users.xml"/>
</providers>
</membership>

<roleManager defaultProvider="XmlRoleProvider" enabled="true" cacheRolesInCookie="true" cookieName=".BLOGENGINEROLES">
<providers>
<clear />
<add name="XmlRoleProvider" type="BlogEngine.Core.Providers.XmlRoleProvider, BlogEngine.Core" description="XML role provider" xmlFileName="~/App_Data/roles.xml"/>
</providers>
</roleManager>

Replace them with :

<membership defaultProvider="SqlMembershipProvider">
<providers>
<add name="SqlMembershipProvider"
type="System.Web.Security.SqlMembershipProvider"
connectionStringName="BlogEngine"
applicationName="JwendlBlog"
minRequiredPasswordLength="5"
minRequiredNonalphanumericCharacters="0" />
</providers>
</membership>

<roleManager enabled="true" defaultProvider="SqlRoleProvider">
<providers>
<add connectionStringName="BlogEngine" name="SqlRoleProvider"
applicationName="JwendlBlog"
type="System.Web.Security.SqlRoleProvider" />
</providers>
</roleManager>

Then you need to install the ASP.NET Membership SQL Package : C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe
Then you go through the steps to set it up on your database.

That was almost flawless. The major issue is then opening up WSAT and setting up your web appliation with a user and role. The only problem is that you need to make sure that your blog applicationid maps to the role's appliationid and that your user exists in that role. I personally had to go into sql and modify the aspnetUsers table and the aspnetRoles table and the aspnet_UsersInRoles table. When I fixed all of that to have entries for my application to allow for one default user, everything went perfectly then.

This is all because the XmlMembershipProvider class that mats et al. made inherits MembershipProvider (like a good application should). This gives you the freedom to chose your own MembershipProvider or specify a custom one.

I have it running on my site and it runs perfectly.
Jan 4, 2008 at 2:13 AM
There are still a few things left though if you want to make this thing work without xml.

In ~\App_Data\log folder you still have the referrer log, so if you can disable that (or change it somehow to sql) then you are fine
In ~\App_Data\ there is the Extensions.xml or whatever for the ExtensionManager, this one right now is not able to use sql server either as it primarily uses the XmlDocument class to write it's settings out to disk.
Also the blogroll.xml file is written to and not stored on sql.
And same with the ping servers.

So if you can figure out those final issues then you should be able to run purely off of sql with BlogEngine.
Jan 4, 2008 at 10:39 AM
What I have found by trimming the contents of the App_Data folder is that the following must remain:

Folders: files and log
Files: blogroll.xml, extensions.xml, and stopwords.txt (latter is read-only I think)

Evertyhing else seems to be fine removing.
Coordinator
Jan 4, 2008 at 1:29 PM
I just recorded a few screencasts earlier this week and this was the topic of one of them. (I think the SQL Membership one will likely get posted Sunday or Monday.)

Regarding the remaining files, I know we expect to move the blogroll and extensions data into the provider for the next release. I'd guess the referrers data will come along with it although I haven't discussed it specifically with anyone on the team.
Jan 4, 2008 at 2:57 PM
Anyone have an estimate of the difficulty of trying to avoid writing to the xml files that jwendl and lvildosola before the next release that RazorAnt talks about?

Having not used this product before I'm not sure how much re-working of the plumbing might be involved.


Thanks.
Jan 25, 2008 at 10:19 PM
Look I am needing some major help in this one I have followed the instructions above and this is what I am getting


When I try to login

EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'blog', schema 'dbo'.
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: EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'blog', schema 'dbo'.

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): EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'blog', schema 'dbo'.
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +862234
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739110
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +903
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Web.Util.SecUtility.CheckSchemaVersion(ProviderBase provider, SqlConnection connection, String[] features, String version, Int32& schemaVersionCheck) +367
System.Web.Security.SqlMembershipProvider.CheckSchemaVersion(SqlConnection connection) +85
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) +1121
System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105
System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42
System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83
System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160
System.Web.UI.WebControls.Login.AttemptLogin() +105
System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99
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) +163
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


Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832
Jan 26, 2008 at 11:35 AM
Edited Jan 26, 2008 at 11:45 AM
The user that authenticated to the database is not configured correctly. It does not have permission to execute the stored procedures. Are you using Impersonation all the way to the database or are you using a single Identity from your Application Pool to SQL Server to authenticate? In either case, whatever that Identity is you will need to make sure that it has db_datareader and db_datawriter roles. If that fails, then try adding the db_ddladmin role to it.

BTW, if you find that you need to assign the db_ddladmin role to that user it indicates that you should really be granting execute rights to that user for all the stored procedures, instead of assigning the db_ddladmin role. That is really the way to secure it. However, you may opt to do it simpler by assigning the db_ddladmin role only and be done with it, if you are not so concern about tightening security.

You may want to take a look at the page I added to the documentation about this: Using ASP.NET 2.0 SQL Membership and Role Providers.
Jan 27, 2008 at 7:49 AM
Okay, so I don't want to gripe too much, but hopefully, you will take this as constructive criticism. I am extremely frustrated right now after having spent 4+ hours trying to get the SQLMembership provider working. I am not even certain I have resolved the issues but at least I've gotten past the inability to connect to file, blah, blah, etc...

I appreciate your efforts in keeping these types of projects going, but honestly, most people are going to give up if you don't do a better job of explaining how to connect to a SQL Server instance (Express or otherwise). I'm not suggesting you turn these install instructions into tutorials of IIS, SQL Server, or anything else, but you are assuming entirely too much from the readers. Everyone has a different level of experience with the 8000 possible technologies that affect ASP.NET application security, and you simply must do a better job at providing step-by-step instructions. Most people are going to want to:

1) Avoid XML files
2) Use either SQL Express or SQL Server
3) Configure either a local or domain account with a dedicated Application Pool.

Your instructions need to incorporate those requirements - and so far, nothing does. I have seemingly gotten my instance to work, but I wouldn't have the first clue about whether I can repeat this process successfully, NOR could I provide instructions to a hosting provider to do the same. I know time is limited, but the amount of information is disappointing to say the least. The focus should be on using the application or learning from the application - it shouldn't take 4+ hours just to get a database connection. Please stay focused on the basics and don't get ahead of yourself.
Jan 27, 2008 at 9:14 AM
Ok first things first.

I would like to say thank you to your team for providing us with this wonderfully written code. I for one am at the level at which I try to imitate good written code so that I can learn better. That being said I did get this working mainly after watching these two videos

http://nyveldt.com/misc/BE13SQLBlogProvider.html
http://nyveldt.com/misc/BE13SQLMembership.html

It was a bit of a pain to do but this script is still in its infancy stages so I completely understand. I see what the road map is so I have no complaints.

As far as the previous posters comments the only thing I could agree with him on is the Wiki shows information regarding the last release of BE not the current one. Mainly speaking of the portion that talks about creating extensions. It shows that was written on Dec1,07 when this current version came out on Dec 20 something. That being said it would be nice for that type of documentation to be up dated but as far as things outside of BE that is best left as giving the broad explanation and then providing the discussion section for any additional issues one may have. This gives the developers more time to work on more important things like documentation of the actual code and coding new goodies for us to play with. At some point the line has to be drawn as to how far documentation is going to go. Giving tutorials on how SQL Server works and what permissions you need for tables and what not is best left to the one off basis. People should know by now that these are the broad range areas of discussion that are generally caused by either in my case a little of lack of knowledge of SQL Server and Brain freeze moments and seeing as how they are so broad ranged they are left as discussion topics that one should search for and if not found then to post a question about. These guys are not making money off of giving this to us for free they are doing this for the challenge of doing it and god bless them for it. So yes I do understand your frustration about not having a quick answer to your questions but someone that is not paying for a service shouldn't expect service when they want it but instead when it becomes available and request it politely.


I am sorry if this last part comes across harsh it is just that I have been waiting for OS MS based projects to start catching on for quite sometime now and now that it is the best way to keep it going is to become a true community.

Thank you again BE Team.
Jan 27, 2008 at 9:44 AM
Hi satarter!

Did you know that Al Nyveldt has put together several step-by-step video tutorials on how to setup Blog engine with SQL Server? You can find them via the links below. Also check out his other BE blog articles.

Using the BlogEngine.NET SQL Server Provider Screencast
BlogEngine.NET Screencast: SQL Membership and Roles Providers

I'm a big fan of using SQL as a database (over XML). However, BlogEngine.NET has its roots in XML and not SQL. So a few things continue to remain in XML, like Blogroll, the referral log and widget data. And uploaded files are stored in the APP DATA directory. You can't use BE 100% without write access to APPDATA. However, stopwords and extensions settings have just recently been added to the SQL provider in the most recent checked-in source code. I suspect that trend will continue and over time write access to APPDATA will not longer be necessary.

One last thing, every IIS/SQL environment seems to be different based on various factors: SQL version (Express, SQL 2000, SQL 2005), IIS versions (IIS 6, IIS7, IIS6 on XP Pro), server in a domain or stand alone, SQL connection type (TCP/IP or named pipes), SQL security (standard or integrated), etc. which tend to make using SQL complicated from the start. So for those that are not familiar with SQL, I recommend using the (default) XML provider - it's much simpler to use and deploy. And for now, the XML provider is faster and uses less memory than using the SQL provider (if you count SQL Server instance + database cache). And since BE loads the entire database of posts, comments, etc. in memory anyway, SQL does not make BE scale up any more than XML. This is not a big deal for small to medium size blogs since the memory usage is so minimal.

I hope this help!
Jul 30, 2008 at 12:16 PM
I'm having a little trouble getting 1.4 to work with an existing web application which is already using the aspnet membership provider.

I've changed the applicationName keys to match those in aspnet_Application. BE seems to let me log in, and the WSAT shows all my users and roles, but BE is not picking up the roles relationship properly. I changed...

<add key="BlogEngine.AdminRole" value="Administrator"/>

...key because the default value was "AdminsistratorS" with an S... I assume this has to match the name of the role from aspnet_roles that I wish to use as the admin? Or am I wrong here.

Any help MUCH appreciated.

Thanks.
Jul 30, 2008 at 3:42 PM
By the way - is there a possibility to edit the SQL membership tables remotely?
Jul 30, 2008 at 7:43 PM
If you have VS.NET IDE, you can edit the membership tables via their Site Admin tool
Jul 30, 2008 at 10:24 PM
And what if I don't have?