Earlier versions of SharePoint were primarily designed for internal use, using ADS or Windows to contain user accounts. Based on product upgrades that make SharePoint more desirable to use outside of a companies LAN, or on both sides in various configurations, many managers are stuck deciding if they want to consider the practice of using ADS accounts. By default, SharePoint (WSS 3.0 and MOSS) is configured to use Active Directory accounts or Windows user accounts to allow specific access, where a non-anonymous login is required. The easy argument for using ADS/Windows accounts is that all of the user management tools are at your disposal. But there are at least two possible problems and one misnomer about this. The first problem is that user accounts in ADS or Windows cost money, based on your configuration. It can be said that this is not a problem, based on the licensing methodology used, but there is a second problem that everyone potentially faces… security.

If an external user managed to "bust out" of the box, then he or she potentially has some level of access on your LAN. It could be argued that many practices like group usage, auditing, etc. will be used. It can also be argued that allowing a user a login on a single windows box (the SharePoint server, presumably) is safer than a locked down ADS account. But no matter how you do this, if a user COULD bust out or through (far more likely due to, or aided by, a mis-configuration than by pure hacking) and also have an account on your LAN, how well are you going to sleep? Another point is that a load balanced situation (one SQL server and two web servers perhaps) has the potential of forcing you back into ADS usage, because (for example) an embedded ASP.NET application might require an account that needs to (potentially) be on more than one server to work correctly. In the case of using SQL, both web servers can pull from the same SQL server. Yet back to ADS, one obvious answer here is put them on ADS, where more than one server uses the same central data source. But we are then back to the same concern. You could run multiple ADS structures but this has pitfalls, and is unlikely. And finally, a misnomer to debunk is the fact that once a user account is created, there are not too many things that are often done to these accounts. It may be possible you are making Exchange accounts for these users, but clearly that would be a need to an ADS usage. We are proposing a situation where many to thousands of users are needed, yet they are basically bare. And once the users are made, systems inside of SharePoint will be used to create and delete groups, will then be used to control access to objects such as webs, sub-webs, documents and lists.

Then comes the idea of creating and storing these accounts in SQL. And there are many reasons for this to be done, not the least of which is based on not only on price, but on security as well. Two examples of security include 1. The web interfaces that may be needed for users to create their own accounts never deal with ADS(I) components and 2. There is no correlation between users of SharePoint (stored in SQL) and users on the LAN, if security problems do come up.

So, of course, the primary question many people have is, how is this done? To be honest, it is not too tough, nor too easy. Microsoft has added some features that assist you in this process, but they have not done all of the work… trust me, at this point you do not just "throw a switch."

But, as a simple heads up of what is to come, you will use Forms Authentication along with SQL. Even with that knowledge, the details can be considered complex. This document will attempt to demystify this process.

 

Finally, before continuing, the reason that this document is labeled 1 of 2 is because, as I attempt to do in many of the articles, I will try to display for you a simple process of getting started, how it works, and why, then another article will come along to discuss more finely grained details. It may or may not be obvious what I am referring to by the end of this document, but in the beginning of the other documents it will become more obvious.

I am going to start with the database first and work from the back forward. Although you can do this any way you want, I feel it helps to work from the back forward. This part, building the database, is kind of odd in that it is semi automated, depending on how you work it. You may expect that we are going to get into the SQL components manually, creating tables and stored procedures by hand, but no, this is not the case… which is probably the odd part of this all. I appreciate the fact that Microsoft has automated portions of this process, but not all of it. Maybe we will be able to just flip a switch in the next version, but not yet. And sadly, it does aggravate me that one may try to classify WSS in relation to DotNetNuke, any of the Linux xNukes, Joomla and so on, it simply is not there yet; very simply because of two reasons, the biggest being that people think Microsoft and they think it will cost money. Yes and no… yes, in that you can do all of this on Windows 2003 Server, Web Edition, and WSS (as opposed to MOSS) and it does not cost "much" (only the Windows Server). And I will tell you right now that there are a significant number of people out there who will choose Windows over Linux because of familiarity. But then they need to consider portal price. DNN is free, but WSS requires Windows accounts, which cost money. I admit, there may be some room in the license condition you select (for example, SBS using 75 devices and unlimited users), but I am going to assume that you, like me, think this is cheesy, and not an option in an enterprise business. Besides the fact that if you code wrong and a user "busts out," they are some level of a LAN user. And again, we all know that the majority of the business world does not think LAMP (Linux, Apache, PHP, MySQL) and does not think DNN (which is not a Microsoft supported product, and made VB.NET at it's heart), but, again, the Windows user licenses come up if you don not think SQL stored accounts. And it has, of course, already been selected to be on Windows, at least until SQL is ported to linux (an excellent April Fools Joke from a few years back… step back Mono'ers… I'm a friend, trust me). And then the final consideration, as a developer you know WSS/MOSS means personal $$$. You go where the money is; so play with LAMP or DNN if you want, but I am choosing the money option. I mean that employers will pay for development on SharePoint, and rarely on the others. Thus, modifying WSS to use SQL is the way to go. Nuff Said.

Building the database required

1.    Find " aspnet_regsql.exe ," (probably under C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 ").

2.    You can run the executable from the command line, assuming that you used the right switches, but I suggest just running it, which will launch it as a basic windows forms wizard.

3.    The first few screens are obvious.

4.    The server name used may change based on the named instances you have. The location of the database to be used is your choice. I am going to add it to the same named SQL instance that holds my other SharePoint related databases.

5.    Notice that the database name is set for the default. You will see it uses its own name, "aspnetdb." You can change it to whatever you want, just recall this in later steps.

6.    The wizard will produce the tables, procs, etc. Once the wizard is complete, look into the database using either the SQL development studio or Visual Studio for familiarization.

 

Creating the SQL Server Connection

1.    Ever since the days of, well, a long time ago, there has been the ability to create an .UDL file to create or test a connection to a data-source. This is a text file that you change the extension and Windows uses it far differently.

2.    Open a folder to the file-system and make a text file called test.txt, and then rename the extension to .UDL.

3.    Click the provider and select Sql. Click next and type in the name of the server or Sql instance you are using. You can use "(local)\<instance name>" if you want.

4.    Select integrated security, and then select the aspnetdb db (or whatever you named it with the RegSql tool).

5.    If clicking Test says it works, then you did well. Otherwise you have homework.

6.    Now open the file with your trusty text edit and everything after "Provider=SQLOLEDB.1;" is your connection string.

Connecting to the Database and Adding Users

Now we are going to create a small, file-based, web application site that will be used to connect to, and manipulate your user db. Check that, we are going to begin a new project, but we are only using it for demonstration purposes, we will not use it for the entire project.

1.    Open Visual Studio (I am using 2008, but it should be possible to use 2005 as well).

2.    Create a file-base website. I am using an empty, C# website and locating it at "C:\Inetpub\Logins." It is not urgent that it is empty as we are only really using the web.config.

3.    If it does not already exist, add a Web.Config. There is a lot of stuff here that is simply not required. So for the sake of simplicity, I have deleted all but the most essential information that we will use.

4.    Expand the connection strings to use a proper open and close as we will add our own here shortly.

5.    Next, go into the connectionStrings key and add a <clear/> tag. This is because we want to over-ride what is used from the Machine.Config file, although we will use a line that is in the Machine.Config to get started.

6.    Open the Machine.Config, which is likely located at " C:\ WINDOWS\ Microsoft.NET\ Framework\ v2.0.50727\ CONFIG ." (NOTE: I have added spaces at the end of each back-slash in the previous line, so it fit well, take them out for your need).

7.    Look for the word "LocalSqlServer." You will find it used in a few conspicuous spots. The first is the location which is that of the connection string. It is pointing to the default location used by the install of Visual Studio 2005 (probably 2008 as well, but I installed 2005 then 2008, and I am using 2008 in this example, so I am not positive). Then continue down (perhaps bookmark them all for easy viewing) and you will find it with the three following components of functionality: AspNetSqlMembershipProvider, AspNetSqlProfileProvider, and AspNetSqlRoleProvider. I am sure they seem familiar and you are seeing a plan at work here.

8.    What this means is that the three points of functionality above all use the LocalSqlServer key. By performing the clear in the Web.Config, the website uses the Machine.Config, but then it overwrites its use with the key you are about to add to your work.

9.    Copy the entire line from the Machine.Config and paste it in the line under your <clear/> key in the Web.Config.

10. Now take the connection string from the .UDL file and replace the connection string section of the line in that key.

11. I know it may be temping, but keep it all on one line, despite what I am showing you here, because something you will do soon will mess it up if you try to break the lines up.

Figure 1: Machine.Config line and the New Line

Configuring the new database with users

1.    This is another spot where it gets interesting, and we will use more built in tools. In Visual Studio, at the top menu, click on Build | ASP.NET Configuration. A page will launch that represents the built in ASP.NET administration tools.

2.    Click the security tab.

3.    Click the Wizard link in the middle of the page.

4.    On the second page, select the Internet Option at the top. This will change the Web.Config to use Forms instead of Windows in this web application… if we were really going to use it.

5.    Click through to step five (I skipped roles for now) and add a few users.

6.    Look at step six. What this is saying is that no one is allowed in unless they are in the database. Ensure that the access management configuration is setup according to the images below. Basically this says that all users IN THE DATABASE should be allowed in.

Figure 2: Configuring a rule such that all (non-anonymous) users are allowed in

Figure 3: This is the way the access rule should look

7.    Step through and finish the Wizard. You will notice more options at the end that are not going to be used in this article, but should give you some ideas.

8.    Alt+Tab back to Visual Studio. If you split up the lines, you will see what I mean about the Wizard destroying the connection string. A little work will allow you to piece it back together.

To this point we have created a database, a connection string, a Web.Config, copied data from the Machine.Config to the Web.Config, changed the Web.Config for our needs and connected to the database, used the Web.Config and connection string to connect with a Visual Studio tool and created users. This is all build-up to our next effort, because if all works as it should, that database will replace the Windows database for the SharePoint site we are using in this example.

1.    With another copy of Visual Studio, open the root of the SharePoint site in question by locating the file root via IIS so you can find the Web.Config.

2.    Open the Web.Config in the site.

3.    Highlight the entire page contents and press Ctrl+m+m, which will collapse the page.

4.    Open Configuration key, then clear a line just above the <SharePoint> opening tag.

5.    Paste the connection strings section from the first Web.Config you have been working on, to this one.

Figure 4: The added connection string

6.    In Figure 4 you can see the addition of the connection string, named LocalSqlServer. This line contains a pointer to the aspnetdb database and references the System.Data.SqlClient assembly.

7.    Now before we continue we need to make a change inside the site. Open the website you are working on by logging in as an administrator. Go to Site Settings, Advanced Permissions, pull down the Settings Tab and click Anonymous Access. Check the Entire Website radio button and click ok. Close out.

8.    Now, open the SharePoint Central Administration website. Go to the Application Management tab, under Application Security select Authentication Providers, use the Web Applications tab to select the application you are after, in my case a very simple and blank site called "siteb."

9.    Click on the Default link and now the information we are presented with, along with our previous actions, puts us close to the completion of our goals.

10. Click on the Forms radio button, which will change the screen in a few ways.

11. Click on Enable Anonymous Access.

12. As you will find in the Machine.Config, add the word "AspNetSqlMembershipProvider " in the membership provider name box.

13. Click save, close out all appropriate open files, open the command prompt and run IISRESET.

14. If you do not already have one, create a basic user on your server. Then open another session to the same server by typing " mstsc /v:<local host name> " and log in as that user.

15. With that user account logged in, reopen the site you are working on, "siteb" in my case.

16. You should now see the front page as an anonymous user.

17. Click on the Sign-In link in the upper right, and you should be presented with SharePoint pre-created login and password box pages.

18. Login as one of the accounts that you had previously made inside SharePoint many steps earlier. Notice that you are logging into Windows as one account, stored in Windows (or ADS) and then you are logging into WSS as a non-Windows user (even as an internet anonymous user, as the goal is), with, of course, the WSS account stored in SQL Server (again, in my case the SQL Server is a named instance, but it could be the main, and maybe even an SQL file).

19. If all goes according to plan, you are done.

20. In conclusion, we have demonstrated the act of switching one WSS site to use an SQL server to store user accounts. The primary reasons for this are two-fold. One is the intent to save money by purchasing less CAL's to allow people into your WSS site (not all situations apply but the intent to do things this way could help you convince bean counters). The second main reason is security related. If a WSS user gets out of the WSS sandbox, at least there is no direct correlation between WSS accounts and Win/ADS accounts. This of course should not be the sole method of security review, although it may help your case in enlisting the security gurus.

21. What we have not covered is also fairly significant. On uncovered detail is the use of multiple SharePoint zones (for example, allowing some users to log into a content site from one direction, such as internal, ADS users, or external SQL accounts). Other uncovered details are highly detailed examples of user permissions and mixing the two together. This will all come together in part two.

In this article you have learned the steps required to configure forms authentication in SharePoint using SQL Server with the help of detailed analysis and relevant screenshots.