:::: MENU ::::

Posts Categorized / Security and Permissions

  • Jul 02 / 2008
  • 0
dbDigger, Security and Permissions

SQL Server 2008 Security Overview

SQL Server 2008 provides rich security features to protect data and network resources. It is much easier to install securely, since 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, particularly the SQL Server Surface Area Configuration Tool. Its authentication features are stronger because SQL Server more closely integrates with Windows authentication and protects against weak or ancient passwords. Granting and controlling what a user can do when authenticated is far more flexible with granular permissions, SQL Server Agent proxies, and execution context. Even metadata is more secure, since the system metadata views return information only about objects that the user has permission to use in some way. At the database level, encryption provides a final layer of defense while the separation of users and schemas makes managing users easier.

Much has been written and discussed about Microsoft’s Trustworthy Computing initiative that guides all software development at the company. The four essential components of this initiative are:

  • Secure by design: Software requires a secure design as a foundation for repelling attackers and protecting data.
  • Secure by default: System administrators should not have to work to make a fresh installation secure; it should be that way by default.
  • Secure in deployment: Software should help to keep itself updated with the latest security patches and assist in maintenance.
  • Communications: Communicate best practices and evolving threat information so that administrators can proactively protect their systems. These guiding principles are evident throughout SQL Server 2008, which provides all the tools you need to secure your databases.


The first requirement of a secure SQL Server installation is a secure environment. You need to

  • physically secure the server
  • 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
  • enable only the minimum network protocols required.

Use Windows Update to automatically apply SQL Server 2008 patches automatically and reduce threats caused by known software vulnerabilities.

SQL Server 2008 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.

There are far too many configuration options to take the time to write this kind of code – especially when you have multiple instances of SQL Server deployed throughout the organization. SQL Server 2008 includes a policy-based management technology called the Declarative Management Framework (DMF). The DMF provides a number of configuration Facets, each of which defines a set of related configuration settings or properties. You can use these facets to create Conditions that specify the desired settings for the configuration options, and enforce these conditions as Policies to SQL Server instances across the enterprise.

One the of the facets included in SQL Server 2008 is the Surface Area facet, and you can use this facet to define a policy that controls the status of various SQL Server 2008 features. By creating a policy that defines the desired surface area settings for your servers, you can easily enforce a minimal surface area on all SQL Server instances in your organization, and reduce the possibility of malicious attack.

SQL Server 2008 improves on the SQL Server Authentication option. First, it supports encryption of the channel by default through the use of SQL-generated certificates. Administrators do not have to acquire and install a valid SSL certificate to make sure that the channel over which the SQL credentials flow is secure. With SQL Server 2008 automatically generating these certificates, it encrypts the channel automatically by default when transmitting login packets. This occurs if the client is at the SQL Server 2005 level or above.

Note: The native certificate generated by SQL Server protects against passive man-in-the-middle attacks where the attacker is sniffing the network. To secure your systems more effectively against active man-in-the-middle attacks, you should deploy and use certificates that the clients trust as well.

SQL Server 2008 further enhances SQL Server Authentication because, by default, the database engine now uses Windows Group Policy for password complexity, password expiration, and account lockout on SQL logins when used in combination with a Windows 2003 server or above. This means that you can enforce the Windows password policy on your SQL Server accounts.

After authentication, it is time to think about what an authenticated login can do. In this area, SQL Server 2008 and SQL Server 2005 are more flexible than earlier versions. Permissions are now far more granular so that you can grant the specific permissions required rather than grant membership in a fixed role that probably carries with it more permissions than are necessary. You now have far more entities, securables, to which you can assign permissions that are more granular.

In addition to the enhanced protection for user data, structural information and metadata about a particular securable is now available only to principals that have permission to access the securable.

Furthermore, it is possible to create custom permission sets using a mechanism that allows one to define the security context under which stored procedures can run.

In addition, SQL Agent uses a flexible proxy scheme to allow job steps to run and access required resources. All these features make SQL Server more complex but far more secure.

In SQL Server 2008 a principal is any individual, group, or process that can request access to a protected resource and be granted permission to access it. The following list shows the hierarchy of SQL Server 2008 principals, excluding the fixed server and database roles, and how you can map logins and database users to security objects. The scope of the influence of the principal depends on the scope of its definition, so that a Windows-level principal is more encompassing than a SQL Server-level principal, which is more encompassing than a database-level principal. Every database user automatically belongs to the fixed public role.

An important part of any security solution is the ability to audit actions for accountability and regulatory compliance reasons. SQL Server 2008 includes a number of features that make it possible to audit activity.

SQL Server 2008 includes auditing support through the Audit object, which enables administrators to capture activity in the database server and store it in a log. With SQL Server 2008, you can store audit information in the following destinations:

  • File
  • Windows Application Log
  • Windows Security Log

To write to the Windows Security Log, the SQL Server service must be configured to run as Local System, Local Service, Network Service, or a domain account that has the SeAuditPrivilege privilege and that is not an interactive user.

DDL triggers were introduced in SQL Server 2005. Unlike DML triggers that execute Transact-SQL code when data in a table changes, a DDL trigger fires when the structure of the table changes. This is a great way to track and audit structural changes to a database schema.

The syntax for these triggers is similar to that of DML triggers. DDL triggers are AFTER triggers that fire in response to DDL language events; they do not fire in response to system-stored procedures that perform DDL-like operations. They are fully transactional, and so you can ROLLBACK a DDL change. You can run either Transact-SQL or CLR code in a DDL trigger. DDL triggers also support the EXECUTE AS clause similar to other modules.

For more information:

Consult us to explore the Databases. Contact us