Configure SQL Server 2005 Express for umbraco

Install and setup
A step-by-step guide to install and configure everything you need to run umbraco 3.0 on Windows Vista. (including IIS7, SQL Server 2005 Express, SQL Server Management Studio Express, and umbraco 3.0)

Chapters

• Chapter 1: Introduction
• Chapter 2: Install IIS
• Chapter 3: Install ASP.NET AJAX 1.0
• Chapter 4: Install SQL Server 2005 Express Edition SP2
• Chapter 5: Install SQL Server Management Studio Express
• Chapter 6: Configure SQL Server 2005 Express for umbraco
• Chapter 7: Install umbraco 3.0 Files
• Chapter 8: Configure IIS for umbraco
• Chapter 9: Configure umbraco 3.0
• Chapter 10: Congratulations!

Configure SQL Server 2005 Express for umbraco

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.

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. Click Continue if prompted for permission to continue.
3. Expand the SQL Server 2005 Network Configuration folder, click on Protocols for MSSQLSEVER.
4. Right-click on the TCP/IP protocol, and select Properties.
   
5. On the Protocol tab, select Yes for Enabled.
   
6. On the IP Addresses tab, select Yes for both Active and Enabled for all IP Address entries.
   
7. Be sure that TCP Port 1433 is set for all the IP addresses, including the IPALL entry when you scroll to the bottom of the window. 
8. Click OK.
9. Click OK.
10. Select the SQL Server 2005 Services folder
11. Right-click SQL Server (MSSQLSERVER), and click Restart.
     
12. When the service has restarted, 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 Continue if prompted for permission to continue.
3. Click Surface Area Configuration for Services and Connections.
   
4. Expand the MSSQLSERVER folder, expand the Database Engine folder, and click on Remote Connections.
5. Select the radio button next to “Local and remote connections”.
6. Select the radio button next to “Using TCP/IP only”.
   
7. Click OK.
8. Exit SQL Server 2005 Surface Area Configuration.
9. Restart SQL Server using the SQL Server Configuration Manager, as shown above.

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 .
   
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
       
    3. Click OK.
14. Right-click on the root server folder (the parent of Databases, Security, etc.), and select Properties.
    
15. Select the Security page:
16. 1. Select the radio button next to “SQL Server and Windows Authentication mode”.
       
    2. Click OK.
17. Restart SQL Server using the SQL Server Configuration Manager, as shown above.

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