Unique SQL Login w/ SqlMembership Provider

April 03, 2008 By: Dave

Yesterday, we looked at how to setup the web.config file and modify the business logic layer to make use of Profile properties to give us unique SQL logins when we use the SqlMembership provider for forms authentication. Today, we are going to show you the code you will need to associate the profile properties with the user.

Most people think of profile properties as something the user associates with his own login. What we need to do in this case is have the admin associate the properties with the login. Fortunately, there are APIs in ASP.NET that allow us to do just that. So, to start, I’m going to show you the update code:

Update the user information

public void UpdateLogin(string LoginName,
    string SQLUserName,string SQLPassword)
{
    ProfileCommon Profile =
      (ProfileCommon)ProfileCommon.Create(LoginName,true);
    Profile.SQLUserName = SQLUserName;
    Profile.SQLPassword = SQLPassword;
    Profile.Save();
}

Note: the Profile classes are part of the System.Web.Profile namespace. You’ll want to include that at the top of any source file that is using it.

The first line of this code creates a profile object that is associated with the login name. The second and third line assign the username and password. The forth line saves that all back into the Membership database.

You may wonder where the SQLUserName and SQLPassword properties came from. The ones that are hanging off the Profile object. They are not part of the ProfileCommon class. They automatically get created simply by declaring them in the web.config file.

Displaying the list of users

As I mentioned yesterday, not every user needs to have this information associated with them. So, we need to be able to filter out the users. In my implementation, I decided to make the SQL login association role based. So, my code that files my DataTable that I can bind to a grid (or whatever) retrieves all of the users in the system and then adds rows to the DataTable if the user is part of that row:

public DataSetContentEditors.LoginsDataTable GetLogins()
{
    ProfileCommon Profile = new ProfileCommon();
    DataSetContentEditors.LoginsDataTable logins =
         new DataSetContentEditors.LoginsDataTable();
    MembershipUserCollection muc = Membership.GetAllUsers();
    foreach (MembershipUser mu in muc)
    {
        if (Roles.IsUserInRole(mu.UserName, "Editor"))
        {
            logins.AddLoginsRow(mu.UserName,
               Profile.GetProfile(mu.UserName).SQLUserName,
               Profile.GetProfile(mu.UserName).SQLPassword);
        }
    }
    return logins;
}

Again, I’m using the ProfileCommon class. This time to retrieve the username and password values from the profile database. Just like we did yesterday when we got the connection string.

The Membership.GetAllUsers() call is retrieving all of the users in the system. My foreach statement iterates through each of those users, checks to see if they are part of the “Editor” role and if they are, add the login name, the sql username and the sql password to the LogingDataTable.

Is it Secure?

No, not out of the box. You can visit this article at Microsoft to get more information on how to implement profile properties at various security levels: Security Profile Properties