SQL Server 2005 Security Features at a Glance
The table below provides an overview of new and enhanced security features in SQL Server 2005.
| Feature | Description |
Off by default | SQL Server 2005 enables only a limited number 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 are disabled by default in SQL Server 2005 include: the Microsoft .NET Framework, SQL Service Broker Network Connectivity, and HTTP connectivity in Analysis Services. Other services such as SQL Server Agent, full-text search, and the new Data Transformation Services (DTS) service are set to start up manually and require explicit action to be set to start automatically. |
Granular permission control | A new security model in SQL Server 2005 allows 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 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. |
Data encryption within the database | SQL Server 2005 supports encryption capabilities within the database itself, fully integrated with a key management infrastructure. |
Clustering authentication | SQL Server 2005 clustering supports Kerberos authentication against a SQL Server 2005 virtual server. |
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. |
For More Information
To find security resources, information, and updates related to SQL Server, visit the SQL Server Security Resources page.
