Archive for the 'C#' Category

ASP.NET Membership Database Migration Helper

This is my project born out of a problem on how to backup the existing data that I have on a website. In one of my projects, I have created a user registration system based on ASP.NET Membership although as I reach the peak of the limits of the database hosting, I need a means to transfer the data from that database to another.

Unfortunately, the original database host of my old project didn’t allow me to connect to the database via SQL Server Management Studio and it would be a pain to manually gather the membership information and maintain its integrity particularly the password if the Membership provider configuration for the password is set to “Hashed”.

I tried searching if there’s an API for programaticaly migrating data but I haven’t found one. Solutions found over the net include backing up the database and restore it in a local store but I find that tedious. I need something that is as much as possible with less clicks. With that, this application is an attempt to solve that problem.


1. Start with Start.aspx (Default.aspx will eventually redirect you to that page). Provide the following information:

a) Source Database Connection String
b) Source Database Application Name
c) Target Database Connection String
d) Target Database Application Name

a & c are obvious reasons why you need to provide them. “b” is needed since we need to specify what specific application database are we backing up. Typically, the ASPNETDB.mdf file that is being created whenever we fire up the Website Administration Tool (WSAT) can handle users for use with Membership for different web applications by virtue of applicationId. This can be verified by opening the aspnet_Applications table. On the other hand “c” is needed since we need to check that the database doesn’t contain that application name as it might overwrite any existing data in there (in cases you want to merge Membership databases).

2. Clicking validate will validate the information that you have provided. Until such time all entries appear to be valid, then it would only be at that period you can click on the “Export >>” button that will perform the actual migration. Likewise, once validated, you can also delete or clear the target database of all it’s entries.


Be aware only that this action is irreversible and there’s no way of backing it up unless you have a spare copy of that database somewhere else.

3. You would be informed in Result.aspx if the migration is okay or not. TryExport.aspx is actually the page that I made to test if the export is working or not. Before trying it out, do the following:

  • Modify web.config by editing the connection string list and the provider list. Edit the connection string section first as the providers will depend on the entries of this section. This application is by default populated with two providers namely: AspNetSqlMembershipProvider (source) and ExportedDBMembershipProvider (destination). The connection string attribute of the provider should match that of the connection string name of the source/destination databases. Failure to do so will prohibit you from validating if the export works or not.
  • The applicationName attribute of the destination provider should match the “Target Application Name” specified in Start.aspx. Failure to do so will prohibit you again from validating if the export works or not.
  • You can change the name of the providers and will allow you to validate however, you need to change as well the values set in the code-behind of TryExport.aspx for the provider name.

Additional Notes:

  • I tested the code to work when transporting between two SQL Server 2005 Express database files. However, the application might throw an error when the data is too big to transfer.
  • This application just exports the application data, membership information and roles. I am still making a way to include the Profile data (if there’s any) to the exported database.

The file is in zip format but since wordpress is not accepting ZIP files as attachment, you can download it here and rename it to .zip.

Comments, reactions, suggestions? Post a comment here!


Searching Approved Users in Roles in ASP.NET Membership

Still more ASP.NET membership tweaks!

The role manager in ASP.NET Membership provides a mechanism to list all the users in the ASP.NET Membership Database for a specific role.


This method returns a string array of usernames that’s a member of that role. Unfortunately, this list returns all the users even if they are approved or not, locked out or not. If you are working on an application that requires to filter out users in the role that’s locked out or isn’t approved yet, there’s no other mechanism on the built-in methods. There’s no overload to the method provided out of the box.roles.png

Luckily, as mentioned in the previous blog about ASP.NET membership, the database is filled with various stored procedures that you can use (or even pattern after) for various usage. I tweaked one of the stored procedures and came up with a stored procedure that gets all the approved users in the ASP.NET membership database.

CREATE PROCEDURE dbo.Membership_GetActiveUsersInRoles

    @RoleName nvarchar(256)



        DECLARE @RoleId uniqueidentifier

        SELECT @RoleId = RoleId
        FROM dbo.aspnet_Roles
        WHERE LOWER(@RoleName) = LoweredRoleName

        SELECT u.UserName, u.UserId
        FROM dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur, dbo.aspnet_Membership m
        u.UserId = m.UserId AND
        u.UserId = ur.UserId AND
        @RoleId = ur.RoleId and
        m.IsApproved = 'true'
         -- You can also specify m.IsLockedOut = 'false' for other purposes


Just copy the said code above and create it in your ASP.NET Membership database (either that ASPNETDB.MDF or an ASP.NET membership configured database) and run it.Happy Coding!

Blog Stats

  • 4,031 hits
July 2018
« Nov