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.
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”.
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:-
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
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