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

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 BUILTINAdministrators
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
Server90SharedSqlSAC.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

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles