SharePoint Forms Based Authentication Using DotNetNuke as User Store

Introduction

In this article, I will explain how to use the DotNetNuke user store and management for a SharePoint web application that uses Forms based authentication.

As you may know, SharePoint has the ability to use Forms based authentication. This is a very important feature if you want to expose your SharePoint sites not just to internal users, but to users over the Internet, such as partners, self-registering users, or just hackers who want to penetrate your intranet.

I assume you are a SharePoint developer or administrator who was asked to create some kind of extranet SharePoint portal that accepts user registration. You are already past the deadline, or you are simply too lazy to write a user management system from scratch (like me).

No advanced knowledge is required for DotNetNuke, but you should at least know what it is. No, it's not an energy drink. It's a free, open source framework that's ideal for creating Enterprise web applications. For more information, please go to www.dotnetnuke.com.

Here are the basic steps or, in other words, sections of this article:

• Install DotNetNuke (if you have not already installed it)
• Modify the DotNetNuke database to make SQLRoleProvier work
• Create an extended SharePoint web application that will use Forms based authentication
• Modify the SharePoint web.config files
• Set SharePoint to use Forms based authentication
• Set permissions for DotNetUsers to access the SharePoint site
• Register in DotNetNuke and login in SharePoint

Install DotNetNuke

If you have not already installed DotNetNuke, download a copy from here and install it (you'll have to register). Even though DotNetNuke might not be your expertise, the installation is not harder than SharePoint installation. Here are the basic steps:

1. Create a new DNS or hosts file entry for the future DNN site. I am calling it: dnn.zeppelinsoft.com.
2. Download DotNetNuke_x_y_z_Source.zip. Unzip it, and set up an IIS site that points to the …/Website folder.
3. Be sure you use ASP.NET version 2.0 and that the Network Services user has permissions to modify that folder.
4. If you don't want to use SQL Express, create an empty database for the future DotNetNuke site.
5. Load your site into a browser, and follow the wizard. Use Typical, and configure it to use your newly created database. Setting the database type and connection is the most important step:



6. Next, you will be asked to set the password and emails for two users: host and admin. You will use these users later in SharePoint as site collection admins.

Modify the DotNetNuke database to make SqlRoleProvider work

There are two providers that we need to set in SharePoint in order to use DotNetNuke as a user store:

1. Membership Provider: responsible for verifying user credentials, changing passwords, etc. As a Membership Provider, we'll use SqlMembershipProvider (that ships with ASP.NET 2.0). DotNetNuke provides native support for it. We'll just have to copy some web.config entries, and we're done. I'll show you that later in this article.
2. Role Provider: responsible for role management, verifying user roles, etc. A role in Forms based authentication is similar to a user group in Active Directory. DotNetNuke doesn't provide native support for SqlRoleProvider (that ships with ASP.NET 2.0), but it does create all stored procedures used by it. The problem is that it does not populate tables used by SqlRoleProvider with data from its native role management tables. There are two solutions to this problem:
1. Use SqlRoleProvider in SharePoint and modify its corresponding SQL stored procedures in DotNetNuke to use its native role management tables.
2. Create a new role provider that will directly use DotNetNuke native role tables.

I will use the first approach here. We only need to modify the following SQL stored procs:

• aspnet_Roles_GetAllRoles
• aspnet_Roles_RoleExists
• aspnet_UsersInRoles_FindUsersInRole
• aspnet_UsersInRoles_GetRolesForUser
• aspnet_UsersInRoles_GetUsersInRoles
• aspnet_UsersInRoles_IsUserInRole

I am making two assumptions here:

1. There is no table prefix for DotNetNuke tables. You are asked about table prefix during DotNetNuke installation. If you chose a prefix, then you will have to modify all the table references in my SQL script to include that prefix.
2. There are no sub-portals in DotNetNuke installation (for simplicity, of course). This means I use only the root portal (PortalID = 0) to get the users and roles definition.

And, here is the SQL sequence to do it (execute it against the DotNetNuke database):

/****** Object:  StoredProcedure [dbo].[aspnet_Roles_GetAllRoles]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[aspnet_Roles_GetAllRoles] (
    @ApplicationName           nvarchar(256))
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications 
    WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN

    select RoleName from dbo.roles
    where 
    PortalID = 0

END

GO
/****** Object:  StoredProcedure [dbo].[aspnet_Roles_RoleExists]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[aspnet_Roles_RoleExists]
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications 
    WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(0)
    
    IF (EXISTS (SELECT RoleName FROM dbo.Roles WHERE LOWER(@RoleName) = lower(RoleName) 
    AND Portalid = 0 ))
        RETURN(1)
    ELSE
        RETURN(0)

END

GO
/****** Object:  StoredProcedure [dbo].[aspnet_UsersInRoles_FindUsersInRole] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[aspnet_UsersInRoles_FindUsersInRole]
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256),
    @UserNameToMatch  nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications 
    WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)

     DECLARE @RoleId int
     SELECT  @RoleId = NULL

     SELECT  @RoleId = RoleId
     FROM    dbo.Roles
     WHERE   
     RoleName  = @RoleName
     and
     PortalID = 0

     IF (@RoleId IS NULL)
         RETURN(1)

    SELECT u.UserName
    FROM   
    dbo.Users u, 
    dbo.UserRoles ur,
    dbo.Roles r
    WHERE  
    u.UserId = ur.UserId AND @RoleId = ur.RoleId 
    AND
    ur.RoleID = r.RoleID 
    AND
    r.PortalID = 0
    AND 
    u.Username LIKE LOWER(@UserNameToMatch)
    ORDER BY u.UserName

    RETURN(0)
END

GO
/****** Object:  StoredProcedure [dbo].[aspnet_UsersInRoles_GetRolesForUser] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[aspnet_UsersInRoles_GetRolesForUser]
    @ApplicationName  nvarchar(256),
    @UserName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications 
    WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)

    DECLARE @UserId uniqueidentifier
    SELECT  @UserId = NULL

    SELECT  @UserId = UserId
    FROM    dbo.aspnet_Users
    WHERE   LoweredUserName = LOWER(@UserName) AND ApplicationId = @ApplicationId

    IF (@UserId IS NULL)
        RETURN(1)

    select 
    a.RoleName
    from
    dbo.Roles a
    inner join dbo.UserRoles b on a.RoleID = b.RoleID
    inner join dbo.Users c on b.UserId = c.UserId
    where
    a.PortalID = 0 
    and
    c.Username = @UserName
    order by a.RoleName

    RETURN (0)
END

GO
/****** Object:  StoredProcedure [dbo].[aspnet_UsersInRoles_GetUsersInRoles] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[aspnet_UsersInRoles_GetUsersInRoles]
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications 
    WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(1)

    select a.UserName
    from
    dbo.Users a
    inner join 
    dbo.UserRoles b on a.UserId = b.UserId
    inner join 
    dbo.Roles c on b.RoleId = c.RoleId
    where
    c.RoleName = @RoleName
    and
    c.PortalId = 0

    RETURN(0)
END

GO
/****** Object:  StoredProcedure [dbo].[aspnet_UsersInRoles_IsUserInRole] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[aspnet_UsersInRoles_IsUserInRole]
    @ApplicationName  nvarchar(256),
    @UserName         nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications 
    WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN(2)

    if (exists(select * from dbo.Users a inner join dbo.UserRoles b on a.UserId = b.UserId
    inner join dbo.Roles c on b.RoleId = c.RoleId
    where c.RoleName = @RoleName and a.UserName = @UserName and c.PortalId = 0))
        return(1)
    else
        return(0)

END

Phew, that wasn't too bad. DotNetNuke is all set now to be used as a user store from SharePoint.

Create an extended SharePoint web application that uses Forms based authentication

Consider the scenario where you already have a SharePoint web application that uses Active Directory for authentication. We'll extend this application to use users from DotNetNuke. To do this, follow these steps:

1. Create a DNS or hosts file entry for the future SharePoint site. I am giving it a creative name: sharepointdnn.zeppelinsoft.com.
2. Log in to Central Administration and go to the Application Management page. Then, click on Create or Extend Web application.
3. Choose Extend an existing Web application. In my case, I am extending an existing application called SharePointDNN.

Be sure that you do the following:

• Choose the right application from the Web Application drop-down.
• Specify port 80, and enter the name you created in the first step in the Host Header entry (you can create a new name if you wish).
• Choose the zone to be Internet.

Modify SharePoint web.config files

Before setting our new extended application to use FBA with DotNetNuke, we need to make several changes to the web.config file in our SharePoint FBA application as well as in Central Administration. Let's start with the extended application (in my case, SharePointDNN.zeppelinsoft.com). Before making modifications, I suggest you make a backup. There are three entries we need to steal from DotNetNuke web.config and one new entry we need add to, so I would suggest to open both web.config files in Visual Studio and start doing the following:

Change the web.config for SharePoint FBA application:

1. Copy the connection string entry from DotNetNuke web.config.

<connectionStrings>
  <add name="SiteSqlServer" 
    connectionString="Data Source=(local);Initial Catalog=DotNetNuke;
                      User ID=xxxxxx;Password=xxxxxx"
    providerName="System.Data.SqlClient" />
</connectionStrings>

Paste it to the SharePoint web.config. I changed the name of the connection string to DNNSqlServer to make it more obvious for a web.config reader to know that it is pointing to a DNN SQL Server. Here is what's actually being pasted into the SharePoint web.config:

<!-- changed for FBA with DotNetNuke-->
<connectionStrings>
  <add name="DNNSqlServer" 
       connectionString="Data Source=(local);Initial Catalog=DotNetNuke;
                         User ID=xxxxxx;Password=xxxxxx" 
       providerName="System.Data.SqlClient" />
</connectionStrings>

2. Copy the <membership> section from DotNetNuke, then we'll make a few creative changes such as change the name of the provider to DNNMembershipProvider and the connection string to DNNSqlServer.

Initial membership entry in DotNetNuke looks like this:

<membership defaultProvider="AspNetSqlMembershipProvider" 
            userIsOnlineTimeWindow="15">
   <providers>
     <clear />
     <add name="AspNetSqlMembershipProvider" 
        type="System.Web.Security.SqlMembershipProvider" 
        connectionStringName="SiteSqlServer" 
        enablePasswordRetrieval="true" 
        enablePasswordReset="true" 
        requiresQuestionAndAnswer="false" 
        minRequiredPasswordLength="7" 
        minRequiredNonalphanumericCharacters="0" 
        requiresUniqueEmail="false" 
        passwordFormat="Encrypted" 
        applicationName="DotNetNuke" 
        description="Stores and retrieves membership data from 
                     the local Microsoft SQL Server database" />
   </providers>
 </membership>

Modified the membership entry in SharePoint:

<!-- changed for FBA with DotNetNuke-->
<membership defaultProvider="DNNMembershipProvider" userIsOnlineTimeWindow="15">
   <providers>
     <clear />
     <add name="DNNMembershipProvider" 
        type="System.Web.Security.SqlMembershipProvider" 
        connectionStringName="DNNSqlServer" 
        enablePasswordRetrieval="true" 
        enablePasswordReset="true" 
        requiresQuestionAndAnswer="false" 
        minRequiredPasswordLength="7" 
        minRequiredNonalphanumericCharacters="0" 
        requiresUniqueEmail="false" 
        passwordFormat="Encrypted" 
        applicationName="DotNetNuke" 
        description="Stores and retrieves membership data from 
                     the local Microsoft SQL Server database" />
   </providers>
</membership>

3. Add the <roleManager> entry. This is not found in DotNetNuke, so you have to manually add it. It is important to set the connection string to DNNSqlServer and the application name to DotNetNuke:

<!-- changed for FBA with DotNetNuke-->
<roleManager enabled="true" defaultProvider="DNNRoleProvider">
    <providers>
        <add name=" DNNRoleProvider " 
             connectionStringName="DNNSqlServer" 
             applicationName="DotNetNuke" 
             type="System.Web.Security.SqlRoleProvider,System.Web,
                   Version=2.0.0.0,Culture=neutral,
                   PublicKeyToken=b03f5f7f11d50a3a" />
    </providers>
</roleManager>

4. This is very important: Copy the <machineKey> as it is from DotNetNuke web.config into SharePoint web.config. Overwrite the existing entry. (You have a backup, right?). <machineKey> is used to hash passwords, and it is specific to each machine and application. Unless you set the passwordFormat attribute in DNNMembershipProvider to be Clear, the machineKey has to be identical.

Change the web.config for the Central Administration application

1. Apply the same changes as for the SharePoint FBA application 1 and 2, i.e., add the same entries for the SQL connection string and the membership provider.
2. Add the same <roleManager> entry, but default it to AspNetWindowsTokenRoleProvider. This is because we only need to let CA know about the DNNRoleProvider, but we still want to use Windows authentication for Central Admin. Here is the entry:

<roleManager enabled="true" defaultProvider="AspNetWindowsTokenRoleProvider">
    <providers>
        <add name="DNNRoleProvider" 
            connectionStringName="DNNSqlServer" 
            applicationName="DotNetNuke" 
            type="System.Web.Security.SqlRoleProvider,
                  System.Web,Version=2.0.0.0,Culture=neutral,
            PublicKeyToken=b03f5f7f11d50a3a" />
    </providers>
</roleManager>

Set SharePoint to use Forms based authentication

Now that we have all the entries in place in the web.config, we need to set the SharePoint application to use Forms authentication in Central Admin. Here are the steps:

1. Go to Central Administration -> Application Management -> Authentication Providers (under Application Security section).



2. Change the Web Application to the one we're working on (inn this example, SharePointDNN). Then, click on Internet zone.



3. Change Authentication Type to: Forms. Enter Membership provider name as: DNNMembershipProvider. Enter Role Manager as: DNNRoleProvider. Choose Yes for Enable Client Integration.



4. Now, you should see DNNMembershipProvider specified in the Membership Provider Name column.

Set the permissions for DotNetNuke users to access the SharePoint site

1. At this point, we have a site that's using Forms based authentication but no user is set to access it. So, go to Central Administration -> Application Management -> Site Collection Administrators (under SharePoint Site Management), and add host (the super admin user in DotNetNuke) as secondary site collection administrator.



2. Go to Central Administration -> Application Management -> Policy for Web Applications (under Application Security), and add Registered Users (a public role in DotNetNuke) to the Internet zone of SharePointDNN as readers.

Register in DotNetNuke and then login in SharePoint

With a little more effort, we can modify the SharePoint login page to contain a link to the registration page in DotNetNuke. Open ../12/TEMPLATE/LAYOUTS/login.aspx in Visual Studio, and add a link to the registration page in DotNetNuke. The link should look like this: http://dnn.zeppelinsoft.com/?ctl=register. And, the login page should look like this:

And, with another little effort, we can redirect the user back to SharePoint after registration by creating a page in DotNetNuke that contains a JavaScript code for redirection. To create a page in DotNetNuke, login as host and go to Admin -> Pages -> Add New Page. Let's call this page SharePoint Redirect Page. Then, add a Text/HTML module to the page. Edit it, and add a code similar to the following:

<script language="JavaScript">
<!--
location.href = 'http://sharepointdnn.zeppelinsoft.com';
// -->
</script>

Now, load the Membership Provider Settings page: go to Admin -> User Accounts -> User Settings. In this page, set Redirect After Registration to be the page we just created, SharePoint Redirect Page:

Now, if you try to access sharepointdnn.zeppelinsoft.com, you will be prompted with a login page and the option to register through DotNetNuke. After registration, you will be redirected back to SharePoint.

Of course, this is one of the simplest scenarios possible. DotNetNuke allows different types of user registration: public, verified, or private. In this example, it is public. If you want users to receive a confirmation email, set it to verified.

Conclusion

You can use DotNetNuke for SharePoint user management.

Well, this is just a little scratch over what you can do about user management authentication and registration using DotNetNuke with SharePoint. DotNetNuke is a mature system, very robust with a lot of enthusiastic users developing components day and night. You can get many things from DotNetNuke that are already done by others, such as user management.