According to ScottGu, an ASP.NET membership provider that works with SQL Server Compact 4 is not coming in the near future. This poses a problem, if you would like to use Forms authentication with a website using only SQL Server Compact 4.
To remedy this, I have implemented an ASP.NET Membership provider for SQL Server Compact 4.0 (CTP1), for use with Forms Authentication for small web sites using only a single SQL Server Compact 4.0 database. The project provides files that contain a Membership provider and Role provider for ASP.NET. (Note that SQL Server Compact 4 is in beta, and is not supported for production).
How to use the provided files (3 simple steps)
1. Change SqlCeMembershipProvider::encryptionKey to a random hexadecimal value of your choice.
2. Copy the three files in the /App_Code folder to your web sites' ~/App_Code folder.
3. Modify your web.config using the template on the CodePlex page
(if you are on a shared hosting server, you will have to set writeExceptionsToEventLog to false).
That's it - you can now create users, roles and use the ASP.NET login controls.
Site file layout (to verify that the provider is working):
If you encounter any bugs, have suggestions or any other issues, please provide feedback here
This is awesome, now what about the DB Structure?
ReplyDeleteAlex: Not sure what you mean with this question...
ReplyDeleteHi Erik
ReplyDeletehow can i use this lib with ASP NET MVC?!
Braga: Same way as with WebForms... Modify web.config and add files to app_code (see the CodePlex site)
ReplyDeleteErik
ReplyDeleteASP NET MVC don't use App_Code (because they are basically Web Application Projects, not Web Site projects).
Where can I put the Provider Files?!
Anywhere in you project, or in a separate class library, and add a reference to this.
ReplyDeleteHi...
ReplyDeletei tried to call GetNumberOfUsersOnline() as follow.
ErikEJ.SqlCeMembershipProvider Member =new ErikEJ.SqlCeMembershipProvider();
this.OnlineUsersLabel.Text=Member.GetNumberOfUsersOnline().ToString();
i got the following exception
An SqlCeParameter with ParameterName 'LastActivityDate' is not contained by this SqlCeParameterCollection.
Mushin: I will look into it.
ReplyDeleteMushin: Fixed in latest source
ReplyDeleteErik, thank you for this excellent library.
ReplyDeleteHowever, I added a reference to the ErikEJ.SqlCeMembership.dll to my ASP.NET MVC project (avoiding to copy the *.cs files to the inexistent App_Code folder) but I keep getting a "Default Membership Provider could not be found." error.
Any hints
Config error?
ReplyDeleteHi Erik,
ReplyDeleteYes, it was just a Web.config error. I should have mistyped something.
Sorry for bothering you with such a nonsense thing and many thanks for your library.
I have an Asp.Net MVC 3 website that uses asp.net membership services for authentication. The membership database has been running on Sql Server Express 2008 and I am attempting to convert it to Sql Sever Compact 4.0.
ReplyDeleteFollowing the instructions here I have created the Sql Server Compact 4.0 database. I used the code from here to switch the membership provider in the MVC app.
Using the new provider and database I am able to successfully create new users and log in using the new user's password. However, I am not able to login to any of the users that previously existed in the Sql Server Express database. I am able to browse the new database and have verified that the users are all there, so I believe the problem is somehow related to not being able to successfully access the encrypted passwords that had been previously created. I really don't want to have all of the existing users change their passwords. Can anyone shed some light as to how I can make this happen?
Michael: Not a clue...
ReplyDeleteI've been able to successfully use your SQL CE Membership Provider with MVC 3, but when I also use EF CodeFirst and DbContext, the membership tables get deleted whenever one of my POCO classes change. Is there a trick to using your membership provider along with EF CodeFirst so that the provider tables don't get deleted?
ReplyDeleteTerry: How about using two database files?
ReplyDeleteThat could work. I'll give that a try. Thanks.
ReplyDeleteHi Terry ,
ReplyDeleteEven I used the DLL instead of classes, but I get this error:
Unable to connect to the database.
If you have not yet created the SQL Server database, exit the Web Site Administration Tool, use the command line utility aspnet_regsql to create and configure the database, and then restart the tool to set the provider
This code is also correct?
Hello Terry,
ReplyDeleteI have also used the dll instead of classes, I created the db and the visual studio filled with tables correctly.
But if I try to assign the provider configuration with Asp.Net returns me this error:
Unable to connect to the database.
If you have not yet created the SQL Server database, exit the Web Site Administration Tool, use the command line utility aspnet_regsql to create and configure the database, and then restart the tool to set the provider.
I would like to ask if the code in the web.config is the same even with the dll?
Leon: Same web.config, and just drop the dll file in the bin folder of your web site. Easiest solution is to use the NuGet package
ReplyDeleteI'm using VWD 2010 Express, and am having difficulty getting the membership provider to work. Is the problem with the Express edition or the wannabe programmer? There are 4 CS files in App_Code, the instructions say there are 3. The web.config file whines about an section, and of course the project won't run. Any suggestions would be greatly appreciated.
ReplyDeletebgt: There are 4 files now, will update instructions. Please post a issue at the CodePlex issue tracker and attach you web.config and the exact error messsage you are getting.
ReplyDeleteYou are my hero!
ReplyDeleteExcellent job!
Hi Erik,
ReplyDeleteThank you for the great tool. I have no problem with installing and getting it run nicely via NuGet.
Just a quick question, I have no chance to look at the code but does it use global session setting? I mean the we define in the web.config? Or it always use InProc? Or any mechanism to hold login session? I'm having trouble on a shared host atm.
Cheers,
Hugh.
Hugh: It only works on a single server, I think the membership fetaures are cookie based, but the database is only on a singel server.
ReplyDeleteHi Eric, totally confused by this Im not sure if im doing something wrong. I downloaded and put the dll into a project and followed all the instructions and run the project and I get a null reference exceptionon the Membership.CreateUser() method. Added to this when I use the classes I cannot click go to definition on the classes that the providers inherit from!
ReplyDeleteMy project is here
http://dl.dropbox.com/u/48829187/WebApplicationSqlCe.zip
Totally stumped, please help...
Regards
Pete
Peter; Suggest you use the NuGet package, it will update your web.config with required changes. (Or even better, use the MS supported new provider)
ReplyDeleteHey Erik, Ok got it working thanks for your help. I have another minor issue now I want to put your code in a DAL but getting conflicting versions. Wondered if this is possible?
ReplyDeletehttp://stackoverflow.com/questions/8262200/strange-error-in-using-erike-sql-membership-provider-unable-to-load-the-native
Great tool btw! :-)
Peter: Answer on SO, also maybe consider using the Microsoft provider instead? (Via NuGet)
ReplyDeleteI installed it and it JUST WORKS. Amazing. :)
ReplyDeleteNot only that - I was in the process of deploying an application to a web host, struggling with connection strings and configuring SQLEXPRESS. Now all the need for additional configuration is gone - I just copied the files to the web host and it just works again - hours saved. Thank you. :)
Hi ErikEJ,
ReplyDeleteI converted your code to VB.NET and it worked like a charm (very few ByRef changes and few guid trycast issue and one <> changed to IsNot). When i converted all the code in the class files to use EDMX of SqlCE, it does not connect to DB. Any idea? I'm trying to use in Silverlight 5.0.
Thanks in advance.
Venkat
RV: I do not think you can use EDMX with Silverlight 5.0 and a local database, but suggest you use the new MS provider going forward.
ReplyDeleteGreat work with this, really easy to implement! Just one question, where are the membership tables stored? I can't see them in my .sdf file like my other model tables (using MVC).
ReplyDeleteThey are stored in the sdf file that the provider creates in app_data folder.
ReplyDeleteYep sorry. They showed up after drop/recreating the table. Is there a way of assigning roles to users with this?
ReplyDeleteYou can use the WSAT (Web Site Admin Tool) from within VS to do that
ReplyDeleteHi Erik
ReplyDeleteyour tools are awsome
i just wona ask about how to use this membership provider with widows forms
thanks
I do not know, if that is posssible at all.
ReplyDeleteIt`s really great!! Super Thanks!
ReplyDeleteJust finding your blog and this post now. Great!
ReplyDeleteAt least I will be able to complete the MVC Music Shop Tutorial. I am going to blog about it in french, obviously linking to your post.
Thanks again.
Bernard
Thank, Bernard. Feel free to link to your post here in the comments. Notice that the MS Universal Provider also supports SQL CE 4.0
ReplyDeleteHi. I've been working with Visual Studio 2015 and Sql Server Express, now I'm doing my executable with database included but with the mdf, to install it on another machine you need sql server express or just slqlocaldb?
ReplyDelete@Pedro: SQL Express
ReplyDelete