SQL Profile Import with Forms Based Authentication - Eric Kraus' Microsoft Blog - Site Home

This has come up several times now and I thought I would contribute a solution that has worked for me.

The problem: Need to create an Audience based on a profile of a SharePoint user. The users is, however, stored in a SQL Server membership database (outside the context of SharePoint). The user profile needs to be imported from the SQL Server membership database into SharePoint user profile store. This process does come out of the box with LDAP (Active Directory), but not when using Forms Authentication and SQL Server.

Background:
SharePoint Publishing Site
Two Web Applications
Port 1234: Windows Authentication
Port 80: Forms Authentication (pointing to SQL Server membership database)

Gotchas:
-When creating new properties, the length attribute is not available via the SQL Server profile. In this example, we have set it to 100. You should consider an alternate solution (config file maybe?) to store the max length for each property.
-To alleviate an Object Reference Not Set to Instance error, you must specify ALL of the attributes of a property (this code is already doing that).
-Add a reference to the Microsoft.Office.Server.dll and Microsoft.SharePoint.dll
-By default, profiles are imported with the account name: Provider:UserName (e.g. CustomAspNetSqlMembershipProvider:username1) I have commented out the code to do this, but alternatively you can add the profile in with the account name set equal to the username.

This code works great in a console application or windows service. The code will connect up to the profile manager for SQL Server and loop through each profile. For each profile, it will check to make sure that SharePoint has all of the profile properties (meta data). If not, it will add the new property before updating the SharePoint profile with the SQL Server profile value.

Don't forget, you will need to set up your SharePoint web.config with the connectionString membership and profile attributes in <system.web>.

----------------------------------------------------------------------------------------------------------------------------------

using System;
using System.Web;
using System.Web.Profile;
using System.Configuration;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using Microsoft.Office.Server;
using Microsoft.Office.Server.UserProfiles;

string providerName = "CustomAspNetSqlMembershipProvider";
string userAcctAlgorithm = "{0}:{1}";
string UserName = "";
string UserType = "";

SPFarm farm = SPFarm.Local;
foreach (SPService serv in farm.Services)
{
   if (serv is SPWebService)
   {
      SPWebService webServ = (SPWebService)serv;
      SPWebApplication webApp = webServ.WebApplications["SharePoint - 80"];
     SPSite site = webApp.Sites["http://mossdemo:80 "];

      //(SP) get the context for the site we have instantiated
      ServerContext serverContext = ServerContext.GetContext(site);

      //(SP) create a new instance of the user profile manager class using the context from above
      UserProfileManager upm = new UserProfileManager(serverContext);

      //(SQL) get membership profiles
      ProfileInfoCollection profiles = ProfileManager.GetAllProfiles(ProfileAuthenticationOption.All);

      //(SQL) for each membership profile in SQL Server
      foreach (ProfileInfo info in profiles)
      {
         //(SP) build our account name (provider + : + username)
         //string userAccount = string.Format(userAcctAlgorithm, providerName, info.UserName);
        string userAccount = info.UserName
         UserName = info.UserName;

         //(SQL) create a profile object for the user profile
         ProfileBase pb = ProfileBase.Create(info.UserName);

         //(SP) check if user account exists and get a reference to it, if not create a new one
         UserProfile up;

         if (upm.UserExists(userAccount))
         {
            up = upm.GetUserProfile(userAccount);
         }
         else
         {
            up = upm.CreateUserProfile(userAccount);
         }

         //(SQL) loop through the properties in the SQL profiles
         foreach (SettingsProperty sp in ProfileBase.Properties)
         {
            //(SP) check if SQL property is in SP profiles
            Property tempProp = upm.Properties.GetPropertyByName(sp.Name);

            //(SP) if the property is null, it does not exist, so we need to create a new property
            if (tempProp == null)
            {
               //(SP) set property attributes
               Property prpty = upm.Properties.Create(false);
               prpty.Name = sp.Name;
               prpty.Type = sp.PropertyType.Name;
               prpty.DisplayName = sp.Name;
               prpty.Length = 100;
               prpty.PrivacyPolicy = PrivacyPolicy.OptIn;
               prpty.DefaultPrivacy = Privacy.Public;
               prpty.Description = sp.Name;
               prpty.IsUserEditable = true;
               prpty.ChoiceType = ChoiceTypes.None;
               prpty.IsMultivalued = false;
               prpty.UserOverridePrivacy = false;
               prpty.IsReplicable = true;
               prpty.IsColleagueEventLog = false;
               prpty.IsAlias = false;
               prpty.IsSearchable = true;
               prpty.IsUpgrade = false;
               prpty.IsUpgradePrivate = false;
               prpty.IsVisibleOnEditor = true;
               prpty.IsVisibleOnViewer = false;
               prpty.Separator = MultiValueSeparator.Comma;
               prpty.MaximumShown = 10;

               //(SP) add the property to the user profiles store
               prpty.Commit();
            }

            //(COMBINED) add the value from the SQL profile base to the SP user profile
            up[sp.Name].Clear();
            up[sp.Name].Add(pb.GetPropertyValues(sp.Name));
            up.Commit();
         }

}