Security in SQL Server 2005
SQL Server 2005 makes significant enhancements to the security model of the database platform, providing more precise and flexible control to enable tighter security of the data. A considerable investment has been made in features that help to provide a high level of security for your enterprise data. These features include the advanced security of surface area reduction, data encryption, native encryption, authentication, granular permissions, and user and schema separations. These advancements contribute to Microsoft’s Trustworthy Computing initiative that defines the steps necessary to help support secure computing and helps you deploy and maintain a secure environment.
| • | Surface Area Reduction and Advanced Security. SQL Server 2005 provides rich security features to protect data and network resources. It is much easier to achieve a secure installation of the software, because all but the most essential features are either not installed by default or disabled if they are installed. SQL Server provides plenty of tools to configure the server. Its authentication features make it harder to get access to a server running SQL Server by integrating more closely with Windows authentication and protecting against weak or old passwords. Granting and controlling what a user can do when authenticated is far more flexible with granular permissions. |
| • | Surface Area Configuration. SQL Server 2005 includes the SQL Server Surface Area Configuration Tool, which provides an intuitive graphical user interface (GUI) for configuring the server. Running this tool should be your first task after installing SQL Server. The tool opens with a brief explanation of its purpose, and a link to documentation. It includes a link to configure services and protocols and another to configure other features. |
| • | Off by Default. To reduce the SQL Server 2005 surface area to unauthorized access after initial installation, a number of services have been turned off or set for manual start-up so no inadvertent access is granted. Services that are off by default include the Microsoft .NET Framework, Service Broker network connectivity, and HTTP connectivity for Analysis Services. Services that require manual intervention to start include SQL Server Agent, Full Text Search, and Integration Services, which can all be reset for automatic start-up. |
| • | Data Encryption. Security at the server level is probably the greatest concern for system administrators, but the database itself is the primary focus in a production environment. Database administrators can let developers focus on the database details, as long as the developer functions within the environment’s constraints. SQL Server 2005 provides plenty of new features for securing the database. |
| • | Native Encryption. SQL Server 2005 supports encryption capabilities within the database itself, fully integrated with a key management infrastructure. By default, client/server communications are encrypted. To centralize security assurance, server policy can be defined to reject unencrypted communications. |
| • | Authentication. SQL Server 2005 clustering supports Kerberos authentication on a virtual server. Administrators are able to specify Microsoft Windows–style policies on standard logins so that a consistent policy is applied across all accounts in the domain. |
| • | Granular Permissions. Permissions to perform a variety of database tasks have been made more granular to narrow the scope of rights that must be granted. This principle of least privileges helps ensure that database users have sufficient rights to do their tasks but only their tasks. The need to grant broad administrative rights to perform routine maintenance tasks has also been significantly decreased. |
| • | User and Schema Separation. Until the release of SQL Server 2005, an implicit link connected users and the database objects they own. It has been a requirement that all database objects owned by a user be dropped or reassigned before a user could be removed from the database. With SQL Server 2005, this link no longer exists and dropping users no longer requires application changes. |
SQL Server and Trustworthy Computing
The Trustworthy Computing initiative outlines a framework that defines the steps necessary to help support secure computing and provides 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 life cycle—from design, to delivery, to maintenance. To uphold the 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 their interaction. For each potential security threat, the team performed a threat analysis to evaluate the issue and complete additional design and testing work to neutralize potential security issues. Because of these design efforts, SQL Server 2005 includes many new server security features. |
| • | Secure by default. Upon installation, SQL Server 2005 chooses the right set of configuration values for all setup options, ensuring that when a new system is installed, it will be in as secure a state as possible—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. |
Other examples of these Trustworthy Computing initiative design tenets include the use of views to access system tables, more adaptable enforcement of password policies, and improved database encryption capabilities. All of these features are supported by a comprehensive Microsoft communications strategy that enables users of SQL Server to be alerted to new security threats, be advised on what action to take, and be provided with the tools to implement any updates.