Since SQL Server 2005 Express is the successor to MSDE there is a lot of interest from developers. There are a few questions that keep being asked about the installation and configuration process:
How to automate the installation of SQL Server Express? How to allow remote connections? How to set up a custom named instance for installation? How to configure SQL security? How to troubleshoot connection problems?
Here is a brief summary addressing those questions.
Installation Options
SQL Server Express does support most of the same options used with MSDE to install from the command line and using INI files. Here are the most frequently used:
INSTANCENAME – allow to specify a custom instance name DISABLENETWORKPROTOCOLS – allows to enable the TCP/IP protocol for remote access (0 - All protocols are enabled; 2 - Shared Memory and TCP/IP are enabled) SECURITYMODE – allows to switch to SQL security mode (when SECURITYMODE=SQL is used to switch to SQL Security a strong password needs to be provides with the SAPWD option)
If SQL Server Express will be distributed via the bootstrapper all those parameters can be added to the product manifest (in the <Command> tag). Here is a detailed description of using the bootstrapper: http://msdn.microsoft.com/msdnmag/issues/04/10/bootstrapper/
Remote Connections and Troubleshooting Connectivity
To troubleshoot connectivity issues it is important to understand that SQL Server Express installs by default with remote access disabled. This closes some security holes in prior versions. By default only the Shared Memory protocol is enabled. That allows making local connections. However, TCP/IP or Named Pipes connections (local or remote) are disabled. The TCP/IP and Named Pipes protocols can be enabled via the DISABLENETWORKPROTOCOLS option on install, or later following the steps in this KB article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
Troubleshooting connectivity normally starts with looking at the connection string. Assuming the default installation options are used (SQL Server Express installs as a named instance SQLEXPRESS by default) the connections string should be something like this:
In the above <MachineName> could be ".", "(local)","localhost", or "<localhostname>".
When connecting to named instances it is required to have the SQL Browser service running or to specify the TCP/IP port number in the connection string.