Using a DotNetNuke auth database for a SharePoint FBA database

While there are a fair number of sites outline how to set up a Forms based authentication (FBA) front end using an new empty SQL database. An excellent one by my co-worker Corey Burke at Rackspace is SharePoint Voodoo: Easy-Bake Forms Based Authentication. There are none that outline the process of using a new FBA with an existing DotNetNuke authentication database.

1. Create your FBA site with a new blank FBA database back-end. For this purposes I will call the FBA database ‘FBA-DB’ and the DotNetNuke database as ‘DNN-DB’.

In this instance we were lucky because both database bases use a standard authentication database from Microsoft when the aspnet_regsql command is used. When we create a blank FBA database it will have the standard database structure like the one pictured below.

Out of these 11 tables only 3 will be of any consequence.

  • aspnet_Applications – Entries will identify DotNetNuke and SharePoint
  • aspnet_Membership – Password information
  • aspnet_Users – User information

2. Create a copy of your existing DotNetNuke database which we will call ‘DNN-DB’.

Copy the default ApplicationID from the [DNN-DB].[dbo].[aspnet_Applications] table. This ApplicationId will be in the format of xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx. It will be the first entry in this particular table as pictured below.


3. We next want to update the existing DotNetNuke users so that they will show up in SharePoint. We can do that by using the previously copied ApplciationID.  If we look at the existing DotNetNuke users they will all be assigned with existing DotNetNuke ApplicationId. Inside the SQL manager we will update ApplicationId for our DotNetNuke users with the follow SQL statement to the SharePoint AppliationId.

UPDATE [DNN-DB].[dbo].[aspnet_Users]
SET ApplicationID=’xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx’ (Use default ‘/’ ApplicationId)
WHERE ApplicationID =’xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx’ (Use DotNetNuke ApplicationId)

4. Next you will have to give appropriate permissions to your SharePoint service accounts to the DotNetNuke database. If you installed the SharePoint site using one account then that account will have to be given explicit ‘db_owner’ role membership into the DNN-DB database. If you used a least privilege access installation model then the two account will have to be added as explicit ‘db_owner’ role membership into the DNN-DB database. The first will be the identity account of the application pool that is running the FBA site. The second will be the account that was used to connect to the SQL database.

5. At this point we now have DotNetNuke auth database but the SharePoint FBA is not looking at it. If we look at Corey’s SharePoint Voodoo: Easy Forms Based Authentication site the connection string in the web.config as follows for SharePoint Central Admin site and SharePoint FBA site.

<connectionStrings>

<add name=”AspNetSqlProvider” connectionString=”server=YourSQLServer; database=site_FBA; Trusted_Connection=True” />

</connectionStrings>

Change the database=site_FBA to the DotNetNuke database as follows.

<connectionStrings>

<add name=”AspNetSqlProvider” connectionString=”server=YourSQLServer; database=DNN-DB; Trusted_Connection=True” />

</connectionStrings>

6. So now we have a SharePoint site that recognizes the DotNetNuke database and we can add the uses to the SharePoint site. Note that while they will be listed in the FBA User management panel they will not be an active user within SharePoint. An analogy is a regular AD/Local account can be on the server but that does not mean is a SharePoint user.

As we can see in these 5 accounts they can have YES/NO Active status. Like an AD account can be there but not necessarily enabled. The accounts can also have YES/NO for the IsInSharePoint status. Like an AD account that can exist but not necessarily be in SharePoint.

7. New we can add the DotNetNuke users to the SharePoint site. In your SharePoint site where your FBA management tools are installed navigate to Site Actions>Site Settings> Users and Permissions> People and groups>New. In the User/Groups section open the phone book and browse. And shown in the example below.

Note that the ‘aspnetsqlmemebershiprovider’ is custom provider we setup using Corey’s SharePoint Voodoo: Easy Forms Based Authentication instructions.

Once the DotNetNuke user is added to the SharePoint site then they will have the ability to log into the SharePoint site.

-Javi

About the Author

1 thought on “Using a DotNetNuke auth database for a SharePoint FBA database

Leave a Reply

%d bloggers like this: