Installing MSSQL 2005 Express

For an overview of the issues to do with installing and updating MSSQL in general, see Installing MSSQL Express Versions. This post covers downloading and installing MSSSL 2005 Express.

The MSSQL 2005 release history is:

• MSSQL 2005
• MSSQL 2005 SP1
• MSSQL 2005 SP2
• MSSQL 2005 SP3
• MSSQL 2005 SP4

It is currently at Service Pack 4 (SP4), downloadable here.

The top file is the core server only, the second file is server + client tools + BI/reporting, and the third is server + client tools.
MSSQL 2005 does not have a native 64 bit installation; but it can be run on 64 bit (it uses the Windows-on-Windows-64 emulation subsystem). The fourth file can be used for native 32-bit-only installs (it leaves out 64 bit compatibility and won't run on 64 bit systems at all).

We will follow the installation of the client tools and server version.

(1) Installing Setup files and welcome screen

 (2) System Configuration Check and Registration Information

(3) Selection of Features

Unless you are doing development or specifically using replication, you can leave out these options as shown.

(4) Windows Authentication Mode is regarded as more secure, but it requires a domain controller and proper network administration.  For legacy Applications which use SQL Authentication (username and password) or for small sites which don't have a dedicated server, Mixed Mode is appropriate.  The password being entered is automatically for the 'sa' (System Administrator) SQL user - you don't have to input the 'sa' username anywhere.

(5) Error Reporting - tick these if you feel inclined.

(6) Installation and Finished Screens

SQL Server Express 2005 is now installed, but by default it can't be accessed from anywhere but the local computer. We need to use the Surface Area Configuration Tool.

(1) Start the tool

(2) Choose 'Surface Area Configuration for Services and Connections'

(3) Make the shown changes and click 'Apply'

(4) You will then need to go to 'Database Engine' > 'Service', and click Restart, then go to 'SQL Server Browser' > 'Service' and click Restart.
This should be all that is needed, but experience shows that it is sometimes necessary to restart the machine, so to save time it's probably a good idea to do that rather than bother restarting the services as just mentioned.

(5) Any other problems connecting from remote machines can be attributed to firewalls.  Check for firewalls on both the client and server (the Server is critical), and if need be the port to open is port 1433.
Note that the slammer worm uses this port, so what we want to do is enable access to the server machine via the port from the local network only; NOT from the internet.