15802 Views // 0 Comments // Not Rated

The Hybrid Provider - Authenticating Both AD And SQL Users In SharePoint 2007


One of the greatest leaps that SharePoint 2003 has made to its 2007 release is the fact it is now built entirely on top on ASP.NET 2.0. SharePoint can now even be thought of as an actual web application, just like any other. While 2003 hacked its way into IIS and ASP.NET, 2007 integrates nicely into its environment, dotting all the I's and crossing all the T's along the way.

Therefore, all the components that we use every day in our web apps should be able to seamlessly play similar roles in SharePoint. This includes web user controls, master pages, managed code, and, as this article discusses, authentication providers. These modules were painful (if even possible) to implement in 2003 and behave much more nicely in 2007. So if you ever find yourself frustrated with the development of some SharePoint functionality, just remind yourself: "Hey, this is just a web app I'm dealing with..."

Before we get started, there are two assumptions/assertions that I'd like to make. The first one is technical, and the second more philosophical. Assumption one is that any code we write is going to be deployed in the global assembly cache (GAC). While this may raise an eyebrow or two among administrators, I will always persist that it is safe, and solves a lot more problems than it raises. There are two crucial SharePoint advantages for this deployment paradigm:

  1. Full trust.  As we know, (and perhaps have staked the survival of project and/or our sanity on) code referenced from the GAC is allowed to run freely about the framework fully trusted.  The beauty of this is that it allows us to keep the trust levels of our web applications to the default "wss_minimial" setting.  I believe that no single piece of a portal should require the rest of it to raise its trust levels.  This way, we keep our individual sites in charge of their own security.
  2. Code in the GAC can easily be referenced by any and all web apps that may require it.  Although bin deployment is preferred for functionality specific to one app, (since it falls under SharePoint's more scrutinizing code access security) deploying to the GAC alleviates the messy business of copying DLLs all over our server.

The second assertion is on the behalf of our users. As far as they are concerned, authentication and authorization are all handled by two textboxes (one with letters and one with dots) and button that has the word "Login" on it. They don't care if they are logging into a Windows authentication popup box, typing their password directly into a web page, or dealing with some other mechanism. They want to type their username in one place, their password directly below it, and, if they choose, have the option to never be bothered with this nonsense again.

So in other words, the assertion is that the actual login mechanism is arbitrary. We don't need to be tied down to one authorization paradigm, and be forced to make the entire portal and even the server topology coincide with it. We should be free to build our site around what our users want, and simply make the authentication fit into it. And most importantly, authentication, when customized, can, in the very worst case, be as painful as it is now for our users.


We have a collaboration SharePoint portal for our projects that is shared by both our clients, and our developers, project managers, architects, and sales reps who are creating the solution. So how do we authenticate - and more importantly, authorize - these users? Well, in our 2003 environment, we settled on ADFS and running SharePoint in account creation mode...which worked...but didn't really give us all the control we needed.

So in 2007, we want to create a provider to take care of everything in this regard automatically. Our employees all already have AD accounts, and there is no reason not to leverage our domain controller. So that's easy: employees will be using Windows Integrated Authentication, and be authorized via their AD groups. And what of our clients? Well, we don't want to keep managing ADFS, and we don't want to have to create AD accounts for external users. So the answer comes to us straight from ASP.NET 2.0: the Login web user control, the AspNetSqlMembershipProvider database, and Forms-Based Authentication.

So now the real problem is clear: how do we implement two authentication providers at the same time? All of the research I've done in this area has led down dark and scary paths, all of which end up satisfying only a portion of our requirements...and all without even throwing SharePoint into the mix! So instead of a massive IIS hack or a messy AD-to-SQL replication solution, I decided (for a change) to pursue a more elegant solution: make one provider do the work of both.

Now, which one to chose? Since I only find myself wandering around IIS when I've broken something, (and if I haven't alluded to it enough already) we are going to go down the forms auth road. Personally, I will always prefer to code rather than to configure. However, if you don't, I can't foresee any reason why this technique can't be "reversed" to work with Windows auth. I haven't tested this, but feel free to go in this direction if your strengths are more in this area.


The solution to this problem comes in three pieces: first, we'll write some code; second comes deployment; and finally everything gets wired up. We'll be needing Visual Studio and a web farm containing a SharePoint 2007 instance, a domain controller with AD, and a SQL Server database. For development purposes, these can all be living on the same physical box or virtual machine. Finally, the SharePoint portal will have at least two web applications - one for the Central Administration site, and one for the Portal Site.

Let's start with the code for phase one.

  1. Create a new solution with a class library project.  Have the default class inherit from System.Web.Security.MembershipProvider.
  2. This is an abstract class, so we need to implement its members.  Go ahead and throw NotImplmentedExceptions for all but the following methods and properties:
    • Initialize
    • ValidateUser
    • GetUser (both signatures)
    • GetUserNameByEmail
    • FindUsersByEmail
    • FindUsersByName
    • GetAllUsers
    • ApplicationName
    • UpdateUser
    • EnablePasswordReset
    • EnablePasswordRetrivial
    • CreateUser
    • DeleteUser
    • RequiresQuestionAndAnswer
    • RequiresUniqueEmail
  3. Add two member variables, one of type SqlMembershipProvider and one of type ActiveDirectoryMembershipProvider.  These are our workhorses that will be handling all of the communication between SharePoint and the user data stores.  Note: for increased scalability, we can add more providers here without logically changing anything.
  4. Initialize the providers.  In our Initialize method, we have to pass our providers enough information so they can find their user data.  So what we need to do is create NameValueCollection objects, and manually feed them name-value pairs with such information as connection strings, administrative credentials, and other attributes.  Then we pass the NameValueCollections to the respective providers' Initialize methods.
  5. Fill in the stubs.  Our provider will act as a conduit between SharePoint and the underlying user data.  So all that is left is to do is basically route calls that SharePoint makes to our provider down to the member SQL and AD providers, aggregate their responses appropriately, and return a single response back to SharePoint.  Review the attached code for examples, and modify them for your purposes.  As you will see in the code, for queries, I hit both SQL and AD.  But since we don't want to mess with our AD forest, calls such as CreateUser and DeleteUser are only sent to SQL.
  6. Strongly name this assembly, and build it.  I build mine directly in release mode, since we'll never be debugging it (and because it'll work on the first try...right?).
  7. Create a new ASP.NET website to the solution.
  8. Add a web form, and make sure NOT TO NAME IT LOGIN.ASPX!  ASP.NET 2.0 will think that this page's class is the Login web user control, and you'll get a server error.  So call it something like "UserLogin.aspx."
  9. Drop a Login user control on this form.  The only modification required is to enter the name of the class created in Step One for the MembershipProvider property.  You can then add any code, design elements, or other functionality you may require.
  10. Build this page.

The second phase is deployment. I like to create a folder called something like "SharePoint" off the root on the server's hard drive to act as a staging area for assemblies, components, updates, documentation, and other files that pertain to my portal. The only files that go into web application's folders should be content-related: web user controls, ASPX pages, style sheets, etc.

  1. Copy the assembly from Step One of the previous section, along with UserLogin.aspx and UserLogin.aspx.cs, to the server.  
  2. GAC the assembly, and note the public key.
  3. Copy the UserLogin page and it's code-behind file to the portal site's web application folder.  By default, this is C:\inetput\wwwroot\wss\VirtualDirectories\<name of site or port number or whatever the administrator who created it specified>.  

Note: this can really go anywhere beneath this folder, or in the "global" layouts folder in C:\program files\common files\microsoft shared\web server extensions\12\template\layouts, but I prefer to have my portal content explicitly in my web application's folder, because that coincides more with how an ASP.NET web app works.

Next, we need to "deploy" the database. We'll use the ASP.NET SQL Server Setup Wizard to first create the standard database with all the tables and stored procedures that that ASP.NET SQL Membership Provider is already wired up to use. Then we only need to create a user that has full access to this database to reference in our connection string.

  1. Run "aspnet_regsql" from the Visual Studio 2005 command prompt.  (If you want to create the database directly on the server, then navigate to the following folder and run the executable: C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe.  Clearly, .NET 2.0 needs to be installed on this machine.
  2. This launches the wizard.  The steps are fairly basic: select a server, specify the database authentication mechanism, and then keep clicking next.
  3. When the wizard is done, click finish.  Log into your new database and make sure that the permissions, as well as the user that will be represented in the connection string, are set up accordingly.
  4. Optionally, we may need to create one user that will be the site collection administrator.  However, once the provider is working, we can always just use an AD account.  But for those of you who may be only using SQL at this point, we just need a user to start with.  The easiest way is to use Visual Studio 2005's web site administration tool.  Create a new web site, and add a connection string to the "aspnetdb" database (where ever it is) in the <connectionStrings> section in the web.config file.  Then launch the admin tool, and use it to create users.  The quickest way, however, is to directly execute the stored procedures in the new database...but you didn't hear that from me...

Finally, it's time to roll up our sleeves and wire everything together. The last phase, configuration, can be the most tedious because we'll be updating web.configs, making settings changes in Central Admin, and generally doing things that, when not done correctly, can crash the entire portal. Note: if you already have a provider in place and existing users and groups, changing the provider will invalidate those users (since, "provider1:username," for example, is technically different than " provider2:username").

  1. First, we need to wire up the web.config file for the Central Administration web application.

    Code Listing 1

    1. <membership defaultProvider="[PROVIDER CLASS NAME]">
    2. <providers>
    3. <clear />
    5. <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" />
    6. <add name="AspNetActiveDirectoryMembershipProvider" type="System.Web.Security.ActiveDirectoryMembershipProvider, System.Web, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalAdServer" />
    7. </providers>
    8. </membership>
  2. Add the following lines to the <connectionStrings /> section.  This defines the connection strings that our providers will use to initialize themselves.

    Code Listing 2

    1. <clear />
    2. <add name="LocalSqlServer" connectionString="[SQL CONNECTION STRING]" />
    3. <add name="LocalAdServer" connectionString="[AD CONNECTION STRING]" />
  3. Change the <PeoplePickerWildcards> section to look like the following.  This defines the wildcard character that will be used by our provider for user searches.

    Code Listing 3

    1. <PeoplePickerWildcards>
    2. <clear />
    3. <add key="[PROVIDER CLASS NAME]" value="%" />
    4. </PeoplePickerWildcards>
  4. Save the file, and click Start->Run->iisreset->OK

Now, we can go into the Central Admin and "point" SharePoint to our provider. There are three main tasks to take care of: enable forms auth on our web app, set our provider as the membership data source, and finally designate users to be the primary and secondary site collection administrators for our site.

  1. Log on to the SharePoint server, and fire up Central Admin.
  2. Click on "Application Management"
  3. Make sure we are currently viewing the settings for the web application of our site.  To make sure, click the "Web application list" link in the SharePoint Web Application Management section.  In the next page, click the name corresponding to the desired web app.
  4. Next, we need to make our provider the one this web app will use.  In the Application Security section, click "Authentication providers."  Then click on the appropriate zone (which in most cases will be called "Default" and probably be the only one listed).
  5. A page of settings is loaded.  Select the "Forms" radio button in the "Authentication Type" section.
  6. Enter the name of the membership provider class created in Step One into the textbox in the "Membership Provider" section.  Click OK, and then navigate back to the Application Management tab.
  7. Click on "Site collection administrators" in the SharePoint Site Management section.
  8. This will be the first test for our provider.  Enter the usernames for the primary and secondary site collection administrators in the two people pickers on this page.  If the users are not found, then make sure that everything has been done correctly up to this point.  Click OK.

Last task: we need to configure our site's web app to be able to authenticate users from our membership provider.

  1. First, we need to reference our code.

    Code Listing 4

    1. <add assembly="[PROVIDER FULLY QUALIFIED ASSEMBLY NAME], Version=[VERSION NUMBER - x.x.x.x], Culture=neutral, PublicKeyToken=[PUBLIC KEY NOTED FROM GAC]" />
  2. Change the <authentication> section to resemble the following.  This example will load our login page's web form corresponding to the "C:\Inetpub\wwwroot\wss\VirtualDirectories\<web application folder name>\UserLogin.aspx" physical path.

    Code Listing 5

    1. <authentication mode="Forms">
    2. <forms loginUrl="/UserLogin.aspx" />
    3. </authentication>
  3. Save the file, and do another IIS reset.
  4. Navigate to your site, and log in as the primary site collection administrator.  Once you're in, you can begin assigning permissions for your portal site to all of your Active Directory and SQL Server users!

Visit my CodePlex Project for the latest version of the code.


And there you have it! This method uses forms authentication on a SharePoint web application to authenticate users against both SQL Server and Active Directory. Not only will this hybrid provider be able to manage users across the two most prevalent user data stores in Windows, but it can also be scaled out to support other .NET membership providers, as well as run custom code to query into any source of user information.

Remember, SharePoint 2007 is an ASP.NET 2.0 web application. Any troubles your have along the way of creating and deploying a portal with this technology can be dealt with - and overcome - by applying the standard techniques that you'd normally use to go after any other ASP.NET issue you've encountered before. Have fun!

No Tags

No Files

No Thoughts

Your Thoughts?

You need to login with Twitter to share a Thought on this post.