Setting Up FBA and SQL Membership in SharePoint 2010 with PowerShell
This post provides PowerShell script to easily configure SQL FBA in SharePoint 2010.
A long time ago, I wrote a blog post that shows how to configure the SQL Server Provider for FBA in SharePoint 2010. I have been working on a project that requires me to configure FBA, and I got kind of tired of following my own blog post. Further, it was cumbersome and error-prone to edit the files on all of the servers. There are several examples in the community that perform similar functions, such as Steve Peschka’s excellent example, “SharePoint 2010 Forms Based Authentication Configuration Manager”, and the popular AutoSPInstaller. However, I needed something that works with my existing PowerShell configuration scripts.
What Does It Do?
The code is rather straightforward, and follows directions in my previous blog closely, automating the steps from that blog post. It updates the web.config for each server in a farm.
The “main” part of the script is at the bottom of the page. This is where we ask for all servers in the farm that are application servers (not SQL, Active Directory, or Exchange servers) and iterate through all of them. Note that the currently logged in account must have read-write permissions to all of the servers in the farm and have access to the C drive on each. Also notice that a backup file is created in the directory using the form “yyyy MM dd HH mm.web.config.bak” so that, should something go wrong, you can easily revert back to a previous version of the configuration file.
One of the benefits of Steve Peschka’s example is that it uses the WebConfigModification class so that any new servers added to the farm are automatically updated. To get a similar benefit without installing a feature to the server, simply run this script again… it’s smart enough to check if settings were already applied, and will update the SQL connection string in each file on all servers with the specified value.
If I’m Going to Use This, What Do I Need to Change?
The only part you should have to change in this script are the variables at the bottom:
#Update with path to Central Administration web.config
$pathToCentralAdminConfig = "c:\inetpub\wwwroot\wss\virtualdirectories\36164\web.config"
#Update with path to web application's web.config
$pathToWebApplicationConfig = "c:\inetpub\wwwroot\wss\VirtualDirectories\SAMLClaims.SharePoint.com443\web.config"
#Update with correct connection string
$connectionString = "data source=SQL; Integrated Security=SSPI; Initial Catalog=aspnetdb;"
Note that the script assumes the SQL connection string name “FBA”, membership provider name “FBAMembership”, and role provider name “FBARoles”. I didn’t bother with making variables for those, so be sure to change them if your environment requires different settings.
Show Me The Code!
The code is available as an attachment to this post as well. As usual, this code is provided as-is, no warranties, use at your own risk.
function CreateBackupFile($xmlDoc, $path)
{
$date = Get-Date
$dateString = $date.ToString("yyyy MM dd H mm")
$backupPath = $path.Replace("web.config", "$dateString.web.config.bak")
$xmlDoc.Save($backupPath)
}
function AddPeoplePickerWildcard($xmlDoc)
{
$newPeoplePickerNode = $xmlDoc.selectSingleNode("/configuration/SharePoint/PeoplePickerWildcards/add[@key='FBAMembership']");
if(!$newPeoplePickerNode)
{
$peoplePickerNode = $xmlDoc.selectSingleNode("/configuration/SharePoint/PeoplePickerWildcards")
$newPeoplePickerNode = $xmlDoc.CreateNode("element", "add", "")
$peoplePickerKeyAttr = $xmlDoc.CreateAttribute("key");
$peoplePickerKeyAttr.Value = "FBAMembership";
$newPeoplePickerNode.Attributes.Append($peoplePickerKeyAttr)
$peoplePickerValueAttr = $xmlDoc.CreateAttribute("value");
$peoplePickerValueAttr.Value = "%"
$newPeoplePickerNode.Attributes.Append($peoplePickerValueAttr)
$peoplePickerNode.AppendChild($newPeoplePickerNode)
}
}
function AddConnectionString($xmlDoc, $connectionString)
{
#Check to see if the node exists and we just need to update the value
$connectionStringAddNode = $xmlDoc.selectSingleNode("/configuration/connectionStrings/add[@name='FBA']")
if(!$connectionStringAddNode)
{
#It doesn't exist. Check to see if "connectionStrings" exists, and if not, create it
$connectionStringNode = $xmlDoc.selectSingleNode("/configuration/connectionStrings")
if(!$connectionStringNode)
{
$connectionStringNode = $xmlDoc.CreateNode("element","connectionStrings","")
$xmlDoc.selectSingleNode("/configuration").AppendChild($connectionStringNode)
}
$connectionStringNode = $xmlDoc.selectSingleNode("/configuration/connectionStrings")
$connectionStringAddNode = $xmlDoc.CreateNode("element","add", "");
$nameAttr = $xmlDoc.CreateAttribute("name")
$nameAttr.Value = "FBA"
$connectionStringAddNode.Attributes.Append($nameAttr);
$providerAttr = $xmlDoc.CreateAttribute("providerName")
$providerAttr.Value = "System.Data.SqlClient"
$connectionStringAddNode.Attributes.Append($providerAttr);
$connectionStringAddNode = $connectionStringNode.AppendChild($connectionStringAddNode);
}
#Create or update the connectionString attribute
$connectionStringAttr = $xmlDoc.CreateAttribute("connectionString")
$connectionStringAttr.Value = $connectionString
$connectionStringAddNode.Attributes.Append($connectionStringAttr);
}
function AddMembership($xmlDoc)
{
$membershipAddNode = $xmlDoc.selectSingleNode("/configuration/system.web/membership/providers/add[@name='FBAMembership']")
if(!$membershipAddNode)
{
#The membership node doesn't exist
$membershipNode = $xmlDoc.selectSingleNode("/configuration/system.web/membership")
$providerNode = $null
if(!$membershipNode)
{
$membershipNode = $xmlDoc.CreateNode("element", "membership", "")
$providerNode = $xmlDoc.CreateNode("element","providers","")
$membershipNode.AppendChild($providerNode)
$xmlDoc.selectSingleNode("/configuration/system.web").AppendChild($membershipNode)
}
$providerNode = $xmlDoc.selectSingleNode("/configuration/system.web/membership/providers")
$membershipAddNode = $xmlDoc.CreateNode("element","add","")
$membershipNameAttr = $xmlDoc.CreateAttribute("name")
$membershipNameAttr.Value = "FBAMembership"
$membershipAddNode.Attributes.Append($membershipNameAttr)
$membershipTypeAttr = $xmlDoc.CreateAttribute("type")
$membershipTypeAttr.Value = "System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
$membershipAddNode.Attributes.Append($membershipTypeAttr)
$membershipAppNameAttr = $xmlDoc.CreateAttribute("applicationName")
$membershipAppNameAttr.Value = "/"
$membershipAddNode.Attributes.Append($membershipAppNameAttr)
$membershipconnectionStringNameAttr = $xmlDoc.CreateAttribute("connectionStringName")
$membershipconnectionStringNameAttr.Value = "FBA"
$membershipAddNode.Attributes.Append($membershipconnectionStringNameAttr)
$membershipenablePasswordResetAttr = $xmlDoc.CreateAttribute("enablePasswordReset")
$membershipenablePasswordResetAttr.Value = "false"
$membershipAddNode.Attributes.Append($membershipenablePasswordResetAttr)
$membershipenablePasswordRetrieval = $xmlDoc.CreateAttribute("enablePasswordRetrieval")
$membershipenablePasswordRetrieval.Value = "false"
$membershipAddNode.Attributes.Append($membershipenablePasswordRetrieval)
$membershippasswordFormat = $xmlDoc.CreateAttribute("passwordFormat")
$membershippasswordFormat.Value = "Clear"
$membershipAddNode.Attributes.Append($membershippasswordFormat)
$membershiprequiresQuestionAndAnswer = $xmlDoc.CreateAttribute("requiresQuestionAndAnswer")
$membershiprequiresQuestionAndAnswer.Value = "false"
$membershipAddNode.Attributes.Append($membershiprequiresQuestionAndAnswer)
$membershiprequiresUniqueEmail = $xmlDoc.CreateAttribute("requiresUniqueEmail")
$membershiprequiresUniqueEmail.Value = "false"
$membershipAddNode.Attributes.Append($membershiprequiresUniqueEmail)
$providerNode.AppendChild($membershipAddNode)
}
}
function AddRoles($xmlDoc)
{
#Check to see if it was already created, and if not, create it
$rolesAddNode = $xmlDoc.selectSingleNode("/configuration/system.web/roleManager/providers/add[@name='FBARoles']")
if(!$rolesAddNode)
{
$rolesNode = $xmlDoc.selectSingleNode("/configuration/system.web/roleManager")
$providerNode = $null
if(!$rolesNode)
{
$rolesNode = $xmlDoc.CreateNode("element", "roleManager", "")
$providerNode = $xmlDoc.CreateNode("element","providers","")
$rolesNode.AppendChild($providerNode)
$xmlDoc.selectSingleNode("/configuration/system.web").AppendChild($rolesNode)
}
$rolesNode = $xmlDoc.selectSingleNode("/configuration/system.web/roleManager")
$rolesEnabledAttr = $xmlDoc.CreateAttribute("enabled");
$rolesEnabledAttr.Value = "true";
$rolesNode.Attributes.Append($rolesEnabledAttr)
$providerNode = $xmlDoc.selectSingleNode("/configuration/system.web/roleManager/providers")
$rolesAddNode = $xmlDoc.CreateNode("element","add","")
$roleNameAttr = $xmlDoc.CreateAttribute("name")
$roleNameAttr.Value = "FBARoles"
$rolesAddNode.Attributes.Append($roleNameAttr)
$roleTypeAttr = $xmlDoc.CreateAttribute("type")
$roleTypeAttr.Value = "System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
$rolesAddNode.Attributes.Append($roleTypeAttr)
$rolesAppNameAttr = $xmlDoc.CreateAttribute("applicationName")
$rolesAppNameAttr.Value = "/"
$rolesAddNode.Attributes.Append($rolesAppNameAttr)
$rolesConnectionStringNameAttr = $xmlDoc.CreateAttribute("connectionStringName")
$rolesConnectionStringNameAttr.Value = "FBA"
$rolesAddNode.Attributes.Append($rolesConnectionStringNameAttr)
$providerNode.AppendChild($rolesAddNode)
}
}
function ProcessCentralAdmin($path, $connectionString)
{
$content = Get-Content -Path $path
[System.Xml.XmlDocument] $xd = new-object System.Xml.XmlDocument
$xd.LoadXml($content)
CreateBackupFile $xd $path
#Add connection string
AddConnectionString $xd $connectionString
#Add People Picker Wildcard
AddPeoplePickerWildcard $xd
#Add Roles
AddRoles $xd
$roleNode = $xd.selectSingleNode("/configuration/system.web/roleManager")
$defaultRoleProviderAttr = $xd.CreateAttribute("defaultProvider")
$defaultRoleProviderAttr.Value = "AspNetWindowsTokenRoleProvider"
$roleNode.Attributes.Append($defaultRoleProviderAttr)
#Add Membership
AddMembership $xd
$membershipNode = $xd.selectSingleNode("/configuration/system.web/membership")
$defaultMembershipProviderAttr = $xd.CreateAttribute("defaultProvider")
$defaultMembershipProviderAttr.Value = "FBAMembership"
$membershipNode.Attributes.Append($defaultMembershipProviderAttr)
$xd.Save($path)
}
function ProcessWebApplication($path, $connectionString)
{
$content = Get-Content -Path $path
[System.Xml.XmlDocument] $xd = new-object System.Xml.XmlDocument
$xd.LoadXml($content)
CreateBackupFile $xd $path
#Add connection string
AddConnectionString $xd $connectionString
#Add People Picker Wildcard
AddPeoplePickerWildcard $xd
#Add Membership
AddMembership $xd
#Add Roles
AddRoles $xd
$xd.Save($path)
}
function ProcessSTS($path, $connectionString)
{
$content = Get-Content -Path $path
[System.Xml.XmlDocument] $xd = new-object System.Xml.XmlDocument
$xd.LoadXml($content)
CreateBackupFile $xd $path
#Add connection string
AddConnectionString $xd $connectionString
#People picker wildcard is not necessary in STS config
#Check to see if the system.web element exists, and if not, create it
$sysWebNode = $xd.SelectSingleNode("/configuration[system.web]")
if(!$sysWebNode)
{
$config = $xd.SelectSingleNode("/configuration");
$sysWebNode = $xd.CreateNode("element","system.web","")
$config.AppendChild($sysWebNode)
}
#Add Membership
AddMembership $xd
#Set FBAMembership as default in STS
$membershipNode = $xd.selectSingleNode("/configuration/system.web/membership")
$defaultMembershipProviderAttr = $xd.CreateAttribute("defaultProvider")
$defaultMembershipProviderAttr.Value = "FBAMembership"
$membershipNode.Attributes.Append($defaultMembershipProviderAttr)
#Add Roles
AddRoles $xd
#Set FBARoles as default in STS
$roleNode = $xd.selectSingleNode("/configuration/system.web/roleManager")
$defaultRoleProviderAttr = $xd.CreateAttribute("defaultProvider")
$defaultRoleProviderAttr.Value = "FBARoles"
$roleNode.Attributes.Append($defaultRoleProviderAttr)
$xd.Save($path)
}
function Main($pathToWebApplicationConfig, $pathToCentralAdminConfig, $connectionString)
{
$servers = Get-SPServer | ?{$_.Role -eq "Application"}
foreach($server in $servers)
{
$name = $server.Name
$webAppConfigPath = $pathToWebApplicationConfig.Replace("c:\", "\\$name\c$\")
$centralAdminConfigPath = $pathToCentralAdminConfig.Replace("c:\", "\\$name\c$\")
$stsConfigPath = "\\$name\c$\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\SecurityToken\web.config"
ProcessWebApplication $webAppConfigPath $connectionString
if(Test-Path $centralAdminConfigPath)
{
ProcessCentralAdmin $centralAdminConfigPath $connectionString
}
ProcessSTS $stsConfigPath $connectionString
}
}
#Update with path to Central Administration web.config
$pathToCentralAdminConfig = "c:\inetpub\wwwroot\wss\virtualdirectories\36164\web.config"
#Update with path to web application's web.config
$pathToWebApplicationConfig = "c:\inetpub\wwwroot\wss\VirtualDirectories\SAMLClaims.SharePoint.com443\web.config"
#Update with correct connection string
$connectionString = "data source=SQL; Integrated Security=SSPI; Initial Catalog=aspnetdb;"
Main $pathToWebApplicationConfig $pathToCentralAdminConfig $connectionString
Summary
That’s it! The code is pretty straightforward and broken into individual functions. I smoke-tested this in my environment and it seems to work for either a new environment or an existing environment, but you’ll want to test this in your environment to make sure any assumptions I made are accurate.
For More Information
SQL Server Provider for FBA in SharePoint 2010
SharePoint 2010 Forms Based Authentication Configuration Manager