Protecting database information from threats and vulnerabilities is vital for any organization. This article discusses 10 new security features introduced in SQL server 2008 that facilitate effective management of the database by using server surface area configuration (policy-based management configuration), full database encryption (TDE), enhanced auditing features and more.
information from threats and vulnerabilities is very important for any
organization; security should be a critical feature of any database engine. The
new security features in Microsoft SQL server 2008 are designed to make the
database more secure. This article discusses the top 10 new security features
introduced in SQL server 2008.
Feature -1 Policy-Based Management Configuration
SQL server 2008 introduces policy-based management, which helps DBAs to define standard rules or policies
and enforce these rules for configuring and managing SQL Server databases
throughout the enterprise. By using the new surface area facet to control
active services and features, policy-based management reduces database exposure
to security threats. You can apply policy-based management on servers,
databases, and database objects (tables, indexes, etc.) across the enterprise.
DBAs can define policy-based management configuration using SQL Server Management
Studio Environment. The policy management node is available under the
management node in Object Explorer (a dockable window present in SQL Server Management
Studio); where you will also see policies, conditions, and facets sub nodes. To
define a new policy from SQL Server Management Studio you need to set following:
- Target entity – Database or database object on which you
would like to enforce your policy.
- Facet – SQL server 2008 already has a predefined set of
rules under the facets sub node. You just need to select the appropriate
facet while creating a condition. Each facet contains a list of
properties. For example, the auto shrink database option is a property.
- Condition – An expression that will return either true or
false value. Your policy-based management will test whether a condition
has returned true or false.
- Policy – You can create a new policy by right clicking on
the policies node and selecting new option. While creating a new policy
you need to select a predefined condition and execution mode. You can
schedule a policy using SQL agent (by creating a Job). For on demand
execution, select the policy from Object Explorer and select evaluate.
-2 Transparent Data Encryption (TDE)
Transparent data encryption (TDE) enables application developers to
encrypt an existing SQL server database without changing the application tires.
In SQL Server 2000, developers need to write an extended stored procedure to
implement encryption on data but not on the full database; or they need to use
third party tools like DbEncrypt. To implement encryption in SQL Server 2005,
all table column data types must be varbinary. Microsoft has overcome these
issues by introducing TDE a full database level encryption with SQL Server
2008. TDE is designed to provide protection for the entire database without
affecting existing applications. (See "Transparent Data Encryption (TDE)
in SQL Server 2008").
-3 Extensible Key Management (EKM)
It’s always recommended to store database encryption keys and passwords
separately from the data. The Extensible Key Management (EKM) feature of SQL
server 2008 enables you to manage encrypted keys and passwords to external
hardware devices like smart cards, USB devices or a hardware security module
(HSM). EKM uses the Microsoft Cryptographic API (MSCAPI) provider for
encryption and key generation. Third party vendors also provide enterprise key
management by using HSM. These HSM devices store encryption keys on hardware or
software modules that makes a database more secure because the encryption keys
do not reside with encryption data. Before implementing EKM, you need to
explicitly execute the sp_configure command to enable this feature.
sp_configure 'show advanced', 1 GO RECONFIGURE GO sp_configure 'EKM provider enabled', 1 GO RECONFIGURE GO
-4 Authentication improvements and enforce password policies
There are significant improvements in SQL Server 2008 authentication rules.
Now it supports encryption of the channel by default through the use of
SQL-generated certificates. In addition, the database engine uses Windows group
policy for password complexity, password expiration, and account lockout.
SQL server 2008 password policy is also different from previous versions of
SQL Server. New password policy is built using NetValidatePasswordPolicy ()
API, which is part of the NetAPI32 library on Windows Server 2003 (enforce
minimum password length, proper character combinations, and regularly-changing
passwords).You can’t create simple passwords like ‘sa’ in SQL server 2008 only
complex passwords are recommended in production environments. To modify
existing SQL Server 2008’s password policy, you must change the authentication
mode to mixed and execute the following SQL command:
ALTER LOGIN sa WITH PASSWORD = 'sa' UNLOCK, CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
Feature -5 Protecting Metadata
SQL Server 2008 protects metadata at the granular level. In SQL Server 2000,
any user having database access could view metadata information. However, SQL
Server 2008 protects metadata by providing permissions to the owner who has access
on metadata objects. In addition, there is a view definition available for
granting permission to users for accessing metadata. SQL Server 2008 also shows
an error message if a user with no permissions wants to drop an object from the
database. The following example shows an error message.
Cannot drop the table 'tbluser', because it does not exist or you do not have permission.
Feature -6 Use the built-in cryptography hierarchy in SQL Server 2008
SQL Server 2008 supports different types of data encryption
using symmetric and asymmetric keys, and digital certificates. On the server
level, there is a service master key and within the scope of a database, there
is database master key. The scope of the database master key is on the entire
database objects, certificates, and data in the database. Each database can
have a single master key. You can create a database master key with the CREATE
MASTER KEY T-SQL statement.
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hVJ#Kh95234nl0zBe'; GO
After creating the database master key, a developer can create asymmetric
keys, symmetric keys or certificates depending on the type of encryption policy
they want to apply on the database.
Feature -7 Sign code modules
SQL Server 2008 provides the flexibility to sign your stored procedures,
functions, triggers, and event notifications digitally with certificates. Using
a code module you can restrict database objects to be available through
certificates only. This provides more granular level control over access to
database objects. Sign code modules also provide you the additional benefit of
protecting your database code against unauthorized changes.
The following code snipped shows the steps sign code module creation for
providing access to TestUser on TestSchema.GetUserAccess procedure.
CREATE CERTIFICATE TestCodeSigningCert ENCRYPTION BY PASSWORD = 'tJI%@V4!axnlXflC' WITH SUBJECT = 'Test Code signing certificate' GO -- Sign the stored procedure ADD SIGNATURE TO TestSchema.GetUserAccess BY CERTIFICATE TestCodeSigningCert WITH PASSWORD = ' tJI%@V4!axnlXflC ' GO -- Map a user to the certificate CREATE USER TestUser FOR CERTIFICATE TestCodeSigningCert GO --Assign SELECT permissions to new TestUser GRANT SELECT ON SocialSecurity TO TestUser GO -- Grant execute permission to the user who will run the code GRANT EXECUTE ON TestSchema.GetUserAccess TO ProcedureUser GO
Feature -8 Auditing in SQL Server 2008
SQL Server 2008 introduces a number of new auditing features. With the newly
included audit object, you can log audit information in a file, Windows application
log and Windows security log. You can create an audit object using the CREATE
SERVER AUDIT statement. The following T-SQL code creates two audit objects; the
first one logs activity to a file, and the other to log activity to the windows
CREATE SERVER AUDIT TEST_File_Audit1 TO FILE ( FILEPATH='SQLSERVPROD_1Audit_LogTest' ); CREATE SERVER AUDIT TEST_File_Audit2 TO APPLICATION_LOG WITH ( QUEUE_DELAY = 500, ON_FAILURE = SHUTDOWN);
-9 Enhance security features with execution context
SQL Server 2008 identifies modules (SQL statements in stored procedures,
functions) with an execution context so that T-SQL statements within the module
execute under a particular user instead of the calling user. When creating user
defined functions, stored procedures and triggers you can use the EXECUTE AS
clause to specify which user’s permissions SQL Server uses to validate access
to objects. In the following example, TestProc
will execute under TestUser1 user.
CREATE PROCEDURE TestProc(@TestParam varchar(50)) WITH EXECUTE AS 'TestUser1'
-10 Use Windows Update to automatically apply SQL Server 2008 patches
To avoid security threats
and vulnerabilities you need to periodically update SQL server patches provided
by Microsoft. You can now use Windows update to automatically download SQL Server
2008 patches and install throughout the enterprise to reduce threats caused by
known software vulnerabilities.
SQL Server 2008 enhanced the security features to protect the database and
other resources. SQL Server 2008 provides many tools to configure the database
server and more specifically, the configuration of server surface area. New
authentication and password policies provide granular level security. Metadata
is also more secure now as only authenticated and permitted users can access
that. Full database encryption can be provided now using TDE.