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.