Background:
I was running SQL Server 2012 and could install DNN automatically with SQL Express or by creating a database and installing DNN with SQL Server 2012. I jumped to SQL 2012 without checking if my hosting provider had a SQL 2012 option. That was a big whoops as downgrading a database from SQL 2012 to SQL 2008 isn't supported. Not wanting to switch hosting providers I uninstalled 2012 via the uninstall wizards for it and its components. Then I installed SQL 2008 R2. This is where my problems started.

I am able to create a SQL Server 2008R2 database via SSMS and install DNN6 or DNN7 to that database. When I run the DNN install DNN is unable to connect to SQL Express. On DNN 6 it disables the auto install and then is unable to install to .\SQLEXPRESS on typical. DNN7 says: No valid default database connection detected. Standard Database setup option is unavailable.

If I try to attach to the database.mdf file with SSMS I get the following error: Directory lookup failed for the file "____" failed with the operating system error 3(The system cannot find the path specified.). Microsoft SQL Server Error 5133.

Both SQL Server and SQL Server Express log on as NT AUTHORITY\Local Service.

I have somehow messed up the ability to connect with SQLEXPRESS. I have tried setting authentication on SQL Express to either Windows Authentication mode or SQL Server and Windows Authentication mode, with no apparent difference.

I'm concerned that I have some underlying installation or configuration issue that will show up sometime in the future, probably when I'm not current on what led up to the issue. I've googled and checked everything I can think of on the SQL Server and SQL Express installation, though I'm just a novice at it. On the other hand I could be hopeful that when I eventually update to SQL Server 2012 these problems would go away.

Questions:
Is there an obvious problem from my description that I can address?
What could I do to further investigate this issue on the road to resolution?
Should SQLEXPRESS be set to Windows Authentication Mode or SQL Server and Windows Authentication mode?
Should I have my SQL Server Express log on as NT AUTHORITY\Local Service?

Thanks!

I'm up and running with 2008R2, 2012 and 2008R express instances. I'm able to create an SQL Express database through SSMS and connect to it from dotnetnuke. My conclusion is that when I setup windows and SQL Server authentication on the full SQL server installation then that required SQL Server authentication for the SQL Express installation, hence the auto install wouldn't work. Afer several installations and uninstalls I've had a sufficient dose of SQL Server education for now and I will be glad to get back to dotnetnuke.

Well, congratulations on that hard won education!

You should be able to use either Windows or Server authentication methods and the installer should handle either.

I find it much better to create an empty database via SSMS than to use the "Attach" method with the Express database that comes with DotNetNuke.