Email Password Remember me?  Lost your password?

• Download source files - 11.0 Kb

Introduction

This article provides two files that contain a Membership provider and a Role provider for ASP.NET v2.0.

Microsoft provides a Membership provider in the framework but only for SQL Server. The class does not seem to work for MySQL so I decided to write a new provider from the ODBC provider sample code included in the framework SDK.

How to use it

To use these classes, you will need the latest MySQL .NET Connector. You might need to recompile it with the new C# compiler for v2.0 (but the new framework should normally support v1.0 or 1.1 assemblies).

1. Create the tables (note that these SQL commands might not work with MySQL 4).

   CREATE TABLE Roles
   (
     Rolename Varchar (255) NOT NULL,
     ApplicationName varchar (255) NOT NULL
   )
   
   CREATE TABLE UsersInRoles
   (
     Username Varchar (255) NOT NULL,
     Rolename Varchar (255) NOT NULL,
     ApplicationName Text (255) NOT NULL
   )
   ALTER TABLE 'usersinroles' 
         ADD INDEX ( 'Username', 'Rolename', 'ApplicationName') ;
   ALTER TABLE 'roles' ADD INDEX ( 'Rolename' , 'ApplicationName' ) ;
   
   CREATE TABLE 'users' (
     'PKID' varchar(36) collate latin1_general_ci NOT NULL default '',
     'Username' varchar(255) collate latin1_general_ci NOT NULL default '',
     'ApplicationName' varchar(100) 
                       collate latin1_general_ci NOT NULL default '',
     'Email' varchar(100) collate latin1_general_ci NOT NULL default '',
     'Comment' varchar(255) collate latin1_general_ci default NULL,
     'Password' varchar(128) collate latin1_general_ci NOT NULL default '',
     'PasswordQuestion' varchar(255) collate latin1_general_ci default NULL,
     'PasswordAnswer' varchar(255) collate latin1_general_ci default NULL,
     'IsApproved' tinyint(1) default NULL,
     'LastActivityDate' datetime default NULL,
     'LastLoginDate' datetime default NULL,
     'LastPasswordChangedDate' datetime default NULL,
     'CreationDate' datetime default NULL,
     'IsOnLine' tinyint(1) default NULL,
     'IsLockedOut' tinyint(1) default NULL,
     'LastLockedOutDate' datetime default NULL,
     'FailedPasswordAttemptCount' int(11) default NULL,
     'FailedPasswordAttemptWindowStart' datetime default NULL,
     'FailedPasswordAnswerAttemptCount' int(11) default NULL,
     'FailedPasswordAnswerAttemptWindowStart' datetime default NULL,
     PRIMARY KEY  ('PKID')
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2. Change MySqlMembershipProvider::encryptionKey to a random hexadecimal value of your choice.
3. Upload the two files to ~/App_Code.
4. Modify your web.config using the following template (if you are on a shared hosting server, you will have to set writeExceptionsToEventLog to false):

   <connectionStrings>
       <add name="ConnString" 
         connectionString="Database=YOURDBNAME;Data Source=localhost;
                           User Id=YOURUSERNAME;Password=YOURPWD" />
   </connectionStrings>
   <system.web>
       <roleManager defaultProvider="MySqlRoleProvider"
           enabled="true"
           cacheRolesInCookie="true"
           cookieName=".ASPROLES"
           cookieTimeout="30"
           cookiePath="/"
           cookieRequireSSL="false"
           cookieSlidingExpiration="true"
           cookieProtection="All" >
       <providers>
           <clear />
           <add
               name="MySqlRoleProvider"
               type="Andri.Web.MySqlRoleProvider"
               connectionStringName="ConnString"
               applicationName="YOURAPPNAME"
               writeExceptionsToEventLog="true"
           />
       </providers>
       </roleManager>
       
       <membership defaultProvider="MySqlMembershipProvider" 
                   userIsOnlineTimeWindow="15">
           <providers>
               <clear />
               <add
                   name="MySqlMembershipProvider"
                   type="Andri.Web.MySqlMembershipProvider"
                   connectionStringName="ConnString"
                   applicationName="YOURAPPNAME"
                   enablePasswordRetrieval="false"
                   enablePasswordReset="true"
                   requiresQuestionAndAnswer="true"
                   requiresUniqueEmail="true"
                   passwordFormat="Hashed"
                   writeExceptionsToEventLog="true"
               />
         </providers>
       </membership>
   </system.web>

5. That's it! You can use the login controls provided in the ASP.NET framework v2.0 if you want or use your own controls.

You can do whatever you want with this code but just send me an email if you have done some improvements or some bug correction. Isn't that what this website is about, sharing?

You must Sign In to use this message board.

Per page 102550

FirstPrevNext

step 1 error thisismycode 1:39 11 Sep '09   when I try this sql command ALTER TABLE 'usersinroles' ADD INDEX ( 'Username', 'Rolename', 'ApplicationName' ) #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''usersinroles' ADD INDEX ( 'Username', 'Rolename', 'ApplicationName')' at line 1 Sign In·View Thread·PermaLink 2.00/5 Re: step 1 error urbluca 1:57 16 Oct '09   incorrect: ALTER TABLE 'usersinroles' ADD INDEX ( 'Username', 'Rolename', 'ApplicationName' ) correct: ALTER TABLE usersinroles ADD INDEX ( Username, Rolename, ApplicationName ) the same goes for the rest of the SQL commands, remove the ' (whatever they are called) see post http://www.codeproject.com/Messages/1994013/SQL-Code-didnt-work-in-Navicat-for-MySQL-heres-an-.aspx[^] Sign In·View Thread·PermaLink 2.00/5 Recompiling simba222 3:26 25 Aug '09   Hi, I am new to .NET. How to recompile the .cs files? Which web.config need to be configured? Thanks in advance. Sign In·View Thread·PermaLink 2.00/5 Could not load type 'Andri.Web.MySqlRoleProvider'. The_J0ker 23:15 8 Jul '09   What's wrong? Configuration Error Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately. Parser Error Message: Could not load type 'Andri.Web.MySqlRoleProvider'. Source Error: Line 36: <add Line 37: name="MySqlRoleProvider" Line 38: type="Andri.Web.MySqlRoleProvider" Line 39: connectionStringName="ConnString" Line 40: applicationName="Booking_System_07072009" Sign In·View Thread·PermaLink 1.50/5 help me jorgebatista07 20:43 10 Jun '09   To use these classes, you will need the latest MySQL .NET Connector. You might need to recompile it with the new C# compiler for v2.0 (but the new framework should normally support v1.0 or 1.1 assemblies). how i do that??? Sign In·View Thread·PermaLink 1.50/5 Re: help me The_J0ker 23:19 8 Jul '09   yes, how? Sign In·View Thread·PermaLink error jorgebatista07 20:38 10 Jun '09   im getting this error "Provider must implement the class 'System.Web.Security.RoleProvider'" can help me Sign In·View Thread·PermaLink Re: error palmerm1 5:44 12 Jun '09   I am getting the same error using the new v6.0.4 connector. Never got this with v5.2.3. Did you find a resolution? Sign In·View Thread·PermaLink Re: error palmerm1 6:16 12 Jun '09   BTW, I have reverted back to 5.2.3 and the error is gone. Sign In·View Thread·PermaLink Provider Could Not Establish A Connection to the Database PLEASE HELP1 TransMayernik751 13:10 6 Apr '09   I built a small website in VS 2008 written in C#. I'm able to view and modify tables within my program using MySQL as a datasource. I'm trying to take it to the next level and use MySQL as a role and membership provider. I followed the above directions to a T. I was able to build the project successfully. Unfortunately, when I go to the ASP.NET Web Application and Admin Tool and click on the "Test" button for MySQLMembershipProvider (or RoleProvider) I am unable to connect. The database is currently hosted online (not localhost). I have created the appropriate tables and tried connection strings known to work, but nothing. I've been working on this for six hours and I'm about to lose it. Can anyone help? Sign In·View Thread·PermaLink 1.00/5 Re: Provider Could Not Establish A Connection to the Database PLEASE HELP1 TransMayernik751 17:25 6 Apr '09   OK...As far as I can figure out, the Admin Tool isn't compatible with remote DBs. So, I gotta code everything manually (which is fine). Does anyone have any sample code for login control? Sign In·View Thread·PermaLink cannot handle more than one user mam54 4:17 29 Nov '08   hello..thanks for the code..its really helpful. I have the folowing issue however, when i create more than one user through the website administration interface, the site goes buzurk..with one user it works fine though. Here is what im doing: i have a master page with a loginstatus and loginname controls, the login control itself is not on the master page i dont want it to appear everywhere, so its on the default.aspx page. using one user, lets say user1, i can login and logout normally, however if i added another user with the same role, lets say user2, and i login using user1, logging out doesnt work anymore, it keeps me on the same page not going back to default.aspx where the login control resides. Is this issue from the way i wrote the site or is it from integrating mysql with asp membership? one thing to note is when i press the test button next to the provider it gives me an error that i must create the database, this happens regardless of how many users i created. I am using mysql 4.1. Your help is much appreciated. Thanks Mohamed Moubarak Sign In·View Thread·PermaLink Re: cannot handle more than one user mam54 5:29 29 Nov '08   ok...the logout button doesnt regularly take you back to the page where the login control exists, my mistake..turns out that it was taking me back when i press logout because i have the following in we.config: but im still facing the problem when creating more than one user...any suggestions? Sign In·View Thread·PermaLink 1.00/5 Natively supported by connector? lblogic 12:46 19 Nov '08   Doesn't the MySQL connector for .NET natively support Memberships, Roles and now (in ) Profiles? http://dev.mysql.com/downloads/connector/net/5.2.html [^] Or am I mistaken.. lblogic http://leftbrainlogic.com/ Outside The Right Thoughts Sign In·View Thread·PermaLink 5.00/5 Add new role and add a user in a role semi 12:29 2 Nov '08   firstly thanks for this provider... I can use it for add user, login site but ı want to use roles for login same page in my site, But for it ı must create roles and add users in role by use codes. How can ı do it? thank for your helps Sign In·View Thread·PermaLink 1.33/5 Using .NET Connector v5.2.3 palmerm1 19:16 19 Oct '08   In case it helps anyone, I've just recently written an article on my blog about implementing ASP.NET Membership using the current version of the MySQL .NET Connector, v5.2.3. That article can be found here. Sign In·View Thread·PermaLink 1.00/5 Profile uswebpro2 23:21 2 Sep '08   Anything for the ASP.net Profile Provider? - aron Sign In·View Thread·PermaLink 2.00/5 "Could not find any recognizable digits" - error Jo3p 6:08 19 Jun '08   Hello, I get a "Could not find any recognizable digits" error when going to the next CreateUserWizard step. Anyone an idea why this happens? I've been cracking my head on it, but no luck... Regards, Joep -- Server Error in '/' Application. -------------------------------------------------------------------------------- Could not find any recognizable digits. 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.FormatException: Could not find any recognizable digits. Source Error: Line 1536: byte[] returnBytes = new byte[hexString.Length / 2]; Line 1537: for (int i = 0; i < returnBytes.Length; i++) Line 1538: returnBytes[i] = Convert.ToByte(hexString.Substring(i * 2, 2), 16); Line 1539: return returnBytes; Line 1540: } Source File: e:\linkedpapers.com\wwwroot\App_Code\MySQLMembershipProvider.cs Line: 1538 Stack Trace: [FormatException: Could not find any recognizable digits.] System.ParseNumbers.StringToInt(String s, Int32 radix, Int32 flags, Int32* currPos) +0 System.Convert.ToByte(String value, Int32 fromBase) +41 Andri.Web.MySqlMembershipProvider.HexToByte(String hexString) in e:\linkedpapers.com\wwwroot\App_Code\MySQLMembershipProvider.cs:1538 Andri.Web.MySqlMembershipProvider.EncodePassword(String password) in e:\linkedpapers.com\wwwroot\App_Code\MySQLMembershipProvider.cs:1490 Andri.Web.MySqlMembershipProvider.CreateUser(String username, String password, String email, String passwordQuestion, String passwordAnswer, Boolean isApproved, Object providerUserKey, MembershipCreateStatus& status) in e:\linkedpapers.com\wwwroot\App_Code\MySQLMembershipProvider.cs:453 System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +305 System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +105 System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +453 System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +149 System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +17 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) +1746 -------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433 Sign In·View Thread·PermaLink how to add custom fields like Firstname etc using Andri Mysql Membership Provider ryandemelo 8:53 11 Jun '08   hi can someone pls tell me how to add custom fields and provide the updation using andris mysql membership provider thanks Sign In·View Thread·PermaLink 1.00/5 Can't use this provider to get the currently logged in user andieje1 12:38 8 May '08   Hi I am having a problem on my login page with this provider. In the loggedIn event of my login control i am trying to get the currently logged in user with this code Dim u As MembershipUser = Membership.GetUser but i am getting the error object not set to instance of object any help much appreciated thanks Sign In·View Thread·PermaLink 1.50/5 Re: Can't use this provider to get the currently logged in user vakti 9:38 25 May '08   i got the same problem, i found the problem with pApplicationName because the function use it, and in the context of that method it is null at this moment. i suppose that initialize method must set this variable when application start and invoke the class, but i dont know why the value of pApplicationName becomes null later.... Sign In·View Thread·PermaLink How to provide Custome eror message podal 18:31 24 Apr '08   hi all, i am using this cs files. work nicely. my last was about navigation error, but that because of tables. now all working fine. Here i am troubling of custom error messages. In change password, i dont want any construction for new messages. but password error or any error occured is shown as "Password incorrect or New Password invalid. New Password length minimum: 7. Non-alphanumeric characters required: 1.". I want to delete the second sentence. how i customize. any possibility Sign In·View Thread·PermaLink Why not Navigate to my Request URL podal 17:49 23 Apr '08   hi, i used MySQLProvider and MyRole Provider as well. All r fine. if any logn problem it Login control show the error. But after enter the correct username and password, Login Control shows as Logged In but didnt redirect to requested page. The same login page will show. Wat is the problem. any one help me. please Sign In·View Thread·PermaLink 1.00/5 Cookies Time Expires Member 3355682 5:47 3 Apr '08   How change the time of the Cookies Time Expires to 5 days? All time I get the value: "#12:00:00 AM#" Example from web.config: <forms loginurl="login.aspx" name="MYWEBAPP.ASPXAUTH" protection="All" timeout="3000000" /> Help... Sign In·View Thread·PermaLink How to use this Role provider for Oracle database elizabethsheeba 6:36 11 Mar '08   Is it possible to use this code for a oracle database. how to create a role provider for connecting to oracle database. Sign In·View Thread·PermaLink 1.80/5

Last Visit: 21:53 20 Nov '09     Last Update: 21:53 20 Nov '09 123456789 Next »