Friday, August 27, 2010

SQL Server Compact 4.0 ASP.NET Membership provider

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):

sqlceprov.png

If you encounter any bugs, have suggestions or any other issues, please provide feedback here

43 comments:

WBS-IT said...

This is awesome, now what about the DB Structure?

ErikEJ said...

Alex: Not sure what you mean with this question...

Unknown said...

Hi Erik

how can i use this lib with ASP NET MVC?!

ErikEJ said...

Braga: Same way as with WebForms... Modify web.config and add files to app_code (see the CodePlex site)

Unknown said...

Erik

ASP 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?!

ErikEJ said...

Anywhere in you project, or in a separate class library, and add a reference to this.

Unknown said...

Hi...

i 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.

ErikEJ said...

Mushin: I will look into it.

ErikEJ said...

Mushin: Fixed in latest source

Unknown said...

Erik, thank you for this excellent library.

However, 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

ErikEJ said...

Config error?

Unknown said...

Hi Erik,

Yes, 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.

Unknown said...

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.

Following 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?

ErikEJ said...

Michael: Not a clue...

Unknown said...

I'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?

ErikEJ said...

Terry: How about using two database files?

Unknown said...

That could work. I'll give that a try. Thanks.

Unknown said...

Hi Terry ,

Even 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?

Unknown said...

Hello Terry,
I 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?

ErikEJ said...

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

GrandpaB said...

I'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.

ErikEJ said...

bgt: 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.

Sergio León said...

You are my hero!
Excellent job!

Hugh said...

Hi Erik,
Thank 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.

ErikEJ said...

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.

Peter Heard said...

Hi 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!

My project is here

http://dl.dropbox.com/u/48829187/WebApplicationSqlCe.zip

Totally stumped, please help...

Regards
Pete

ErikEJ said...

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)

Peter Heard said...

Hey 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?

http://stackoverflow.com/questions/8262200/strange-error-in-using-erike-sql-membership-provider-unable-to-load-the-native

Great tool btw! :-)

ErikEJ said...

Peter: Answer on SO, also maybe consider using the Microsoft provider instead? (Via NuGet)

Evgeny said...

I installed it and it JUST WORKS. Amazing. :)
Not 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. :)

RV said...

Hi ErikEJ,
I 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

ErikEJ said...

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.

Unknown said...

Great 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).

ErikEJ said...

They are stored in the sdf file that the provider creates in app_data folder.

Unknown said...

Yep sorry. They showed up after drop/recreating the table. Is there a way of assigning roles to users with this?

ErikEJ said...

You can use the WSAT (Web Site Admin Tool) from within VS to do that

UnKnOwN said...

Hi Erik
your tools are awsome
i just wona ask about how to use this membership provider with widows forms

thanks

ErikEJ said...

I do not know, if that is posssible at all.

Unknown said...

It`s really great!! Super Thanks!

Unknown said...

Just finding your blog and this post now. Great!

At 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

ErikEJ said...

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

Unknown said...

Hi. 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?

ErikEJ said...

@Pedro: SQL Express