SQL Server Express Installation and Connectivity Configuration

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)

The complete list of options and values to set for each option is available here:
http://msdn2.microsoft.com/en-us/library/ms144259.aspx

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:

"Provider=SQLNCLI;Server=<MachineName>\SQLEXPRESS;
Initial Catalog=master;Integrated Security=SSPI"



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.

Next is to follow the steps as outlined in this article:
http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

Resources

Here are a few more good resources for SQL Server Express:

SQL Server Express Home: http://www.microsoft.com/sql/editions/express/default.mspx
SQL Server Express Download: http://msdn.microsoft.com/vstudio/express/sql/download/
Redistribution Rights Registration: http://www.microsoft.com/sql/editions/express/redistregister.mspx
SQL Server Express WebLog: http://blogs.msdn.com/sqlexpress/default.aspx
Configuring During Installation: http://blogs.msdn.com/sqlexpress/archive/2006/09/20/configuring-sql-express-during-installation.aspx
Troubleshooting SQL Server 2005 Connectivity Issues (Part 1): http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
Troubleshooting SQL Server 2005 Connectivity Issues (Part 2):
http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx
Troubleshooting SQL Server 2005 Connectivity Issues (Part 3):
http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx

Labels: ,