Security Features 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.

Key Features

The table below provides an overview of new and enhanced security features in SQL Server 2005.

Feature	Description
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.
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.
Granular permission control	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.
Separation of users and schema	SQL Server 2005 simplifies 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 significantly complicated the process and potentially impacted a large number of applications. With the new model, dropping users does not require an application change.
Enforced password policy for standard logins	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.
Execution context on modules	SQL Server 2005 allows you to specify a context under which statements in a module execute. This feature also acts as an excellent mechanism for granular permission management.
Data Definition Language (DDL) triggers	With SQL Server 2005 you are able to specify triggers on DDL operations, providing a supplemental mechanism for auditing DDL actions.
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.
Clustering 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.
Multiple proxy accounts	SQL Server Agent supports multiple proxy accounts (one per job subsystem).
No dependency on the Local Security Authority (LSA) database	SQL Server Agent no longer requires access to the LSA to use proxy accounts. Therefore, SQL Server Agent no longer requires the service to run as a local administrator for it to be enabled.
SQL Profiler no longer requires system administrator rights	A new permission is available in SQL Server 2005 that allows users who do not have system administrator rights to run SQL Profiler.
Analysis server communication encryption with server-defined policies	By default, client/server communications are encrypted. To centralize security assurance, server policy can be defined to reject unencrypted communications.
Granular administrative roles for Analysis server	More administrative permissions are available in SQL Server 2005. In addition to online analytical processing (OLAP) administrators, database administrators are able to possess administrative permissions within the context of an individual database. New permissions on objects enable users to see the object definition (without being able to access the object itself) and to process an object.
SQL Server Agent job roles	SQL Server Agent has been enhanced to support assigning rights over jobs in a granular fashion.
New tools and Help files	A set of new deployment tools and documentation helps ensure that SQL Server 2005 can be securely deployed into an existing 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.
Improved auditing capability for Analysis Services	SQL Server 2005 Analysis Services includes new auditing capabilities integrated with SQL Profiler.
Security bulletins	Microsoft will publish security bulletins and patches as appropriate 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.

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.

Security Features in SQL Server 2005

Key Features

SQL Server and Trustworthy Computing

Technologies

Try SQL Server

Buy SQL Server