SQL Server Hardening

The estimated reading time for this post is 6 minutes

SQL Server hardening can be implemented on instance, database and network levels. SQL Server applications rely on data, and protecting that data has never been more important than it is today. SQL Server is a popular target for hackers, so your data is at risk of being intentionally compromised. In addition, your data is at risk of being accidentally compromised. You can minimize these risks by hardening SQL Server.

SQL Server Components

Install only needed SQL Server components, such as Database Engine, Integration Services. Do not install Reporting Services component beside Database Engine due to Web Services and security holes that Reporting Services opens for hackers.

Network Ports and Protocols

Only enable and use needed protocols such as TCP/IP in the SQL Server configuration manager and do not use the default SQL Server network ports such as 1433, take some actions to change the default port numbers to reduce the DoS attack or network sniffing.

Virtual Service Accounts

—It is a new feature in Windows Server 2008 R2 and it does not require password management. By using Virtual Service Accounts, SQL Server can access network with a computer identity, like Network Service account. It gives Network Service account security benefits with a distinct account per service and it cannot be deleted, and it is available whenever the service is installed on the Windows Server 2008 R2. Virtual Service Account can be part of a local group and like a normal user on local ACL rights.

vsa

Encrypting the Session with SSL

—SQL Server is able to encrypt every sessions for protecting information from network sniffing attempts. To enable this feature you need to purchase an SSL certificate from a Certificate Authority (CA), such as VeriSign, Comodo or DigiCert. Make sure that certificate is installed using the same account running SQL Server service or an account with administrator privileges. After certificate installation, SQL Server database engine firewall rule should be configured to accept only certified connections by enabling “Allow the connection if it is secure”.

ssl

Disable Instance Configurations

SQL Server Instance configuration by default has few security risks, which needs to be mitigated. “xp_CMDShell” and “OLE Automation Procedures” should be disabled. By using “OLE Automation Procedures” hackers can run scripts to control Operating System, File System and SQL Server. The following example calls the Connect method of the previously created SQLServer object.

EXEC @hr = SP_OAMETHOD @OBJECT, 'Connect', NULL, 'my_server',
    'my_login', 'my_password'
IF @hr <> 0
BEGIN
   EXEC SP_OAGETERRORINFO @OBJECT
    RETURN
END

Encrypting data with Symmetric Keys

—SQL Server Symmetric key is faster than Asymmetric key, but it comes with less security, Symmetric key is accepting vary range of algorithm in SQL Server, such as: DES , TRIPLE_DES , TRIPLE_DES_3KEY , RC2 , RC4 , RC4_128 , DESX , AES_128 , AES_192 , AES_256. Base on algorithms you better stick with AES, which is strongest. Keep in mind that, longer the key results strong data protection. Use the sys.Openkeys DMV shows you the keys that are opened for the current session only.

Consider the following statements:

--Create the Symmetric Key encrypted by password.
CREATE Symmetric KEY SymKey 
WITH Algorithm = AES_256 , 
Key_Source = 'SymKey Source’,
Identity_Value = 'SymKey VALUE’
Encryption BY Password = 'SymKeyPassword',
			 Password = 'SymKeyPassword2';
Go
OPEN Symmetric KEY SymKey Decryption BY Password = 'SymKeyPassword';
-- Do something here…
CLOSE Symmetric KEY SymKey;

 

Authenticating Stored Procedures by Signature

In other way to raise the security in high level, DBAs can use certificate to sign the stored procedure and grant required permissions to it and assign the certificate user to the real users such as the following illustration:-

sign sp

The following statement is an example for above illustration, database developers can implement in-depth security in database environment itself.

CREATE Certificate SignCert Encryption BY Password = 'Pa$$w0rd’
With Subject = 'Certificate', Expiry_date = '12/12/2013';
Go
Add Signature to TestCertSP By Certificate SignCert With Password = 'Pa$$w0rd’ ;
Go
CREATE USER CertUser FROM Certificate SignCert;
Go
GRANT EXECUTE ON TestCertSP TO CertUser;

 

Protecting SQL Server Against Denial of Service Attack

—The goal of DoS attack  is to overload the SQL Server with requests to crash it or make it unavailable for normal operations.

  • —First solution is to protect the SQL Server by Network Firewall to block suspicious IP addresses.
  • —Second solution is, SQL Server connections must be limited.
  • —Third solutions is to enable Use Query Governor to Prevent long-running Queries.
  • —Forth solution is using Resource Governor.

Consider the following statements to avoid DoS attack using SQL Server Resource Governor.

RETURNS sysname
WITH Schemabinding
AS BEGIN
 
	DECLARE @workloadGroup sysname;
 
	IF(HOST_NAME() = 'FS-2050A' OR APP_NAME() = 'Microsoft SQL Server Management Studio - Query')
		SET @workloadGroup = 'SSMS Group';
	ELSE
		SET @workloadGroup = 'Default';
 
	RETURN @workloadGroup;
END
 
Go
--Create resource pool for SSMS app
CREATE Resource Pool [SSMS Pool] 
WITH (	min_cpu_percent = 0 , 
		max_cpu_percent = 20 , 
		min_memory_percent = 25 , 
		max_memory_percent = 40);
 
Go
 
--Create worload group with SSMS Pool is included
CREATE Workload GROUP [SSMS GROUP]
WITH (	group_max_requests = 0 , 
		importance = low , 
		request_max_cpu_time_sec = 1 , 
		request_max_memory_grant_percent = 10 , 
		request_memory_grant_timeout_sec = 1 , 
		max_dop = 0) 
USING [SSMS Pool]
Go
--Alter the resouce governor to use the function
ALTER Resource Governor WITH ( Classifier_Function = dbo.GovernorClassifierFunc );
Go
ALTER Resource Governor RECONFIGURE;

 

Transparent Data Encryption

Transparent Data Encryption (TDE) encrypts SQL Server and Azure SQL Database data files, known as encrypting data at rest. You can take several precautions to help secure the database such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this kind of protection must be planned in advance. TDE performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.

Backup files of databases that have TDE enabled are also encrypted by using the database encryption key. As a result, when you restore these backups, the certificate protecting the database encryption key must be available. This means that in addition to backing up the database, you have to make sure that you maintain backups of the server certificates to prevent data loss. Data loss will result if the certificate is no longer available.

Consider the following statement as TDE implementation:-

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$W0RD';
go
CREATE CERTIFICATE TDECert WITH SUBJECT = 'My DEK Certificate';
go
USE AdventureWorks2012;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECert;
GO
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
GO

 

Physical Security

Best practices for physical security strictly limit access to the physical server and hardware components. For example, use locked rooms with restricted access for the database server hardware and networking devices. In addition, limit access to backup media by storing it at a secure offsite location.

Operating System Security

Operating system service packs and upgrades include important security enhancements. Apply all updates and upgrades to the operating system after you test them with the database applications.

Firewalls also provide effective ways to implement security. Logically, a firewall is a separator or restrictor of network traffic, which can be configured to enforce your organization’s data security policy. If you use a firewall, you will increase security at the operating system level by providing a chokepoint where your security measures can be focused.

Author: Hamid Jabarpour Fard

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz