DotNetNuke - Change connection settings Sql Express

Being relatively new to DotNetNuke, I set up my first site pretty quickly without much thought as to architecture. As a result, I just stuck with the default database configuration which means you have an .mdf file in your App_Data folder. I prefer to have my database files all in one place and attached to the Sql Server instance running on my machine so I decided tonight to switch them over.

I quickly fell in love with the ease of use of DotNetNuke and before I knew it, I had 4 or 5 live sites up and running on the platform. With live sites running, I found out that I had to shut down the web server in order to copy / move the database file. The easiest way for me to do this was go to the Services MMC and shut down the "World Wide Web Publishing Service".

From there I went to my DotNetNuke install directory and found the App_Data folder. Inside of this folder there will be two files, the .mdf and the .ldf files. The .mdf file is the "master database file" and contains your real data. The .ldf file is the "log data file" and contains the transactions log for your database. You need both of these files so copy both of them if you are moving them.

App_Data Folder

Once you have copied the files to where you want them, you are ready to attach them via Sql Server Management Studio (SSMS). Incidentally the default location for Sql Server 2005 database files is:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

This assumes that you have one instance of Sql Server running (MSSQL.1). If you have more, look under the appropriately named folder to find the files you are looking for.

Attaching Database Files

To attach the database files, you should have SSMS connected to your desired instance. Right click on the "databases" node and select "attach". The window below will pop up. Choose "Add..." and find the .mdf file you want to attach.

Attach Database

Pay special attention to the marked areas to make sure they point to the correct files and paths. The .ldf file should "come along for the ride" when you choose the .mdf file. If it doesn't, double check things before adding it manually.

Adding Logins and Users

While you can use windows authentication for your SQL Server connections (some say you always should!) I typically prefer to have login / users that I can assign to each database. In order to do this, the first step is create a login.

The login is different from the user in that the login gives you access to the database server. The user credentials give you access to a particular database. You associated a user with a particular login. The permission scheme in Sql Server 2005 is very robust and can be confusing but for what we need it is pretty simple.

To create a login, choose "Security" (a sibling node to "Databases") then choose "Logins". Right click and choose "New Login...".

New Login Guy

Select a login name
Choose "Sql Server Authentication
Uncheck the "Enforce Password Policy" box
Switch the default database to your DNN database

I always choose very long passwords for anything on a server. I use the open source program KeePass Password Safe to generate and keep track of all my passwords.

Once you have created a login, you must then create a user for your DotNetNuke database. Drill down into the "Database" node to find your DotNetNuke database. Then open up and find the "Security" node under the database. There, instead of Logins, you will find a Users node. Right click and select "New User..."

Fill in the UserName (I choose the same name as the Login I just created) then fill in the Login name. In the two boxes below select "db_owner". There might be a case to be made for a more restrictive permission but I selected db_owner for mine and it worked fine.

New User

Now your database is set up and ready to be accessed by DotNetNuke as well as SSMS. You can test the connectiong by creating a new connection in Object Explorer using your new Login information. If you did things properly, you should be able to login and should have access to only your DotNetNuke database.

Change Web.Config File

One last change needs to be made. Actually two changes were needed for my installation. The Web.config file contains the connection strings. In my file the appropriate section looked like this:

The key here is to replace the line:

With:

<add name="SiteSqlServer" connectionString="SERVER=DDW\SQLEXPRESS;DATABASE=DotNetNuke;UID=<USERNAME>;PWD=<PASSWORD>" providerName="System.Data.SqlClient" />

And then replace the line:

With:

These changes will update the connection strings for both the core DotNetNuke application as well as any modules that may happen to be using the <appSettings> key as the connection string.

Conclusion

Once you finish these changes you are ready to turn the World Wide Web Publishing Service back on and your web site should be running just fine using the copied database. This will give you concurrent access for making backups etc. while the site(s) are running against the database.

Welcome ›
Notes ›
DotNetNuke - Change connection settings Sql Express

Saturday, March 01, 2008, maswaffer