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),
runningEXEC 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.
