<add key="SiteSqlServer" value="Server=.\SQLEXPRESS;Database=DotNetNuke;User ID=dnn;Password=dnnpassword;Connection Timeout=5" />

in appsettings

Using SQL Server Express 2005 I used the following connection string:

.\SQLEXPRESS;Database=DotNetNuke;User ID=dnn;Password=dnnpassword;Connection Timeout=5

I manually created the database DotNetNuke and user dnn with the password dnnpassword.

Here is how my web.config file looked:

  <connectionStrings>
    <add name="SiteSqlServer" connectionString="Server=.\SQLEXPRESS;Database=DotNetNuke;User ID=dnn;Password=dnnpassword;Connection Timeout=5" />
  </connectionStrings>
  <appSettings>
    <add key="SiteSqlServer" value="Server=.\SQLEXPRESS;Database=DotNetNuke;User ID=dnn;Password=dnnpassword;Connection Timeout=5" />
    <add key="InstallTemplate" value="DotNetNuke.install.config" />
    <!--  Alternative Install Templates (included in package)
    <add key="InstallTemplate" value="Club.install.config" />
    <add key="InstallTemplate" value="Personal.install.config" />
    <add key="InstallTemplate" value="SmallBusiness.install.config" />
    -->
    <add key="AutoUpgrade" value="true" />
    <add key="InstallMemberRole" value="true" />
    <add key="ShowMissingKeys" value="false" />
    <add key="EnableWebFarmSupport" value="false" />
    <add key="EnableCachePersistence" value="false" />
    <add key="InstallationDate" value="1/25/2006" />
  </appSettings>

OK, I found the problem

I changed the "server=.\SqlEXPRESS"  to the name of my machine and it works.

You have to edit the connection string in two places in web.config:  <connectionStrings /> and <appSettings />. If you're still seeing the message about the "DotNetNuke_template.mdf" file, then you haven't edited BOTH connection strings.