Introduction
Protecting database 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:
1. Target entity - Database or database object on which you would like to enforce your policy.
2. 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.
3. 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.
4. 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.
Feature -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").
Feature -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
Feature -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 application log.
CREATE SERVER AUDIT TEST_File_Audit1
TO FILE ( FILEPATH='\SQLSERVPROD_1Audit_Log
Test' );
CREATE SERVER AUDIT TEST_File_Audit2
TO APPLICATION_LOG
WITH ( QUEUE_DELAY = 500, ON_FAILURE = SHUTDOWN);
Feature -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'
Feature -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
Full article...
