I believe your problem is with the .Net2.0 Membership and Roles providor.  .Net20 allows you to use SQLServer2k+ to manage centrally, your registration/login/roles information.  This requires you to set up a seperate SQLDB for the providor, hence the fact that your error is referencing connectionStringName="SiteSqlServer". You need to find out where "SiteSqlServer" is pointing to. My guess is that you never set up the roles DB.  Take a look at the .Net Quickstarts, they tell you how to set it up.

-e

The problem you are experiencing is probably this:

When you remove the connection string for the Express 2005 provider then you are left with this:

    <!-- Connection String for SQL Server 2000/2005
    <add name="SiteSqlServer"
      connectionString="Server=(local);Database=DotNetNuke;uid=;pwd=;"
      providerName="System.Data.SqlClient" />
   -->

This code is commented out with the <!--   --> brackets. This would result in an error like the one you are describing.

If you change it to this:

    <!-- Connection String for SQL Server 2000/2005   -->
    <add name="SiteSqlServer"
      connectionString="Server=(local);Database=DotNetNuke;uid=;pwd=;"
      providerName="System.Data.SqlClient" />

and fill in your details about the server, db, user and password it should work fine.

Guys - DNN 4.x Application Strings settings assumes you're using SQL 2005 Express. If you're using SQL 2000 or 2005 you'll need to edit the application string and connections strings to include this line.

To use a SQL Server 2000 or 2005 database replace the whole connection string by something similar to

Server=(local);Database=DatabaseName;uid=LoginName;pwd=LoginPassword;

Here's the copy and paste from the Welcome.html page in the documents folder in DNN install.

The examples shown above are for the default SQL Server 2005 Express database file "Database.mdf".  If you used the defaults when creating you SQL Server Express 2005 database, you will not need to make any modifications to the connection strings. If you used a different filename for the database file, you need to change the AttachDBFilename=|DataDirectory|Database.mdf; section.

To use a SQL Server 2000 or 2005 database replace the whole connection string by something similar to

Server=(local);Database=DatabaseName;uid=LoginName;pwd=LoginPassword;