Security in SQL Server 2005
Posted: May 24, 2004
In January 2002, Microsoft introduced the Trustworthy Computing initiative to improve security, privacy, reliability, and business integrity. As part of this initiative, Microsoft follows development processes that help to ensure that products are secure by design, secure by default, and secure in deployment. The SQL Server development team has incorporated these processes into the development of SQL Server 2005, the next version of SQL Server.
Trustworthy Computing and SQL Server 2005
The Trustworthy Computing initiative outlines a framework that defines the steps necessary to help support secure computing as well as the measures that help you deploy and maintain a secure environment. These steps help to protect the confidentiality, integrity, and availability of data and systems at every phase of the software lifecycle—from design, to delivery, to maintenance. Microsoft’s goals in the Trustworthy Computing initiative are to:
Reduce potential security issues through product design and testing. Reduce potential surface area of attack by disabling functions that may not be necessary. Also to ensure that on installation, the product chooses the right set of configuration values for all the options, such that by default when a system is installed it is in a secure state. Provide tools and guidance that support ongoing protection, detection, defense, recovery, and maintenance. Create documentation and regularly communicate the latest security information to customers to help maintain the security and integrity of the SQL Server environment.
For more information about these goals, visit the Security Vision and Framework page on the Microsoft Trustworthy Computing site. To uphold the four tenets of the Trustworthy Computing initiative, Microsoft and the SQL Server team have taken the following steps:
Secure by design. The SQL Server development team has conducted multiple security audits and spent more than two months studying SQL Server components and the interaction between them. For each potential security threat, the team did a threat analysis to evaluate the issue and completed additional design and testing work to neutralize potential security issues. As a result of these design efforts, SQL Server 2005 will include many new server security features. Secure by default. Upon installation, SQL Server 2005 will choose the right set of configuration values for all setup options, ensuring that when a new system is installed, it will be in a secure state by default. Secure in deployment. Microsoft has created content to help organizations deploy SQL Server using the proper security credentials and to fully understand the steps and permissions required. The SQL Server deployment tools provide the
information necessary to understand the decisions you need to make during deployment. Additionally, security updates are easy to find and install—and if you choose the option, the updates install automatically. Tools are also available to help you assess and manage security risks across organizations. Communications. For ongoing support of a SQL Server deployment, Microsoft provides communications related to security issues. The Security Resources page serves as a central location for all security information related to SQL Server, including existing security threats as well as the patches and tools required to mitigate those threats.
SQL Server 2005 will incorporate security improvements and features that meet the goals of the Trustworthy Computing initiative. In general, these features and improvements fall into the following areas:
Restricting user access to the server. SQL Server 2005 will provide greater control of access to SQL Server and will enable administrators to control access to SQL Server through policies. Disabling services and restricting service configuration. Administrators will be able to restrict access to resources within SQL Server, at the administrator’s designated scope and with a fine degree of granularity. Administrators will also have a manageable system that does not violate the princ iple of least privileges. By having certain services disabled by default for new server installations, administrators will be more actively involved in deciding which specific additional services they want to enable. Reducing the surface area of attack for new features. Starting with the installation and setup of SQL Server, the surface area of attack will be minimized. Throughout the development cycle of the product, new features have been reviewed and tested for security to help reduce the surface area of attack.
Security Features at a Glance
Posted: May 24, 2004
The table below provides an overview of new and enhanced security features that will be available with SQL Server 2005. Feature
Off by default
Description
SQL Server 2005 will enable only a limited numbe r of core features and services by default, thereby limiting the exposed surface area of the server and allowing administrators to enable only those services and features that are necessary in their environment. Services and components that will be disabled by default in SQL Server 2005 include: the Microsoft .NET Framework, SQL Service Broker Network C onnectivity, and HTTP connectivity in Analysis Services. Other services such as SQL Server Agent, full-text search, and the new Data Transformation Service s (DTS) service will be set to a manual startup and require explicit action to be set to start automatically.
Granular permission control
A new security model in SQL Server 2005 will allow administrators to manage permissions at a granular level and at a designated scope, making management of permissions easier as well as ensuring that the principle of least privileges is upheld.
Separation of users and schema
SQL Server 2005 will simplify security administration by separating the implicit link between users and the database objects that they own. For example, in earlier versions of SQL Server, if you wanted to remove a user, you had to first drop or reassign ownership of all database objects that the user owned, which sig nificantly complicated the process and potentially impacted a large number of applications. With the new model, dropping users will not require an application change. Administrators will be able to specify Microsoft Windows ®-style policies on standard logins so that a consistent policy is applied across all accounts in the domain. SQL Server 2005 will allow you to specify a context under which statements in a module execute. This feature also acts as an excellent mechanism for granular permission management. With SQL Server 2005 you will be able to specify triggers on DDL operations, providing a supplemental mechanism for auditing DDL actions. SQL Server 2005 will support encryption capabilities within the database itself, fully integrated with a key management infrastructure. SQL Server 2005 clustering will support Kerberos authentication against a SQL Server 2005 virtual server. SQL Server Agent will support multiple proxy accounts, one per job subsystem. SQL Server Agent will no longer require access to the LSA to use proxy accounts. Therefore, SQL Server Agent will no longer require the service to run as a local administrator for it to be enabled. A new permission will be available in SQL Server 2005 that allows users who do not have system administrator rights to run SQL Profiler. By default, client/server communications are encrypted. To centralize security assurance, server policy can be define d to reject unencrypted communications. More administrative permissions will be available in SQL Server 2005. In addition to online analytical processing (OLAP) administrators, database administrators will be able to possess administrative permissions within the context of an individual database. New permissions on objects will enable users to see the object definition (without being able to access the object itself) and to process an object. SQL Server Agent has been enhanced to support assigning rights over jobs in a granular fashion. A set of new deployment tools and documentation will help ensure that SQL Server 2005 can be securely deployed into an e xisting SQL Server topology or a new installation. These tools provide a step by-step approach by giving detailed information, analyzing the existing topology, checking for prerequisites, recommending a configuration setting, and validating each step. SQL Server 2005 Analysis Services will include new auditing capabilities integrated with SQL Profiler. Microsoft will pub lish security bulletins and patches as appropriate
Enforced password policy for standard logins Execution context on modules Data Definition Language (DDL) triggers Data encryption within the database Clustering authentication Multiple proxy accounts No dependency on the Local Security Authority (LSA) database SQL Profiler no longer requires system administrator rights Analysis server communication encryption with server-defined policies Granular administrative roles for Analysis server
SQL Server Agent job roles New tools and Help files
Improved auditing capability for Analysis Services Security bulletins
for SQL Server 2005. These bulletins help you understand and assess potential threats to your existing environments, and how to neutralize those threats. Microsoft Internet Information Services (IIS) Lockdown Wizard If you plan to deploy SQL Server 2005 on a Windows 2000 Server platform, the IIS Lockdown Wizard is a powerful tool for securing your Web server environment. IIS Lockdown Wizard works by turning off features that are unnecessary in your environment, thereby reducing the exposed potential surface available to attack. To provide defense in multiple layers of protection against attackers, a tool called URLScan, with customized templates for each supported server role, is integrated into the IIS Lockdown Wizard. If you are deploying SQL Server 2005 on a Windows Server 2003 platform, the IIS Lockdown Wizard is integrated into IIS 6.0.
For More Information
To find security resources, information, and updates related to SQL Server, visit the SQL Server Security Resources page.