SQL Server 2005 Express Edition - Part 4 - Surface Area Configration

August 10, 2007

In the previous article of our series dedicated to SQL Server 2005 Express Edition, we have provided a comprehensive listing of its currently available releases (along with locations from which each of them can be downloaded), distinguished by a number of factors, such as supported platform, presence of advanced features, or inclusion of Service Pack 2. We also described in a detailed manner the basic setup process (without Advanced Services or Management Studio Express).

By following our description, you can easily determine that the sequence of installation steps is for most part the same for both the RTM and SP2- (or SP1-) integrated versions of the core database engine. There are however some exceptions, resulting mainly from the fact that Service Pack 2 addresses changes introduced in Windows Vista, such as, for example, a new security model, in which members of the local, built-in Windows Administrators group are no longer automatically granted the sysadmin fixed server role. This approach mirrors the User Access Control mechanism, which generates a privileged and a non-privileged access token for members of the local Administrators group at the logon time, using the latter for all subsequent operations unless an action requiring the former is invoked and explicitly acknowledged (effectively, all user initiated actions run by default in the non-administrative security context).

Launching setup of the RTM version of SQL Server 2005 Express Edition on a Vista system will trigger the Program Compatibility Assistant informing you about the need for subsequent SP2 install. You will also notice that "Add user to the SQL Server Administrator role" option (which appears in the SP2 version) is missing from the Configuration page of the wizard. When running SP2-integrated install (or applying SP2 update to an existing instance), selecting this option will automatically create a SQL login corresponding to the Windows account of the user who initiated the setup and assign to it SysAdmin fixed server role (as well as apply equivalent actions to the BUILTIN\Administrators group).

If, for some reason, you have not selected the aforementioned option, you can obtain the same result after the installation by executing the SQL Server 2005 Surface Area Configuration Tool (%ProgramFiles%\Microsoft SQL Server\90\Shared\SqlSAC.EXE), via a shortcut in the Configuration Tools submenu of Microsoft SQL Server 2005 menu (or a link on the final page of the setup wizard). Once the tool's interface appears, click on the Add New Administrator link, triggering the display of SQL Server 2005 User Provisioning Tool for Vista. Verify that the "User to provision" text box contains correct credentials, select the appropriate entry from the "Available privileges" list on the left side of the window, and click the command button with ">" sign. Using the "OK" command button in the lower right corner confirms your action and closes the window. Keep in mind that this procedure allows you to grant privileges only to the account in which security context the User Provisioning tool is running. In order to manage other logins (as well as to simplify a variety of other administrative tasks), install SQL Server Management Studio Express (refer to our previous article regarding its availability).

It is likely that you will need to employ the Surface Area Configuration tool following the installation in order to modify some of the more restrictive settings that have been applied during initial setup. While they provide much needed out-of-the-box security, their restrictions affect a wide range of activities, including those that you potentially might rely on for your standard operational tasks (for example, SQL Server 2005 Express Edition - just like its SQL Server 2005 Developer or Evaluation counterparts - by default does not accept remote connections). In addition to the earlier described "Add New Administrator" link, the Surface Area Configuration Tool window contains two other items, which can be used to manage both local and remote hosts:

• Surface Area Configuration for Services and Connections - handles services and connection types for all SQL Server 2005 instances on a target system.
• Surface Area Configuration for Features - enables and disables individual features for all SQL Server 2005 instance on a target system.

Surface Area Configuration for Services and Connections allows you to organize its views either by instance or component. Both of them contain a node hierarchy representing individual Database Engines for each instance of SQL Server 2005 (note, however, that user instances, unique to SQL Server 2005 Express Edition, are not accessible from this interface, hence you need to resort to alternative methods for their administration - which we will describe when discussing this feature in our future articles) and a single SQL Server Browser shared among all of them. Within the nodes, you can configure the startup type of their respective services. (Database Engine, which is essential for the majority of data management operations, is configured with Automatic startup but SQL Server Browser, which, as we explained in our previous article, facilitates connectivity from remote clients by providing necessary protocol details, is by default disabled in the case of Express Edition). In addition, for each Database Engine instance, you have an option to decide whether to allow both local and remote connections. As we mentioned earlier, by default only the former are permitted for SQL Server 2005 Express, Evaluation, and Developer editions. Remote connectivity is possible via named pipes or TCP/IP protocol (possibly both), although the latter is recommended, since it requires fewer ports opened for inbound traffic (this is also the option automatically enabled on out-of-the-box installations of SQL Server 2005 Workgroup, Standard, and Enterprise editions). Keep in mind that changes to these settings require restarting the database engine (which can be accomplished directly from the Surface Area Configuration interface by using the Stop and Start command buttons visible whenever the Service entry under the Database Engine subnode for a target SQL Server instance is selected).

Similar to Surface Area Configuration for Services and Connections, the Features interface also provides the ability to view its settings based on instance or component categories. Once you pick your preference, you will be able to manage:

• Ad Hoc Remote Queries - governing the ability to initiate remote connections that utilize OPENROWSET and OPENDATASOURCE functions (rather than relying on linked or remote server functionality, employed in scenarios where remote queries to the same target server are performed on a regular basis). Enabling this feature is equivalent to running the following sequence of T-SQL statements:

  	sp_configure 'show advanced options', 1
  	RECONFIGURE
  	GO
  	sp_configure 'Ad Hoc Distributed Queries', 1
  	RECONFIGURE
  	GO
  

• CLR Integration - leveraging .NET Framework Common Language Runtime by allowing you to create a variety of database objects (such as stored procedures, triggers, or user-defined types and functions) using any of .NET programming languages. Another way to implement this functionality is to execute the sp_configure stored procedure with 'clr enabled' server configuration option:

  	sp_configure 'clr enabled', 1
  	RECONFIGURE
  	GO
  
  

• Native XML Web Services - providing direct data access via Simple Object Access Protocol (SOAP) over HTTP, without the need for installing the Internet Information Server component on the target computer, by taking advantage of the HTTP listener (implemented as a kernel module HTTP.SYS, which is currently available only on Windows XP SP2, Windows 2003 Server, and Windows Vista platforms). Unfortunately, SQL Server 2005 HTTP SOAP functionality relies on HTTP Endpoints (for more information on this subject, you can refer to Using Native XML Web Services in SQL Server 2005 section of SQL Server 2005 Books Online), which, are not available in its Express and Compact editions (you will have to resort to Workgroup, Standard or Enterprise products in order to take advantage of this feature).

• OLE Automation - allowing for a number of system stored procedures that facilitate management of OLE Automation objects (sp_OACreate, sp_OADestroy, sp_OAGetProperty, sp_OASet_Property, sp_OAMethod, sp_OAStop, and sp_OAGetErrorInfo, for their creation and destruction, retrieving and setting their property values, invoking their methods, stopping the server-wide OLE execution environment, and getting related error information, respectively) in stored procedures, batches of T-SQL statements, and triggers. This effectively gives you the ability (via custom T-SQL code) to take advantage of functionality exposed by COM interfaces (such as, for example, FileSystem Object for file and folder manipulation). To turn this feature on, simply mark the checkbox "Enable OLE Automation" or execute the following:

  	sp_configure 'show advanced options', 1
  	RECONFIGURE
  	GO
  	sp_configure 'Ole Automation Procedures', 1
  	RECONFIGURE
  	GO
  

• Service Broker - enabling queue-based, asynchronous, reliable messaging infrastructure with transactional support. Both a source and a target of such communication are identified by endpoints, corresponding to unique TCP/IP ports between which Service Broker messages are exchanged. To create an endpoint, use CREATE ENDPOINT (with FOR SERVICE_BROKER clause) T-SQL statement (for more information on this subject, refer to SQL Server 2005 Books Online). Once this step is successfully completed, you will be able to manage the state of such endpoint (by configuring it as either Stopped or Started) within the Service Broker section of Surface Area Configuration for Features interface. Note that the Service Broker functionality in Express Edition is artificially restricted only to message exchanges that involve an instance of one of the full-fledged SQL Server 2005 editions.

• xp_cmdshell - determines whether it is possible to launch an execution of arbitrary command shell executables (this applies typically to executables that return results in the text format) from within the Database Engine by using xp_cmdshell extended stored procedure. For example, in the enabled state (which is done simply by marking the "Enable xp_cmdshell" checkbox in the Surface Area Configuration interface), running

  	EXEC xp_cmdshell 'dir c:\'

  would generate a listing of files and folders located on the C: drive. This functionality can also be configured by running the following:

  	sp_configure 'show advanced options', 1
  	RECONFIGURE
  	GO
  	sp_configure 'xp_cmdshell', 1
  	RECONFIGURE
  	GO
  

In our next article of this series, we will review other post-installation configuration tasks and take a look at other administrative utilities available in SQL Server 2005 Express Edition.

» See All Articles by Columnist Marcin Policht