Hello:
There are different ways to connect to SQL Server/Express, but this has more to do with security and not necessarily with using SQL 2000, SQL Desktop, SQL 2005, or SQL Server Express. One resource I find helpful is http://www.connectionstrings.com, especially if you have to connect to MS Access, Oracle, etc.
In this case we are dealing with SQL Server, the desktop version AKA SQL Server Express. Your connection string will most probably be in one of two formats for DNN:
A) connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Database.mdf;"
B) connectionString="Server=(local);Database=DotNetNuke;uid=;pwd=;"
I simply pulled these from the DNN 4.x web.config file. Option (A) is terrible from a security standpoint and Microsoft recommends not using it. As a result, I would setup a user account and password within SQL Server Express and use Option (B). Also, option (B) was/is standard in 3.x and a consistent standard in connecting to a SQL Server database on behalf of some service.
Here is what I would do in setting up your database and connection string:
- If possible open SQL Server Management Studio (SSMS). (SQL Server Express should have something similar).
- You will see a "Connect To Server" dialog window. Use the following values:
- Server Type: Database Engine
- Server Name: ".\ThisIsWhatNeedsToBeYourSQLSrvInstance" (i.e. ".\SQLExpress", or ".\MSSQLServer2005", ".\MSSQLServer2000")
Note that there is a DOT or Server Instance with a backslash "\" rather than a forward slash which is a common error, and that MSSQLServer2005 and MSSQLServer2000 that I've used are completely arbitrary and are simply names that I use when I install MS SQL 2K and MS SQL 2K5 on my computer. Just an FYI that ".", "(local)", and your machine name all mean the same thing, really. When or if you deploy to a webhosting company, you will need to enter their server name in place of the ".\Whatever", etc.
Also, if you look at the connection string for (B) above and have noticed that only "(local)" is present, you would be correct in thinking that there is a descrepancy here. The reason you do not see "(local)\MSSQLSomething" is because "(local)" was used pre-MS SQL 2005 when most people only ran MS SQL 2000. Since most people switching or migrating to MS SQL Server 2005 have both MS SQL 2000 AND MS SQL 2005 running off of their computer we need to add the addtional "\ServerInstance" to make this distinction between MS SQL 2000 and MS SQL 2005.
3. Authentication: Windows Authentication or SQL Server Authentication using sa with the correct
password.
3. Once connected on the left you "should" see an "Object Explorer" pane that will allow you to expand the
Database leaf - Expand the Database leaf to view all the databases (Microsoft installs some by default).
4. If you do NOT see your database or have not created your database, then do so at this point.
DotNetNuke's installation program does not create your database. It does create the tables, stored
procedures, views, and other objects within it, and it does load your database with base data, but you need
to create the database yourself - often refered to as shell or empty database. If you are confused or
concerned about what version of MS SQL Server you are creating your database in, an easy way to
distinguish between the two is that SQL 2000 will show "SQL Server 8.0.2039" in the Summary tab when
selected, and SQL 2005 will show "SQL Server 9.0.1399".
I mentioned above that option (A) was or is not the preferred connection string because it implies the use of trused security scheme which is a potential security issue. Therefore, I would recommend that you complete the following steps and use connection string (B) as a connection template to your database.
5. Expand the "Security" leaf that is off the registered SQL Server (same level as Database and NOT under the
database you created in step (4)...).
6. Under Logins right click over Logins and select "New Login...". Add a Login Name of choice and click the
SQL Server Authentication radio button. Add the pwd of choice and confirm it's entry. Just below this
uncheck "Enforce password expiration" and "User must change..." check boxes. Click OK to create the
new Login account.
7. Right click on your new Login and select Properties (I know, I brought you right back to that previous
windows... ). From the left select "User Mappings". Locate your database and check the checkbox.
8. In the lower panel, "Database role membership for: YourDatabaseCreatedInStep4" check db_owner and
public (if not checked already). This gives this Login dbo rights, which are needed, and will apply them to
your database. You can verify this in the Object Explorer by drilling into
YourMachineName\YourSQLInstance\Databases\YourDatabaseCreatedInStep4\Security. You will see
your newly created SQL Server UID here.
Note: This is not a Windows NT domain account, which has been a point of confusion as well. Although you can use a domain account it is not the standard practice to do so.
You are all set to try to load your site again. Your connection string will be similar to the following:
<!-- Connection String for SQL Server 2000/2005 - kept for backwards compatability - legacy modules -->
<add key="SiteSqlServer" value="Server=.\ThisIsWhatNeedsToBeYourSQLSrvInstance ;
Database=YourDatabaseCreatedInStep4;uid=YourUIDOfChoice;pwd=YourPWDOfChoice;"/>
Delete or comment out the SQLExpress connect string, if it exists in your web.config, and save. You can know open up a instance of Internet Explorer and browse to your website. I've assumed that you created your Virtual Website, so if you have not done so you will need to perform this step and also the NT Permissions on the folder itself by adding aspnet user account with full access. For those who think that setting up a DNN site is a cake walk, you are correct. For those who think that it is a royal pain and an incredibly long and arduous process, you are correct too. Once you learn how to do something and have done this something many times, it is very easy. For everyone else, there are plenty of people who enjoy helping when they can.
Best of luck!
John