I have an old EX470 MediaSmart machine that I use for various purposes.  I use it for backing up my existing machines, I use it as a media center extender with my XBOX, a Subversion source code server, and today I setup SQL Server 2008 Express on this machine.

*Disclaimer* – I’m sure this is not approved or condoned by Microsoft for Windows Home Server owners, so do this at your own risk.  If you brick/break/fubar your machine, please do not email me about it.

Here are the steps I followed:

Step 1:

Remote desktop into your Windows Home Server machine.  “Start > Run > mstsc”.   From here enter your home server machine name (i.e. HOME-SERVER).  Once prompted, enter your username as “Administrator” and enter the root administrator password you selected when you setup your machine.

Step 2:

Download Microsoft Windows Installer 4.5 (if you have not previously done this) as it is required to install SQL Express 2008.   Here is the download link (http://www.microsoft.com/downloads/details.aspx?FamilyID=5a58b56f-60b6-4412-95b9-54d056d6f9f4&displaylang=en).  I believe a reboot is required here.

Step 3:

Download SQLEXPR_x86_ENU.exe from this Microsoft download link (http://www.microsoft.com/downloads/details.aspx?FamilyID=58ce885d-508b-45c8-9fd3-118edd8e6fff&displaylang=en).

Step 4:

Run the setup program, and under the “Installation” tab, select “New SQL Server stand-alone installation”.  Follow the steps required for the installation process.  I would  reboot after installing this as well.

Step 5:

Click Start > Programs > Microsoft SQL 2008 > Configuration Tools > Configuration Manager

Step 6:

Follow steps 1 – 9 here in order to enable remote connections as this will not be enabled by default.   Steps 10 and later will not work because SQL Express Management Studio cannot be installed on a Windows Server 2003 installation (to my knowledge).

Step 7:

On your home server, under control panel, select “Windows Firewall”.   Select the exceptions tab, and add a program exception for: c:\program files\microsoft sql server\mssql10.sqlexpress\mssql\binn\sqlservr.exe

Step 8:

On your desktop machine now, enter the following command into your command prompt:

C:\>SQLCMD -U sa -P <the-sa-password-you-set> -S YourHomeServerName\SQLEXPRESS,1122

(note: 1122 is the port # of the server.   This is the default, but you can see this changed from the TCP/IP protocol settings inside SQL Server configuration manager).

If this worked, you should be given a prompt such as “1>” to enter sql commands on that server.  Just enter “QUIT” once you have confirmed it worked.

Step #9:

Install Microsoft SQL Express 2008 Management Studio (http://www.microsoft.com/downloads/details.aspx?familyid=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b&displaylang=en).   When prompted for connection details, specify “YOUR-HOME-SERVER\SQLEXPRESS,1122″ (without quotes), pick SQL Server Authentication, and enter your “sa” credentials.   Note the 1122 in the location.  This not a well documented feature, but this is how you specify the port # for a connection.