SQL Express 2008 on Server Core 2008 R2

So I installed Server Core on a site which I felt at the time would not need a full blown GUI as their only requirement was some security and file sharing. Well as usual the goalposts move and they now want to run an app that requires SQL backend. Suppiler who I have dealt with before tends to use SQL Express where possible. So rather the reinstall the server from scratch I investigate can I get it on Server Core. Answer yes I did but It is not supported by Microsoft so caution is advised if its on a production server.

Prerequisites

The following features need to be enabled

• NetFx2-ServerCore
• NetFx3-ServerCore
• ServerCore-WOW64
• NetFx2-ServerCore-WOW64
• NetFx3-Server-Core-WOW64
• MicrosoftWindowsPowerShell

Use dism /online /enable-feature /featurename:<feature1> /featurename:<feature2> etc  to install all of the above.

Installation

Download SqlSeverExpress-2008-x64 and save to temporary location. Use 7-Zip Portable to extract files to a folder of your choice.
Run setup with the following parameters (on seperate lines for clarity but of course will be on a single line separeted by spaces)
/qs  
/ACTION=Install
/FEATURES=SQL
/INSTANCENAME=SQLEXPRESS
/SQLSVCACCOUNT="NT AUTHORITY\SYSTEM" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS"
/AGTSVCACCOUNT="NT AUTHORITY\Network Service"
/IAcceptSQLServerLicenseTerms
This will take 5 to 10 minutes to load. If any errors are encountered have a look in the summary.txt file located in %ProgramFiles% \Microsoft SQL Server\100\Setup Bootstrap\Log folder.

Enable Remote Access

To enable remote connections, use SQLCMD.exe locally and execute the following statements against the Server Core instance

sqlcmd -S <SERVERNAME>\SQLEXPRESS -E
1>EXEC sys.sp_configure N'remote access', N'1'
2>GO
1>RECONFIGURE WITH OVERRIDE
2>GO
1>exit
sqlcmd is located %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn folder.

Firewall Configuration

Open a netsh propt in the folloing context

netsh advfirewall firewall

>set rule group="remote administration" new enable=yes

>add rule name=SQLTCPPort dir=in protocol=tcp action=allow localport=1433 remoteip=localsubnet 

>add rule name=SQLNPPort dir=in protocol=udp action=allow localport=1434 remoteip=localsubnet

Enable TCP/IP

By default TCP/IP is not enabled. You can use TCPENABLED=1 in the setup to enable it but it defaults to using Dynamic Ports. I prefer to assign ports for easier firewall configuration. Open regedit and Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQLServer\SuperSocketNetlib\tcp
set Enabled (DWORD) to 1

Enable TCP on all interfaces

open IPALL SubKey
clear TcpDynamicPorts (string)
set TcpPort (string)to 1433 or port of your choice

Enable TCP on Selected Interfaces

SubKeys IP1 to IP9 will contain settings for each interface. change the following in each subkey that you wish to have enabled
set Enabled (DWORD) to 1
clear TcpDynamicPorts (string)
set TcpPort (string)to 1433 or port of your choice

Enable Named Pipes

This is not enabled by default. Open Regedit and navigate to
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQLServer\SuperSocketNetlib\np
set Enabled (DWORD) to 1

Finally

Restart the SQL Server
sc stop MSSQL$SQLEXPRESS
sc start MSSQL$SQLEXPRESS
If you still have remote connection problems your client or application may not be getting the correct port/instance name. Enable and start the SQLBrowser service
sc config SQLBrowser start= auto (space between = and auto)
sc start SQLBrowser