Compared to SQL 2005 we have got pretty good new security features in the latest version SQL 2008. To list out the new features goes like this:
1. Policy Based Management.
2. Transparent Data Encryption.
3. Upgraded Auditing features.
4. Employ Extensible Key Management.
To begin with ‘Policy Based Management’ is one of the new features of SQL 2008. This feature allows defining and enforcing different policies for managing and configuring SQL Server. For example, if we want to enforce a policy which will make sure the DB Options to be set as per standard like ‘Auto Create/Update Statistics’ options should be Turned On or to enforce a policy which checks for naming conventions for all ‘Views’ to start with ‘VW-‘. This feature is available in all editions of SQL Server 2008.
Before going to start how to enforce policies we need to look at set of new terms which are related to
1.Policy-based Management:
1. Policy: It is a condition to be checked and/or enforced.
2. Condition: It is a property expression that evaluates to True or False which gives the state of a Facet.
3. Facet: These are the predefined set of properties that can be managed.
4. Target: It’s an entity that is managed by Policy – Based Management like Database, Table, etc..,
A ‘Target’ will be managed using a set of Pre-defined ‘Facets’ depending on some ‘Condition’ through a ‘Policy’ which enforces the condition to be checked.
2. Transparent Data Encryption:
The next new security feature is TDE (Transparent Data Encryption) this feature allows encryption of data and log files. Data is encrypted before it is written to disk and it’s decrypted when it is read from disk. This feature helps in encrypting backup files as well and these backup files are helpful in restore only if the Certificates are also backed up. This feature is available only in Enterprise Edition of SQL Server 2008.
Following are the things we need to be aware before encrypting any database or its backups:
1. Master Key: A master key is a symmetric key that is used to create certificates and asymmetric keys.
2. Certificate: Certificates can be used to create symmetric keys for data encryption or to encrypt the data directly.
3. Database Encryption Key: A database encryption key is required for TDE.
As a first step, we need to create a ‘Master Key’ before enabling TDE or using already encrypted Backup/Data/Log files.
Creation of Master Key:
USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = ‘P@ssw0rd’;
GO
Creation of Certificate:
We can create a Certificate as shown below
USE master;
CREATE CERTIFICATE TestCertificate
WITH SUBJECT = ‘TDE Certificate’
GO
Creation of Database Encryption Key:
We can create Database Encryption Key as given below:
USE AdventureWorks;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TestCertificate
GO
Enabling TDE:
The below command helps you to enable TDE on any DB:
ALTER DATABASE AdventureWorks
SET ENCRYPTION ON
GO
Note: After creation of Master Key and Certificate they need to be backed up as these are mandatory if we want to re-use the encrypted backup or Data/Log files.
Backup Certificates:
Backup the certificate/Master Key using the below commands:
BACKUP CERTIFICATE DEKCertificate TO FILE = ‘C:\DEKCertificate.cert’
WITH PRIVATE KEY
(
FILE = ‘C:\Instance1PrivateKey.key’,
ENCRYPTION BY PASSWORD = ‘P@ssw0rd’
)
While restoring or using the encrypted files first the Master Key and Certificates needs to be created in the new instance of SQL Server 2008 then only it will allow us to use these files.
Creation of Certificate from Backup:
CREATE CERTIFICATE DEKCertificate
FROM FILE = ‘C:\DEKCertificate.cert’
WITH PRIVATE KEY (
FILE = ‘C:\Instance1PrivateKey.key’
, DECRYPTION BY PASSWORD = ‘P@ssw0rd’
)
Without backups of these keys, it won’t allow us to restore the database using encrypted backup files.
3. Upgraded Auditing features:
This new security feature allows you to monitor any Server level or Database level activities. This can be achieved by using a separate tool called SQL Server Profiler in SQL Server 2005 but this tool has some limitations in providing granular information like ‘who accessed the data’, for configuring/managing traces requires always we require this separate tool profiler and this tool is not accessible via management studio(SSMS).
SQL Server 2008 Audit can automatically log changes made to databases and database server settings and allows administrators to audit a wider range of information and settings than was possible in previous versions, without requiring any custom code or third-party tools. Most notably, administrators can now log changes made to database tables and schema. SQL Server auditing is available only in Enterprise Edition.
Another advantage compared to SQL Server Trace is SQL Server Audit can log events in a binary file that can be viewed using Management Studio, the Windows Application event log, or the Windows Security event log. SQL Server 2005 Trace logs events in a binary file format which require a separate tool called SQL Server Profiler, for viewing.
Now we will create a sample Audit which will trace all successful and failure logins of a server. For this first we will need to create server level Audit as shown below:
Creation of Server Level Audit:
CREATE SERVER AUDIT Audit_Logins TO
File (FILEPATH=’c:\TEMP’, MAXSIZE=100 MB)
WITH (QUEUE_DELAY=2000, ON_FAILURE=SHUTDOWN)GO
Creation of Audit Specification:
Audit specification specifies what sort of actions to be audited:
CREATE SERVER AUDIT_SPECIFICATION Audit_Logins_Specification
FOR SERVER AUDIT Audit_Logins
ADD (SUCCESSFUL_LOGIN_GROUP), ADD (FAILED_LOGIN_GROUP)
GO
Enabling Audits/Audit Specifications:
By default the Audits and Audit Specifications created will be in disabled state we can enable by using below commands:
ALTER SERVER AUDIT Audit_Logins
WITH (STATE=ON)
GO
ALTER SERVER AUDIT SPECIFICATION Audit_Logins_Specifications
WITH (STATE=ON)
GO
To View Audit File:
And in order to view the Audit file we can use the below query:
Select * from sys.fn_get_audit_file (‘C: \filename.sqlaudit’, default, default)
The ‘sys.fn_get_audit_file‘ is an in-line function which requires 3 parameters to be passed. The first parameter is the @file_pattern which we need to provide the file location and the remaining 2 parameters @initial_file_name and @audit_record_offset which we can pass default values.
Disabling the Audit/Specification disables auditing.
4. Extensible Key Management:
One of the new features with SQL Server 2008 is the addition of extensible key management (EKM), allowing hardware devices to be integrated into the encryption/decryption process for the keys securing your data. This feature is available in Enterprise Edition of SQL Server 2008.
Essentially you are registering your device within SQL Server as a way to handle cryptographic functions, kind of like you used to register DLLs so you could implement an extended stored procedure. In this case, there is a new CREATE statement, the CREATE CRYPTOGRAPHIC PROVIDER which registers the DLL for your EKM system. This is provided by the manufacturer and doesn’t get you access to the device. There will be a device specific procedure for logging into the device. Once that is complete, you’ll have to set up a credential and grant a login rights to the credentials for other individual logins to use the HSM device.
There are very few vendors currently who are providing these devices like SafeNetLuna HSM and so on…
Conclusion:
After all SQL Server 2008 is having many new security features which help in providing effective management of security configuration, strong access control, enhanced Auditing and powerful encryption and key management capabilities.
1. Policy Based Management.
2. Transparent Data Encryption.
3. Upgraded Auditing features.
4. Employ Extensible Key Management.
To begin with ‘Policy Based Management’ is one of the new features of SQL 2008. This feature allows defining and enforcing different policies for managing and configuring SQL Server. For example, if we want to enforce a policy which will make sure the DB Options to be set as per standard like ‘Auto Create/Update Statistics’ options should be Turned On or to enforce a policy which checks for naming conventions for all ‘Views’ to start with ‘VW-‘. This feature is available in all editions of SQL Server 2008.
Before going to start how to enforce policies we need to look at set of new terms which are related to
1.Policy-based Management:
1. Policy: It is a condition to be checked and/or enforced.
2. Condition: It is a property expression that evaluates to True or False which gives the state of a Facet.
3. Facet: These are the predefined set of properties that can be managed.
4. Target: It’s an entity that is managed by Policy – Based Management like Database, Table, etc..,
A ‘Target’ will be managed using a set of Pre-defined ‘Facets’ depending on some ‘Condition’ through a ‘Policy’ which enforces the condition to be checked.
2. Transparent Data Encryption:
The next new security feature is TDE (Transparent Data Encryption) this feature allows encryption of data and log files. Data is encrypted before it is written to disk and it’s decrypted when it is read from disk. This feature helps in encrypting backup files as well and these backup files are helpful in restore only if the Certificates are also backed up. This feature is available only in Enterprise Edition of SQL Server 2008.
Following are the things we need to be aware before encrypting any database or its backups:
1. Master Key: A master key is a symmetric key that is used to create certificates and asymmetric keys.
2. Certificate: Certificates can be used to create symmetric keys for data encryption or to encrypt the data directly.
3. Database Encryption Key: A database encryption key is required for TDE.
As a first step, we need to create a ‘Master Key’ before enabling TDE or using already encrypted Backup/Data/Log files.
Creation of Master Key:
USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = ‘P@ssw0rd’;
GO
Creation of Certificate:
We can create a Certificate as shown below
USE master;
CREATE CERTIFICATE TestCertificate
WITH SUBJECT = ‘TDE Certificate’
GO
Creation of Database Encryption Key:
We can create Database Encryption Key as given below:
USE AdventureWorks;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TestCertificate
GO
Enabling TDE:
The below command helps you to enable TDE on any DB:
ALTER DATABASE AdventureWorks
SET ENCRYPTION ON
GO
Note: After creation of Master Key and Certificate they need to be backed up as these are mandatory if we want to re-use the encrypted backup or Data/Log files.
Backup Certificates:
Backup the certificate/Master Key using the below commands:
BACKUP CERTIFICATE DEKCertificate TO FILE = ‘C:\DEKCertificate.cert’
WITH PRIVATE KEY
(
FILE = ‘C:\Instance1PrivateKey.key’,
ENCRYPTION BY PASSWORD = ‘P@ssw0rd’
)
While restoring or using the encrypted files first the Master Key and Certificates needs to be created in the new instance of SQL Server 2008 then only it will allow us to use these files.
Creation of Certificate from Backup:
CREATE CERTIFICATE DEKCertificate
FROM FILE = ‘C:\DEKCertificate.cert’
WITH PRIVATE KEY (
FILE = ‘C:\Instance1PrivateKey.key’
, DECRYPTION BY PASSWORD = ‘P@ssw0rd’
)
Without backups of these keys, it won’t allow us to restore the database using encrypted backup files.
3. Upgraded Auditing features:
This new security feature allows you to monitor any Server level or Database level activities. This can be achieved by using a separate tool called SQL Server Profiler in SQL Server 2005 but this tool has some limitations in providing granular information like ‘who accessed the data’, for configuring/managing traces requires always we require this separate tool profiler and this tool is not accessible via management studio(SSMS).
SQL Server 2008 Audit can automatically log changes made to databases and database server settings and allows administrators to audit a wider range of information and settings than was possible in previous versions, without requiring any custom code or third-party tools. Most notably, administrators can now log changes made to database tables and schema. SQL Server auditing is available only in Enterprise Edition.
Another advantage compared to SQL Server Trace is SQL Server Audit can log events in a binary file that can be viewed using Management Studio, the Windows Application event log, or the Windows Security event log. SQL Server 2005 Trace logs events in a binary file format which require a separate tool called SQL Server Profiler, for viewing.
Now we will create a sample Audit which will trace all successful and failure logins of a server. For this first we will need to create server level Audit as shown below:
Creation of Server Level Audit:
CREATE SERVER AUDIT Audit_Logins TO
File (FILEPATH=’c:\TEMP’, MAXSIZE=100 MB)
WITH (QUEUE_DELAY=2000, ON_FAILURE=SHUTDOWN)GO
Creation of Audit Specification:
Audit specification specifies what sort of actions to be audited:
CREATE SERVER AUDIT_SPECIFICATION Audit_Logins_Specification
FOR SERVER AUDIT Audit_Logins
ADD (SUCCESSFUL_LOGIN_GROUP), ADD (FAILED_LOGIN_GROUP)
GO
Enabling Audits/Audit Specifications:
By default the Audits and Audit Specifications created will be in disabled state we can enable by using below commands:
ALTER SERVER AUDIT Audit_Logins
WITH (STATE=ON)
GO
ALTER SERVER AUDIT SPECIFICATION Audit_Logins_Specifications
WITH (STATE=ON)
GO
To View Audit File:
And in order to view the Audit file we can use the below query:
Select * from sys.fn_get_audit_file (‘C: \filename.sqlaudit’, default, default)
The ‘sys.fn_get_audit_file‘ is an in-line function which requires 3 parameters to be passed. The first parameter is the @file_pattern which we need to provide the file location and the remaining 2 parameters @initial_file_name and @audit_record_offset which we can pass default values.
Disabling the Audit/Specification disables auditing.
4. Extensible Key Management:
One of the new features with SQL Server 2008 is the addition of extensible key management (EKM), allowing hardware devices to be integrated into the encryption/decryption process for the keys securing your data. This feature is available in Enterprise Edition of SQL Server 2008.
Essentially you are registering your device within SQL Server as a way to handle cryptographic functions, kind of like you used to register DLLs so you could implement an extended stored procedure. In this case, there is a new CREATE statement, the CREATE CRYPTOGRAPHIC PROVIDER which registers the DLL for your EKM system. This is provided by the manufacturer and doesn’t get you access to the device. There will be a device specific procedure for logging into the device. Once that is complete, you’ll have to set up a credential and grant a login rights to the credentials for other individual logins to use the HSM device.
There are very few vendors currently who are providing these devices like SafeNetLuna HSM and so on…
Conclusion:
After all SQL Server 2008 is having many new security features which help in providing effective management of security configuration, strong access control, enhanced Auditing and powerful encryption and key management capabilities.
Links:
No comments:
Post a Comment