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
Find " aspnet_regsql.exe ," (probably under
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.
The first few screens are obvious.
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.
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.
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
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
Open a folder to the file-system and make a text file called test.txt, and
then rename the extension to .UDL.
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.
Select integrated security, and then select the aspnetdb db (or whatever
you named it with the RegSql tool).
If clicking Test says it works, then you did well. Otherwise you have
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.
Open Visual Studio (I am using 2008, but it should be possible to use
2005 as well).
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.
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.
Expand the connection strings to use a proper open and close as we will
add our own here shortly.
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.
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).
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.
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.
Copy the entire line from the Machine.Config and paste it in the line
under your <clear/> key in the Web.Config.
take the connection string from the .UDL file and replace the connection string
section of the line in that key.
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
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
Click the security tab.
Click the Wizard link in the middle of the page.
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.
Click through to step five (I skipped roles for now) and add a few
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
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
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.