Tip: Configure Your SQL Server Attack Surface
Security is an exercise in creating enough barriers to the system such that the effort involved to attack a system exceeds the benefit derived from the data. In order to defeat a variety of attacks systems, rely on a “defense in depth” approach that places several barriers in the way of an attacker.
During the installation of your instance, you have the option to specify the authentication mode that is allowed. If you limit access to an instance to Windows logins only, you can prevent a wide variety of attack methods by ensuring that any user connecting to your instance must first authenticate to a Windows domain.
Once the instance has been installed, you can configure the network protocols to allow remote connections. If remote connections have not been enabled, an attacker must first gain access to the machine that your instance is running on. Your configuration for remote connections and authentication mode represent the first layers of security for your instances.
Each feature within SQL Server enables access to functionality, but at the same time provides a method for an attacker to find a way into the system. The most significant potential for attacking an instance is through the use of features that expose an external interface or ad hoc execution capability. When you install a SQL Server instance, any feature that is not necessary for the core engine to run has been disabled by default.
You can enable or disable features within your instance by executing the system stored procedure sp_configure. Although sp_configure is used for several internal features, the following list describes how the options apply to the attack surface of your instance. Unless you are specifically using one of these features, it should be disabled:
Ad Hoc Distributed Queries
Allows a user to execute OPENROWSET and OPENDATASOURCE. Passwords are embedded into the text of the query, exposing a login and password to an attacker. If you need to frequently access remote data sources, you should use a linked server.
CLR Enabled
The Common Language Runtime (CLR) enables triggers, functions, and stored procedures that have been written in .NET languages such as C#.NET to execute within your SQL Server engine. If the CLR is disabled, CLR routines are not allowed to run.
Cross Database Ownership Chaining (CDOC)
Allows users to cross databases without having permissions rechecked as long as an ownership chain has not been broken.
Database Mail
Enables the use of the Database Mail features.
External Key Management
Allows approved external key management (EKM) software to manage encryption keys used within your instance.
Filestream Access Level
Enables the FILESTREAM capabilities within your instance. When set to 1, you can use T-SQL to manipulate FILESTREAM data. When set to 2, you can use a Windows API to directly interact with FILESTREAM data from your application.
OLE Automation Procedures
Allows OLE automation procedures to be executed. You can replace any OLE automation procedure with a CLR procedure that is more fl exible as well as more stable.
Remote Admin Connections
Enables the ability to remotely connect to the Dedicated Admin Connection (DAC). If remote admin connections are not enabled, you must fi rst connect to the desktop of the machine your instance is running on before creating a connection to the DAC.
SQL Mail XPs
Enables SQL Mail capability for backward compatibility. All SQL Mail functionality should be replaced with Database Mail.
Xp_cmdshell
Enables the use of xp_cmdshell so that you can execute operating system commands.
From the Microsoft Press book Microsoft SQL Server 2008 Step by Step by Mike Hotek.
Looking for More Tips?
For more SQL Server Tips, visit the TechNet Magazine SQL Server Tips page.
For more Tips on other products, visit the TechNet Magazine Tips index.