Sql server Technical Article
|
|  Yüklə 0.51 Mb.
|
Secure Installation and ConfigurationThe first requirement of a secure SQL Server installation is a secure environment. Nothing much has changed in the external security requirements of a server running SQL Server 2005. You need to physically secure the server and back up data regularly, put it behind one or more firewalls if it's connected to a network, avoid installing SQL Server on a computer with other server applications, and enable only the minimum network protocols required. Install SQL Server on a Microsoft Windows Server® 2003 computer so that it has full advantage of operating system-level security protections. Further, the most secure installation will be on one or more NTFS partitions. Once the environment is secure, it is critical to install SQL Server 2005 securely. The installation program does all the usual installation tasks, and has a nice new System Configuration Checker that notifies you of any deficiencies that might cause problems. Installing SQL Server 2005 does not enable all features by default. Instead, it installs the core essentials and widely used features. Other features that might not be needed in a production environment are turned off by default. You can use the supported tools to turn on just the features you need. This is all part of Trustworthy Computing's secure by default mandate. It means that SQL Server 2005 is secure out of the box when you install it, with default settings set safely. Features that are not required by a basic database server are left uninstalled, aiming for a reduced surface area. Since by default not all features are enabled across all systems, a heterogeneity is introduced in terms of the install image of a system. Because this limits the number of systems that have features that are vulnerable to a potential attack, it helps defend against large-scale attacks or worms. Table 1 lists the various SQL Server services, the Microsoft Windows® service startup type, and the default state upon installation. For certain services, you can elect during installation to auto-start the service when Windows restarts, which affects the initial Windows service startup state.
One decision you have to make when installing SQL Server is which Windows account each service will run under. You can have all services run under a single Windows account, but that is rarely the best option under the principle of least privilege since each service needs access to different resources. You could opt to have one or more services run under the Local System, Network Service, or Local Service accounts. These accounts are convenient because you don't have to take the time to grant the correct privileges and permissions for the service to run. But this potentially opens a huge security hole. For example, the Local System account has complete access to a server so if your SQL Server system is compromised by an attacker, there is no limit to the potential damage that can occur. Table 2 Minimum permissions required for user accounts for SQL Server services
There may be additional permissions required in Windows for each of these services, depending on any additional functionality you require and on the operating system platform. For example, the SQL Server service is likely to require network write privileges to send e-mail using xp_sendmail. The xp_cmdshell extended stored procedure, a dangerous procedure to enable, requires the act as part of operating system on operating systems prior to Windows Server 2003. You should, of course, only grant these privileges if that feature is required on your server. Services that use network resources may require a domain user account. Features such as remote procedure calls, replication, backing up to network drives, heterogeneous joins across machine boundaries, and some mail features commonly require network access. You can use SQL Server Management Studio to change user accounts for services as well as to update an account's password.
Configuration toolsSQL Server has long had a rich user interface for administering the server and its databases. SQL Server 2005 continues that tradition with new tools you can use to keep the server secure. One tool you'll use often is the SQL Server Configuration Manager, which replaces Client Network Utility, Network Utility, and Service Manager from SQL Server 2000. Its various sections, shown in Figure 1, let you start and stop services, enable and disable network protocols, and define aliases. (An alias is an alternate name that can be used to make a connection to the server. It encapsulates the elements of a connection string and exposes them with a user-defined name.) 
Figure 1 SQL Server Configuration Manager SQLCMD is the latest incarnation of the isql and osql command-line tools of previous versions of SQL Server. Probably the most interesting feature from an administrator's point of view is the SQLCMD Dedicated Administrator Connection. This connection lets an administrator access an instance of SQL Server even when the instance is not otherwise responding to standard connections. There can be only one such connection to any instance at one time and you must be a member of the sysadmins group to make the connection.
Because the purpose of the Dedicated Administrator Connection is troubleshooting, there are a number of restrictions on the connection. For example, it supports only TCP/IP, the login's default database is available, and it requires CONTROL_SERVER permission. But SQL Server now has a secure access of last resort that administrators can use to solve database problems when all else fails. For more information, see Using a Dedicated Administrator Connection in SQL Server Books Online. SQL Server Surface Area Configuration ToolSQL Server 2005 comes packed with numerous features, many of which are installed in a disabled state. For example, CLR integration, database mirroring, debugging, Service Broker, and mail functions are installed but are not running and not available until you explicitly turn them on or configure them. This design is consistent with the reduction in surface area paradigm of the secure by default philosophy of SQL server, and leads to a reduced attack surface. If a feature is not available or enabled, an attacker cannot make use of it. The tradeoff is that it can be time consuming to hunt down all of the Transact-SQL statements for turning on features. Even when you discover that the sp_configure system stored procedure does much of what you need, you still have to write non-intuitive code like this: sp_configure 'show advanced options', 1 reconfigure with override sp_configure 'clr enabled', 1 There are far too many configuration options to take the time to write this kind of code. So SQL Server includes the SQL Server Surface Area Configuration Tool, which provides a handy GUI for configuring the server. Running it should be the first thing you do after installing SQL Server. To start it, open the Windows Start menu and select All Programs, and then Microsoft SQL Server 2005. Select Configuration Tools, and then SQL Server Surface Area Configuration. The tool opens with a brief explanation of the purpose of the tool, a link to its documentation, and one link to configure services and protocols and another to configure other features. The configuration tool for services and protocols, shown in Figure 2, displays all of the installed services for all local instances of SQL Server. This is a convenient alternative to using Computer Browser to start, stop, and modify SQL Server services. Some options let you control other server settings, such as Remote Connections, which lets you turn on and turn off remote connections to the server as well as specify whether to use TCP/IP or named pipes or both for communications.
Figure 2 Services and protocols portion of the SQL Server Surface Area Configuration Tool The configuration tool for features, shown in Figure 3, puts in one place all of the settings to enable various optional features for the server. Some features, such as Native Web Services and Service Broker, display a list of all existing endpoints to the server so that you can selectively turn them on and off. While Microsoft has worked hard to make these features as secure as possible, you should leave any that you don't use disabled. Enabling unused features can open potential attack paths to your server. For example, if you aren't going to write any .NET Common Language Runtime (CLR) extended stored procedures, don't enable it.
Figure 3 Features portion of the sql Server Surface Area Configuration Tool This part of the configuration tool provides a list of features for the database engine, Reporting Services, and Analysis Services, as long as the service is currently running. Following are the features available for each service in the SQL Server Surface Area Configuration Tool. SQL Server Database Engine Features
Analysis Services Features
Reporting Services Features
Note There are interdependencies between services and features, so turning one on may cause others to start or become enabled. |
