Hands on: SQL Server Express on the Lan

Installation is pretty straightforward and it’s OK to accept the defaults most of the time, but it is important to take note of the screen asking for an Instance Name.

An instance is effectively a self-contained SQL Server environment, enabling different applications to have their own dedicated implementation. When installed manually the default is to create an instance called SQLExpress, which is fine except that when you want to connect to the server over the network you won’t be able to do so simply using the host name.

Instead you’ll need to specify the name of the host server, followed by the instance you want to use in the format “hostname\instance”, such as myserver\SQLExpress, for example.

If you know this in advance it’s not a problem, but it can trip up those who don’t. So either make a note of the instance name you’ve chosen or use the default instead.

If you’re still having problems connecting you might also want to try specifying the host server IP address rather than its name, as name resolution issues can occur. You also need to make sure the appropriate Lan protocols are enabled, which I’ll explain more about shortly.

In the meantime the Authentication Mode settings can also cause problems for the unwary.

There are two options, the default being Windows Authentication Mode, which is where SQL Server leaves it up to the Windows host to authenticate users wanting database access. The other is Mixed Mode, where SQL Server can also authenticate users itself, using locally stored user names and passwords.

The former is the more secure option, but for use on small networks I would recommend Mixed Mode, especially on a workgroup Lan without a domain controller. You may also need to configure Mixed Mode in order to support web and other applications which often assume this approach, and applications running on non-Windows platforms.

I usually set Mixed Mode during setup but if you do need to change it later don’t worry as you don’t have to re-install; just use the Regedit utility to get to the following Registry setting: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer. Once there change the value of the key called LoginMode from 1 (Windows Authentication Mode) to 2 (Mixed Mode).