Configure SQL Server 2005 Express

Install and setup
A step-by-step guide to install and configure everything you need to run umbraco 3.0.x on Windows XP Pro. (including IIS, MSXML, .NET Framework, AJAX, SQL Server 2005 Express, SQL Server Management Studios Express, and umbraco 3.0.x)

Contents

• Introduction
• Install IIS
• Install .NET 2.0 Framework
• Install ASP.NET AJAX 1.0
• Install MSXML
• Install SQL Server 2005 Express Edition SP2
• Install SQL Server Management Studio Express
• Configure SQL Server 2005 Express
• Install umbraco 3.0.x Files
• Configure IIS
• Configure umbraco 3.0.x
• Congratulations!

Configure SQL Server 2005 Express

There are many steps here, so take your time and be sure you perform each step. Various problems can occur if you don't have SQL Server configured properly.

Special thanks to Brian on the umbraco forum for his excellent outline of the steps to configure SQL Server 2005 Express for umbraco 2.1.x.

Trouble-shooting notes are shown at the end, if you have any problems.

SQL Server 2005 Configuration Manager

1. Click Start, click All Programs, click Microsoft SQL Server 2005, click Configuration Tools, and click SQL Server Configuration Manager.
   
2. Expand the SQL Server 2005 Network Configuration folder, click on Protocols for MSSQLSEVER.
3. Right-click on the TCP/IP protocol, and select Properties.
   
4. On the Protocol tab, select Yes for Enabled.
   
5. On the IP Addresses tab, select Yes for both Active and Enabled for all IP Address entries.
   
6. Click OK.
7. Click OK.
8. Select the SQL Server 2005 Services folder
9. Right-click SQL Server (MSSQLSERVER), and click Restart.
   
10. Exit SQL Server Configuration Manager.

SQL Server 2005 Surface Area Configuration

1. Click Start, click All Programs, click Microsoft SQL Server 2005, click Configuration Tools, and click SQL Server Surface Area.
   
2. Click Surface Area Configuration for Services and Connections.
   
3. Expand the MSSQLSERVER folder, expand the Database Engine folder, and click on Remote Connections.
   
4. Select the radio button next to "Local and remote connections".
5. Select the radio button next to "Using TCP/IP only".
6. Click OK.
7. Exit SQL Server 2005 Surface Area Configuration.
8. Restart SQL Server using the SQL Server Configuration Manager, as shown above.
9. Exit SQL Server Configuration Manager.

SQL Server Management Studio Express

1. Click Start, click All Programs, click Microsoft SQL Server 2005, and click SQL Server Management Studio Express.
   
2. Select SQL Server Authentication, enter sa for the login, and type the password you specified when you installed SQL Server 2005 Express, above.
   
3. Click Connect.
4. Right-click on the Databases folder, and select New Database.
   
5. Enter a Database name.
   (we'll use 'umbracoCMS' in these instructions)
6. Leave the Owner as <default>.
   
7. Click OK.
8. Expand the Security folder.
9. Right-click on the Logins folder, and select New Login.
   
10. Select the General page:
11. 1. Enter a Login name.
       (we'll use 'umbracoUser' in these instructions)
    2. Select the radio button next to "SQL Server authentication", and enter and confirm a Password.
    3. Remove the check mark in the box next to "Enforce password policy".
       (note: this is optional and should not be done in a production environment)
       
12. Select the User Mapping page:
13. 1. Place a check mark in the box next to the Database you created.
       ('umbracoCMS' in these instructions)
    2. Place a check mark in the boxes next to the following Database role memberships:
           db_datareader
           db_datawriter
           db_owner
           public
       
14. Click OK.
15. Right-click on the root server folder (the parent of Databases, Security, etc.), and select Properties.
    
16. Select the radio button next to "SQL Server and Windows Authentication mode".
    
17. Click OK.
18. Right-click on the root server folder (the parent of Databases, Security, etc.), and select Restart.
    
19. Click Yes.

Test the configuration by disconnecting and logging in as the user you just created.

1. Click File, click Disconnect Object Explorer.
2. Click File, click Connect Object Explorer.
3. Select SQL Server Authentication, enter the name of the login user you create ('umbracoUser' in these instructions), and type the password you specified when you created the login user, above..
   
4. Click Connect.

If you cannot log in with SQL Server authentication…

• Check the log files after a failed attempt.
• The default log location is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG and can be opened with Notepad.
• Look for an entry in the ERRORLOG such as:
  2007-05-16 16:43:42.75 Logon Error: 18456, Severity: 14, State: 8.
• "State: 8" indicates a problem with the user password.
• You can find a list of the meaning of each state message at
  http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx