This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from this class, refer them and earn a $50 Amazon gift card – see class pages for details. You can find all the other posts in this series at http://www.SQLskills.com/help/AccidentalDBA. Enjoy!

SQL Server offers multiple layers of security configuration that allow for a defense-in-depth approach to securing business critical data. A strong security implementation starts with the SQL Server installation and service account configuration, and continues into the surface area configuration for SQL Server by minimizing the options that are turned on that may increase the risk of threat from an attack to the instance, and finally securing individual logins and database level user permissions following the principal of least-privilege required to accomplish a task.

Installation and Service Accounts

A secure installation starts with properly selecting service accounts that the individual services will run under. Ideally, if the server is participating in an Active Directory domain, these will be domain accounts that are isolated per service and the accounts shouldn’t have additional permissions added to them except where necessary for the activity required. The service accounts should not be members of the Domain Admins, or the local server Administrators groups under any circumstances.

For most environments, the service account should be created as a low privilege user, and then during SQL Server setup, the required permissions will be added on the local server to allow SQL Server to use the account to run the services. The only additional permissions that should be added in most cases are the Perform Volume Maintenance Tasks and Lock Pages in Memory user rights assignements if Instant File Intialization and locked pages are going to be used by the instance, and then any specific file folder ACLs to allow the instance to access additional paths for data, transaction log, or writing backups to local disk locations. Additionally if network shares are used for backups or for file transfers, eg SSIS packages reading files for data loading, specific permissions for those locations can be added for the account. Permissions on data, transaction log, and backup file locations should be restricted to the service account and administrator users only whenever possible to minimize the accessibility of these files to other users within the domain.

Surface Area Configuration

SQL Server 2008/R2 and 2012 by default take great strides at minimizing the surface area for attack through SQL Server during setup. This low surface area configuration was first introduced in SQL Server 2005 and has been improved with each release to minimize the risks associated with a default installation of SQL Server. Features such as xp_cmdshell, OLE Automation, and SQLCLR are disabled by default to minimize the potential security risks that could occur when these features are enabled. Before enabling any of these features, consider the implications that they might have to the overall security of the server. If the server will host third-party application databases that require these features, there may be little that you can do to prevent enabling these features, but it is always worthwhile to question the need for enabling these to ensure that you’ve properly documented the reasons behind having these features enabled.

As a part of the instance surface area, the Windows Firewall should be configured to allow TCP access to the ports necessary for SQL Server connectivity.  For default instances this usually means enabling TCP ports 1433 and 1434 to allow user connections and connections to the Dedicated Administrative Connection.  For named instances, the default configuration of the instance is to use dynamic ports.  However, this should be changed for each named instance to fix it’s TCP listener port to allow the appropriate Windows Firewall rules to be created.  While it may be easier to disable the firewall, this does not minimize the attack surface for the server, and specific rules should be created instead of disabling the firewall entirely.

Controlling Server Access

An important consideration to overall security is controlling physical access to the server hardware as well as controlling access for who can actually login to the server OS. Physical security is incredibly important and should be thought out as a part of overall infrastructure security. Generally SQL Servers are installed in the corporate data center, but it is not uncommon for servers to also exist in smaller shops in unsecured locations, for example under the administrators desk or in other generally available locations within an office space. This can pose a significant risk for loss of data in the event the server is physically stolen or for downtimes if someone indavertently unplugs the server from power or the network. Servers that contain private information or data should always be locked inside a server closet or secured inside of a datacenter that has limited access to a restricted set of employees only.

Windows access to SQL Servers should equally be restricted to only administrative users that have a need to manage SQL Server or the OS that is running. One important item to remember is that any local administrator on the server has the ability to access the database and log files, backup files, and if they are so inclined gain access to SQL Server using easy to Google/Bing options that would add their account to the sysadmin fixed server role within SQL Server, even if they are not currently able to access SQL Server. The local administrators on a SQL Server should be limited to the Domain Admins group, which should contain a minimum number of users requiring that level of permissions, and, where appropriate for security controls, the database administrators only, other users should not have direct access to the server OS for a SQL Server, and the list of users that have access should be reviewed regularly to ensure that only those users requiring access actually have access to the server.

Controlling SQL Server Permissions

Permissions within the SQL Server instance can be granted at multiple levels from the server instance level to individual databases and even objects with a database. In general server level permissions should be restricted to administrators that need to manage the SQL Server instance. Most server level permissions are granted through the use of one of the many fixed server roles that are a part of the default installation of SQL Server. The sysadmin, serveradmin, and securityadmin roles should be minimized to use only be actual administrator requiring permissions to manage SQL Server. Just like the Local Administrators group in Windows, these roles provide elevated permissions to control the instance, make configuration changes, create additional logins and manage security within the instance.  Application accounts should not be a member of these roles as a matter of least privilege, specifically the sysadmin role. However, certain applications like SharePoint require the securityadmin fixed server role and there is nothing you can do as an administrator to prevent this. The sysadmin role should be audited routinely like the Local Administrators group in Windows to track the logins that have administrative control over the instance.

Application logins should continue the principal of least privilege by only having access to the databases(s) and objects required by them. While permissions to individual objects should be enforced as a general security best practice, the reality of most third-party applications is that the application login will need to be the dbo or a member of the db_owner database role for the databases that are used by the application.  In cases like this, there will also be little that can be done as an administrator to change the security requirements for the application.  However, in cases where a third party application requires elevated permissions to the sysadmin fixed server role, understanding the requirements for this high level role should be a top priority, and any application with this elevated level of access should be isolated from other databases on separate instances to minimize the risk imposed.

Summary

Creating a secure SQL Server installation starts with pre-installation planning of the service accounts, to minimize the permissions the instance has to other resources within the network. After installing the instance, minimizing the surface area through proper firewall configuration and leaving configuration options that are disabled by default disabled unless absolutely necessary continues to build on the defense in depth concept for SQL Server. Auditing of the Local Administrators group in Windows and the sysadmin fixed server role should be a part of normal system security reviews to understand who has administrative access to the server or instance. Further information about SQL Server security can be found in Paul’s 2009 TechNet article Security: Common SQL Server Security Issues  and Solutions.