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:
- Create a new DNS or hosts file entry for the future DNN site. I am calling it: dnn.zeppelinsoft.com.
- Download DotNetNuke_x_y_z_Source.zip. Unzip it, and set up an IIS site that points to the …/Website folder.
- Be sure you use ASP.NET version 2.0 and that the Network Services user has permissions to modify that folder.
- If you don't want to use SQL Express, create an empty database for the future DotNetNuke site.
- 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:
- 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:
- 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. - 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:
- Use
SqlRoleProvider
in SharePoint and modify its corresponding SQL stored procedures in DotNetNuke to use its native role management tables. - 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:
- 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.
- 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):
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
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
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
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
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
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:
- Create a DNS or hosts file entry for the future SharePoint site. I am giving it a creative name: sharepointdnn.zeppelinsoft.com.
- Log in to Central Administration and go to the Application Management page. Then, click on Create or Extend Web application.
- 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:
- 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:
<connectionStrings>
<add name="DNNSqlServer"
connectionString="Data Source=(local);Initial Catalog=DotNetNuke;
User ID=xxxxxx;Password=xxxxxx"
providerName="System.Data.SqlClient" />
</connectionStrings>
- 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:
<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>
- 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
:
<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>
- 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
- 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.
- 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:
- Go to Central Administration -> Application Management -> Authentication Providers (under Application Security section).
- Change the Web Application to the one we're working on (inn this example, SharePointDNN). Then, click on Internet zone.
- Change Authentication Type to: Forms. Enter Membership provider name as: DNNMembershipProvider. Enter Role Manager as: DNNRoleProvider. Choose Yes for Enable Client Integration.
- Now, you should see DNNMembershipProvider specified in the Membership Provider Name column.
Set the permissions for DotNetNuke users to access the SharePoint site
- 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.
- 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.