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 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:
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.