I want to be able to view tables, run queries, and make changes to the database through Sql Server 2005 Management Studio Express while the website is running. So far, I haven't had any success. Here's all the stuff I tried... any ideas what I am doing wrong?

The web.config file uses a user account called gorilla that I made in SQL Server 2005 Express when I added a database called DotNetNuke according to the installation notes.

After accessing the DNN website with IE, both the .mdf and .ldf file in the App_Data directory list these under security:

Administrators (full access)
ASP.NET Machine Account (modify access)
SQLServer2005MSSQLUser$HIGHLANDER$SQLEXPRESS (HIGHLANDER\SQLServer2005MSSQLUser$HIGHLANDER$SQLEXPRESS)
(full access)

Upon rebooting and not opening IE, the same 3 users are on the list.

After rebooting, I log in to SSMSE using the SA account.
I right click on Databases in the SSMSE, Attach…, choose Database.mdf from the App_Data directory, press OK and attach it no problem as “C:\DOTNETNUKE\WEBSITE\APP_DATA\DATABASE.MDF”.
The owner is listed as sa in SSMSE.
When I select the Database in the tree, nothing lists underneath it but the tooltip lists the name followed by (In Recovery).

After having attached the database, I try to access the website again.and get this error message:
Error Installing DotNetNuke
Current Assembly Version: 04.03.00
ERROR: Could not connect to database specified in connectionString for SqlDataProvider

Next I go in SSMSE, right click on the database, choose tasks… detach
I check Drop Connections because a message on the right says under status “not ready” and there is “1 active connection” listed under message
I uncheck Keep Full Text Catalogs
It detaches without a problem
I close IE and open a new one, then browse to the website again. I get this error this time:
DotNetNuke Upgrade Error
The Assembly Version ( [ASSEMBLYVERSION] ) does not match the Database Version ( [DATABASEVERSION] )

ERROR: Could not connect to database.

Cannot open user default database. Login failed. Login failed for user 'HIGHLANDER\ASPNET'.

Both the .mdf and .ldf file in the App_Data directory list these under security:

Administrators (full control)
NETWORK SERVICE (full control)
SQLServer2005MSSQLUser$HIGHLANDER$SQLEXPRESS (HIGHLANDER\SQLServer2005MSSQLUser$HIGHLANDER$SQLEXPRESS)
(full control)

I add an ASPNET user again with modify permission for both the .mdf and .ldf files, and the website works again.

While the website is open in IE, I open SSMSE and try to attach the database. This time I get an error in SSMSE:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical ‘C:\DotNetNuke\App_Data\Data\Database.mdf’. (Microsoft SQL Server, Error: 5123)
I then close SSMSE and restart it, and login as gorilla. I try to attach the database but am stopped before even selecting the file this time with the following error:
C:\DotNetNuke\website\App_Data
Cannot access the specified path or file on the server. Verify that you have the necessary security priveleges and that the path or file exists.
If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

The locate Database window doesn’t show any file tree, so I type in Database.mdf and press return. I get the following error:
Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)
Additional information:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
User ‘guest’ does not have permission to run DBCC checkprimaryfile. (Microsoft SQL Server, Error: 2571)

When I check the permissions for the mdf and ldf, they are the same as before (still includes network service and aspnet with same permissions as before)
I refresh the website in IE and it still loads.
I add everyone as a user and give it full control
I try to attach the database again and get the same errors.
I close SSMSE and restart it and login as sa.
I try to attach the database again and it works completely this time, I can see all the tables. But when I refresh the website, I get:
Unhandled Error
Error Details
File
Error Cannot open user default database. Login failed. Login failed for user 'HIGHLANDER\ASPNET'.
I check the file permissions again on the .mdf and .ldf and they are:
Administrators (full control)
NETWORK SERVICE (full control)
SQLServer2005MSSQLUser$HIGHLANDER$SQLEXPRESS (HIGHLANDER\SQLServer2005MSSQLUser$HIGHLANDER$SQLEXPRESS)
(full control)

Note that everyone and aspnet have been removed, apparently by attaching the database with SSMSE.

Note from http://msdn2.microsoft.com/en-us/library/ms228037.aspx
SQL Server Express Edition allows only a single connection to an .mdf file when you connect with a connection string that has User Instance set to true.

I changed from User Instance=True to User Instance=False in both connection strings of the web.config file in the DNN folder.

After rebooting, I get the the following error when accessing the website:
Error Installing DotNetNuke
Current Assembly Version: 04.03.00
ERROR: Could not connect to database specified in connectionString for SqlDataProvider
I check the file permissions for the .mdf and .ldf and aspnet is missing still.
I add aspnet again with modify permission.
I press refresh on the website but get the same error.
I change User Instance=False back to User Instance=True back in web.config file. I refresh the website and it works fine again.

 

I'm having a problem accessing my database.mdf file too. I was new to DNN and set up my site with all defaults using the folling web.config entries. Site is up and running, but now I want to open up my database and back it up. What can I do to open up the database using INTEGRATES SECURITY?

I don't know what the database username and password is.

 

 <connectionStrings>
    <!-- Connection String for SQL Server 2005 Express -->
    <add name="SiteSqlServer" connectionString="Data Source=.\SQLExpress;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />

  </connectionStrings>
  <appSettings>
    <!-- Connection String for SQL Server 2005 Express - kept for backwards compatability - legacy modules   -->
    <add key="SiteSqlServer" value="Data Source=.\SQLExpress;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True" />
  

One more thing: After mounting the DB in SSMSE, I noticed that the full access rights assigned to the ASP.NET account (XP) have disappeared. As a result, I also had to reset the rights (full control) on the DB files in the App_Data folder.

Also, when mounting a SQL Server 2005 database into SQL Server Express, I didn't encounter the "in recovery" issue. I still had to reset the security for the ASP.NET account but encountered no other issues.

Andrei