SQL Server 2012 Database Security Tasks.

By taking appropriate precautions, most of these breaches can be prevented or detected before they get out of hand.

1. Use a dedicated server for your database

Host your database on a dedicated server. Whether it is local or in the cloud, spend the extra cash on a dedicated server to prevent security leaks and breaches.

2. Harden the Operating System

On your dedicated server, the first step is to implement operating system hardening.

Many hardening techniques exist. At a minimum, you need to:

Change the default ports, as described below.
Hide SQL instances from showing in the network, as described below.
Allow only network protocols that are needed.
CONNECT permission should be granted only on endpoints to logins that need to use them.
If there is a need to work with SQL Login, install an SSL certificate from a trusted CA rather than SQL Server's self-signed certificates.
Avoid the exposure of SQL Server to the public internet/intranet.

Change default ports

The first step in hardening the operating system is to change the default ports. A default installation of SQL Server 2012 use TCP port 1433 for client requests and communications. These ports are well known and are common targets for hackers.

To change the default ports

From the Start menu, choose All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager.
Expand the SQL Server 2012 Network Configuration node and select Protocols for the SQL Server instance to be configured.
In the right pane, right-click the protocol name TCP/IP and choose Properties.
In the TCP/IP Properties dialog box, select the IP Addresses tab.

There is a corresponding entry for every IP address assigned to the server.

Clear the values for both the TCP Dynamic Ports and TCP Port for each IP address, except for the IP addresses under IPAll.
In the IPAll section for each instance, type in a new port for SQL Server 2012 requests and communications.
Click Apply.
Restart the SQL Server Services.

Hide SQL instances from showing in the network

The SQL Server Browser service enumerates SQL Server information on the network. Attackers can use SQL Server clients to browse the current infrastructure and retrieve a list of running SQL Server instances.

To hide SQL instances:

From the Start menu, choose All Programs, Microsoft SQL Server 2012, Configuration Tools, SQL Server Configuration Manager.
Expand the SQL Server 2012 Network Configuration node and select Protocols for the SQL Server instance to be configured.
Right-click Protocols for [Server\Instance Name] and choose Properties.
In the Hide Instance box on the Protocols for [Server\Instance Name] Properties page select Yes.
Click OK.
Restart the services for the change to take effect.

3. Control Admin Access to the Database - Increase Role Based Access Control

You should control not only the individuals who have access to the database, but also how administrators access the database.

Administrator Privileges Control

Elevated permissions are allowed not only for sysadmin users, but also any log in with built-inSA, and also any login with CONTROL SERVER permission. For accountability in the database, avoid relying on the Administrators group and add only specific database administrators to the sysadmin role. For a full description of best practices, see the official documentation by Microsoft entitled SQL Server 2012 Security Best Practice Whitepaper.

Quick Tips for Admin Privileges

Administrator privileges should be used only when they are really needed.
Have as few admins as possible.
Do not use one login for more than one administrator. Each admin should have his or her own account.
Provision admin principals explicitly.
Do not use the "BUILTIN\Administrators" Windows group.
Regularly audit to ensure only the appropriate authorized individuals have admin access privileges.

Removing the Builtin/Administrators Group

Following is a Transact-SQL (T-SQL) syntax for removing the BUILTIN\Administrators Windows Group from a SQL Server instance. You should use this if a group exists from previous versions of SQL Server or using BETA code.

To remove the Builtin/Administrators Group, run the following code on each SQL Server instance installed in the organization:

USE MASTER

IF EXISTS (SELECT * FROM sys.server_principals

WHERE name = N’BUILTIN\Administrators’)

DROP LOGIN [BUILTIN\Administrators]

Control Admin Access Routes to the Database

Not only can you restrict the individuals who have admin access, but you can also restrict the routes of admin control. Using a tool such as GreenSQL, you can ensure that access to admin privileges can come only from certain IP addresses or specific computers. This way, if someone leaves the company or if login information is compromised, it will be impossible for anyone else to use that login data.

Managing Non-Administrative Users

It's important to manage users who do are not admin but have access to the database for other purposes. As with system administrators, it's important to not only give different authentication to different types of users, but also to control the routes of access to the database.

SQL Server instance can contain many databases which were created by users who are database owners -DBO (by default) as shown in the following image: User workshop created the workshop database and is a member of db_owner database role.

Members of server role sysadmin are database owners with the db_owner role in every user database. Members of the db_owner role have approximately the same privileges as the dbo.

Best practices for non-administrator roles:

Minimize the number accounts/users that have the db_owner role for each database.
Have distinct owners for databases; not all databases should be owned by SA or by any other user in sysadmin server role.
Control the access methods and IP addresses for access of the database on a per-role basis.

4. Encrypt the Data Between App and SQL Server 2012

The MS SQL database comes with built-in encryption within the database. However, it is also crucial to encrypt the data as it is passed between the app and the database. Furthermore, it's important to limit access to this information.

Best practices for encryption:

Ensure that DBAs and other people using the database do not have access to sensitive information.
When sending information to users who do not need to know the actual content, mask the sensitive information.
Limit the amount of information that can be drawn from the database by those who have access to the database.
Set up rules to identify authorized and unauthorized use of data, including the IP addresses and routes for accessing data, not username-only authentication.
Set up encryption keys between applications and the database.
Implement cell-level encryption
Implement Transparent Data Encryption
Encrypt high-value and sensitive data.
Use symmetric keys to encrypt data, and asymmetric keys or certificates to protect the symmetric keys.
Password-protect keys and remove master key encryption for the most secure configuration.
Always back up the service master key, database master keys, and certificates by using the key-specific DDL statements.
Always back up your database to back up your symmetric and asymmetric keys.
Perform SSL configuration

Cell Level Encryption

Follow the SQL Server 2012 has an encryption hierarchy, as shown below.

The top-level resource in the SQL Server encryption hierarchy is the Service Master Key, which is encrypted by the Windows Data Protection API. Encrypt all Service Master Keys.
Next is the Database Master Key. This key can be used to create certificates and asymmetric keys.
Third are certificates and asymmetric keys. Both can be used to create symmetric keys or encrypt data directly.
Finally, symmetric keys can also be used to encrypt data.

To encrypt information on a cell level:

Create a Database Master Key. Before creating certificates (assuming the Database Master Key is used to create the certificate) or other keys can be generated, a Database Master Key must be created as follows:

USE <DatabaseName>
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Password>'

1. Backup Database Master Keys. Once a Database Master Key is created, back it up to a secure location, preferably offsite. Back up a Database Master Key as follows:

USE <DatabaseName>
BACKUP MASTER KEY TO FILE = '<FolderLocation>'
ENCRYPTION BY PASSWORD = '<Password>'

Create a certificate for creating symmetric keys for data encryption or to encrypt the data directly, as follows:

USE <DatabaseName>
CREATE CERTIFICATE certXXXXX WITH SUBJECT='XXXXXX Certificate',
EXPIRY_DATE = '03/09/2018''

Backup the certificate as follows:

USE <DatabaseName>
BACKUP CERTIFICATE certXXXXX TO FILE = '<FileLocation>',
ENCRYPTION BY PASSWORD='<Password>'

Encrypt the data via certificate as follows:

INSERT INTO [DatabaseName].[SomeTable]

values( N'data encrypted by certificate ''ThisIsSomething''', EncryptByCert(Cert_ID('certXXXXX'), @cleartext));

TDE – Transparent Data Encryption in SQL Server 2012 (Database Level Encryption)

TDE provides real time encryption of data and log files. It is important to mention that this is database level encryption. Data is encrypted before it is written to disk and decrypted when it is read from disk. The "transparent" aspect of TDE is that the encryption is performed by the database engine and SQL Server clients are completely unaware of it. There is absolutely no code that needs to be written to perform the encryption and decryption.

The database is prepared for TDE, and then the encryption is turned on at the database level via an ALTER DATBASE command. With TDE, the backup files are also encrypted when using just the standard BACKUP command.

To enable TDE:

Create a Master Key. A master key is a symmetric key that is used to create certificates and asymmetric keys. Execute the following script to create a master key:

USE master;

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'Create^A#LongPassw@rd123';

Note that the password should be a strong one (i.e. use alpha, numeric, upper, lower, and special characters).

Back up the Master key using BACKUP MASTER KEY and store it in a secure location.
Create a Certificate. Certificates can be used to create symmetric keys for data encryption or to encrypt the data directly. Execute the following script to create a certificate:

USE master;

CREATE CERTIFICATE TDECert

WITH SUBJECT = 'TDE Certificate'

Backup the certificate using BACKUP CERTIFICATE and store it in a secure location.
Create a Database Encryption Key.A database encryption key is required for TDE. Execute the following script to create a new database and a database encryption key for it:

CREATE DATABASE mssqltips_tde

USE mssqltips_tde;

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDECert

To work with TDE, the encryption key must be encrypted by a certificate (a password will not work) and the certificate must be located in the master database.

Enable TDE by executing the following script:

ALTER DATABASE mssqltips_tde

SET ENCRYPTION ON

SELECT [name], is_encrypted FROM sys.databases

SSL Configuration in SQL Server

Microsoft SQL Server can use Secure Sockets Layer (SSL) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. Enabling SSL encryption increases the security of data transmitted across networks between instances of SQL Server and applications.

To Configure SSL for SQL Server:

Install a certificate in the Windows certificate store of the server computer.

Click Start, in the Microsoft SQL Server program group, point to Configuration Tools, and then click SQL Server Configuration Manager.
Expand SQL Server Network Configuration, right-click the protocols for the server you want, and then click Properties.
On the Certificate tab, configure the Database Engine to use the certificate.
On the Flags tab, view or specify the protocol encryption option. The login packet will always be encrypted.

When the ForceEncryption option for the Database Engine is set to Yes, all client/server communication is encrypted and clients that cannot support encryption are denied access.
When the ForceEncryption option for the Database Engine is set to No, encryption can be requested by the client application but is not required.

Restart SQL Server after you change the ForceEncryption setting.

6. Reduce the Potential Attack Surface

Attack Surface refers to the potential entrances for attack. It's advisable only to enable the features that are essential for any given database.

SQL Server comes with several features that administrators can choose to install during the installation process:

Database Engine
Reporting Services
Integration Services
Analysis Services Engine
Notification Services
Documentation and Samples (Sample databases & codes)

Analyze your needs and install only the features you need.

Surface Area Reduction Practices

Use the Surface Area Configuration Tool or sp_configure as described below.
Do not install sample databases and sample codes on SQL servers in the production environment.
Use only development and test environments for sample databases and sample code on SQL servers.
Use the Configuration tools such as sp_configure or SQL Server Surface Area Configuration tool (described below) to enable only needed features.
When upgrading from SQL Server 2000 to 2005 and higher, review the configuration settings and turn off features such as the xp_cmdshell. The upgrade process does not change these settings by default.
Turn off unnecessary services by setting them to disabled or manual startup.
Disable unneeded system stored procedures as described below
Use SQL Server Surface Area Configuration to enforce a standard policy for extended procedure usage.
Document each exception to the standard policy.
Do not remove the system stored procedures by dropping them.
Do not DENY all users/administrators access to the extended procedures.

SQL Server Surface Area Configuration Tool

SQL Server 2012 contains configuration tools such as a system stored procedure calledsp_configure and the SQL Server Surface Area Configuration tool (for services and features). These tools make it easier to enable and disable optional features. Typically the features are disabled by default.

Following are the features that can be enabled using the tool:

xp_cmdshell
SQL Server Web Assistant
CLR Integration
Ad hoc remote queries (the OPENROWSET and OPENDATASOURCE functions)
OLE Automation system procedures
System procedures for Database Mail and SQL Mail
Remote use of a dedicated administrator connection

The CLI version of the tool - sac.exe can be found at %Program Files%\Microsoft SQL Server\90\Shared. Using these tools you can import and export settings between several servers on the network. You must have sysadmin privilege to use this tool.

Following is an example of exporting all settings from the default instance of SQL Server on server A and importing them to server B:

sac out serverA.out -S serverA -U admin -I MSSQLSERVER

sac in serverA.out -S serverB

The following code snippet describes how to remove SQL Server components by using shell commands. At the command prompt, run the following command:

%ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe /Remove

Uninstall the SQL Server components one at a time until desired SQL Server components are uninstalled

Stored Procedure: sp_configure

sp_configure is a system-stored procedure which can be used instead of the Surface Area Configuration Tool to enable and disable the features in SQL Server. Following is an example of using sp_configure to disable the xp_cmdshell command which enables to run shell commands on the server. The xp_cmdshell is turned off by default in SQL Server 2005 and on.

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 0

RECONFIGURE

Disable Unneeded System Stored Procedures

SQL Server comes with various system stored procedures such as xp_cmdshell orsp_send_dbmail that interact with operating system or execute code outside of a normal SQL Server permissions and may constitute a security risks. It's important to disable those procedures you don’t need.

In SQL Server 2005 it is possible to enable and disable these features in the Surface Area Configuration tool at Start -> All Programs -> Microsoft SQL Server 2012 -> Configuration Tools -> Surface Area Configuration tool.

The following features are disabled by default and should stay disabled:

Disable xp_cmdshell unless it is absolutely needed.
Disable COM components once all COM components have been converted to SQLCLR.
Disable both mail procedures (Database Mail and SQL Mail) unless you need to send mail from SQL Server. Prefer Database Mail as soon as you can convert to it.

Notes: Some system stored procedures, such as procedures that use SQLDMO and SQLSMO libraries, cannot be configured by using SQL Server Surface Area Configuration. They must be configured by using sp_configure or The SQL Server Management Studio (SSMS) directly.

In SQL Server 2012, the SAC was replaced by Policy Based Management framework. This could be accessed from the SSMS under the Management option. It allows you to define and enforce policies for configuring and managing SQL Server across the enterprise.

7. Implement Strong Authentication

Use Windows Authentication mode, described below, when possible.
Use Mixed Mode Authentication, described below, only for legacy applications and non-Windows users.
SQL Authentication mode is described below, but it is NOT the recommended mode. It should be used only when in mixed mode, to leverage complex passwords and the SQL Server /2012 password and lockout policies
Maintain a strong password policy for the SA account and change the password periodically.
Do not manage SQL Server using the sa login account. Assign sysadmin privilege to a knows user or group.
When using Mixed Mode Authentication beware that potential attackers are aware of the SA user. Knowing the SA user makes cracking the database one step easier. To avoid this, in mixed mode, the SA account must be renamed. Before renaming make sure there is at least one additional account with administrator privileges, to access the SQL Account.
To rename the SA User:

USE MASTER

ALTER LOGIN sa DISABLE;

ALTER LOGIN sa WITH NAME = [WinnerUser];

Note: Before renaming the SA account, verify that another account with administrative privileges exists in order to have access to the SQL Server.

Windows and SQL Authentication Modes

SQL Server 2012 supports two modes for validating connections and authenticating access to database resources: Windows Authentication mode and SQL Server and Windows Authentication mode. Both authentication methods provide access to SQL Server and its resources. Windows authentication is preferred.

Windows Authentication Mode

Windows Authentication mode is the default and recommended authentication mode. It leverages local accounts, Active Directory user accounts, and groups when granting access to SQL Server. In this mode, you, as the database administrator, can grant domain or local server users access to the database server without creating and managing a separate SQL Server account. Another advantage of using Windows Authentication is management of password policy by the Active Directory or the local security policy.

Mixed Mode: SQL Server & Windows Authentication

The SQL authentication mechanism is based on accounts that are managed inside the SQL server, including the password policy.

Mixed authentication (SQL Server and Windows Authentication mode) is still required if there is a need to support legacy applications, or if specific applications require mixed mode, or clients are coming in from platforms other than Windows and a need for separation of duties exists.

Configuring SQL Server Authentication Modes

To select or change the server authentication mode, follow these steps:

In SQL Server Management, right-click on a SQL Server and click Properties.
On the Security page, select the desired server authentication mode under Server Authentication and click OK.
In the SQL Server Management Studio dialog box, click OK to acknowledge the need to restart SQL Server.
In Object Explorer, right-click on a desired server and then click Restart.
If the SQL Server Agent is running, restart the agent.

Using Windows authentication is a more secure choice. However, if mixed mode authentication is required, you must make sure to leverage complex passwords and the SQL Server 2012 password and lockout policies to further bolster security.

Here is an example of password policy for SQL accounts:

The password must contain uppercase & lowercase letters.
The password must contain numbers & alphanumeric characters.
The password must contain non-alphanumeric characters such as &, ^,%,*,$ etc.
Do not use common known passwords that are easy to guess such as: admin, password, sa, administrator, sysadmin etc.
Passwords contain a minimum of 8 characters.

SQL Server 2005 and on do not allows blank password for the SA account. If you are using earlier version of SQL, set a password for SQL accounts and also for the SA account according to according to password policy.

Note: If Windows Authentication mode is selected during installation, the SA login is disabled by default. If the authentication mode is switched to SQL Server mixed mode after the installation, the SA account is still disabled and must be manually enabled. It is a best practice to reset the password when the mode is switched.

8. Perform Regular and Reliable Auditing

For reliable auditing it is necessary to use a third-party tool such as Green SQL. Many companies think of auditing as something that must be done to comply with regulation. However, it's also an important internal security precaution in and of itself, and should be performed regularly. Therefore, it's recommended to choose a third-party auditing tool that is quick and simple to use.

Additional Instructions

Auditing is scenario-specific. Balance the need for auditing with the overhead of generating addition data.
Audit successful logins in addition to unsuccessful logins if you store highly sensitive data.
Enable C2 auditing or Common Criteria compliance only if required by selecting the appropriate checkbox (Those options should be selected only if there is a need to comply with these security standards)

Auditing Mechanism in SQL Server

SQL Server security auditing monitors and tracks activity to log files that can be viewed through Windows application logs or SQL Server Management Studio. SQL Server offers the following four security levels with regards to security:

None—Disables auditing (no events are logged)
Successful Logins Only—Audits all successful login attempts
Failed Logins Only—Audits all failed login attempts
Both Failed and Successful Logins—Audits all login attempts

The default mode is: Failed Logins Only. It is recommended to set the auditing mode to be Both Failed and Successful Logins.

Configuring SQL Server Security Logs for Auditing

To configure security login auditing for both failed and successful logins:

In SQL Server Management Studio, right-click on a desired SQL Server and then clickProperties.
On the Security page under Login Auditing, select the desired auditing criteria option button, such as Both Failed and Successful Logins, and click OK.
Restart the SQL Server Database Engine and SQL Server Agent to make the auditing changes effective.

C2 Audits

To configure the C2 audit, activate (in Query Analyzer or osql.exe) and run the following (Command example):

EXEC sp_configure 'show advanced option', '1'

RECONFIGURE

EXEC sp_configure 'c2 audit mode','1'

RECONFIGURE

9. Update Patches Regularly

Security updates and patches are constantly being released by Microsoft. Install these updates made available for SQL Server and the operating system. These patches can be manually downloaded and installed, or they can be automatically applied by using Microsoft Update. It's recommended to test updates before applying to production systems, therefore many admins prefer not to use auto update.

Best practices for Patch Updates

Always stay as current as possible.
Enable automatic updates whenever feasible, but test them before applying to production systems.

10. Manage Contained Databases for SQL Server 2012 Only

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. This situation requires additional security steps. It's important to enable partially contained databases delegates control over access to the instance of SQL Server to the owners of the database.

The main concepts are covered in Security Best Practices with Contained Databases.

To summarize, for managing contained databases, you will need to:

Handle threats related to users, including:
- Accessing other databases using a guest account
- Duplicate logins
- Changes in containment status of databases
- Attaching a contained database
- Password management
- Kerebos authentication
- Offline dictionary attacks
- Auditing of contained databases
- Denial of services through AUTO_CLOSE

MS SQL SERVER DBA

Search This Blog