6.7. Best practice considerations: security
Since 2002, Microsoft's Trustworthy Computing Initiative has been an integral component in the design of each of their products. As a result, the default settings in SQL Server are designed for maximum security. Together with these defaults, the following best practices should be considered as part of achieving the most secure SQL Server environment.
Where possible, use Windows Authentication mode. Where SQL Server login authentication is required, ensure the SA password is strong, password policies are in effect, and passwords aren't stored in plain text in any connection strings or registry entries.
Install only the features you need. If you think you may need a feature like Reporting Services in the future, don't install it until you need it.
Surface Area Configuration (SAC) settings such as xp_cmdshell and Database Mail are secure by default in SQL Server 2005 and above. Before enabling any SAC option, make sure you're aware of the security risks of doing so.
Perhaps the most dangerous SAC option, xp_cmdshell should remain disabled wherever possible. If you enable it, ensure the Server Proxy account is configured to limit permissions. SQL Server BOL contains a description of both the dangers of xp_cmdshell and the procedures for limiting the potential adverse effects if enabled.
If mail functionality is required, use Database Mail in place of the older SQL Mail. Among other benefits, Database Mail doesn't require an Outlook client to be installed on the SQL Server, and it provides more stability by running in its own process space. Also consider the possibility of someone with access to Database Mail emailing sensitive data to someone not authorized to view it. We'll cover Database Mail in chapter 14.
To reduce the need for mail solutions such as Database Mail being enabled on the database server, consider using tools such as Systems Center Operations Manager (SCOM/MOM) as part of an integrated monitoring solution. We'll discuss this further in chapter 14.
Disable network protocols that aren't required. In almost all cases, TCP/IP should be the only enabled network protocol.
Configure SQL Server instances with static TCP ports, and place them behind firewalls with only the required ports opened.
Ensure SQL Servers are never directly accessible on the internet without an appropriate perimeter firewall strategy.
Enable the Windows Firewall on all SQL Servers and add the appropriate port exclusions.
Consider stopping the SQL Server Browser Service. In such a configuration, client applications need to include the appropriate TCP port as part of the connection details, either in the connection string or using an alias.
Use IPSec or SSL to encrypt network connections for applications containing sensitive data. Even if the database is encrypted with TDE or cell-level encryption, the network transmission of data is not, leaving it exposed to network packet sniffers.
For maximum security, use certificates from public certification authorities such as VeriSign in place of self-signed SQL Server certificates.
Separate the permissions of system administrators and DBAs. For almost all tasks, DBAs don't need to be local administrators, and system administrators shouldn't be members of the SQL Server sysadmin server role.
Ensure SQL Server service accounts aren't localsystem or local administrator accounts and use separate accounts for each service.
If the SQL Server service accounts are changed, make the changes using the SQL Server Configuration Manager tool, which ensures the appropriate permissions are assigned to the new account.
For SQL Agent jobs requiring access to operating system resources, run the jobs using proxies and credentials rather than using the SQL Agent service account.
Utilize schema and server/database roles to simplify management and reduce permissions.
While a Windows group can be added as a SQL Server login (therefore allowing simplified management of groups of logins), take into account the limitations with this technique, including the inability to assign the group a default schema, and complications with object creation requiring implicit ownership.
Application roles can be effective in cases where user permissions are managed internally within an application. Similar to database roles, permissions are assigned to the application role, and have the added benefit of ensuring the only way users can access the database is via the application.
Consider using the fixed server roles to avoid adding all DBAs to the sysadmin role. In environments where some DBAs have limited responsibilities, the fixed server roles provide a means of implementing least privilege. In a similar manner, fixed database roles provide the same function, but at a database level.
Despite the availability of advanced auditing options such as audit action groups, Change Data Capture, and logon triggers, don't overlook the need for basic auditing of login failures, which is enabled by default.
Take care when using the APP_NAME function in logon triggers. This value can be spoofed by someone with access to a connection string, therefore circumventing the intended logic of the trigger. We'll cover this in more detail when we look at Resource Governor in chapter 16.
If enabling Change Data Capture, reduce the performance overhead by only capturing the tables and columns required. The sys.sp_cdc_enable_table command takes an optional parameter for specifying a table's columns to capture.
Consider the new Transparent Data Encryption feature to prevent databases from being attached or restored to unauthorized servers, but be aware of its limitations before implementing it.
Cell-level encryption, also available in SQL Server 2005, is suitable in cases where only a small amount of data needs to be encrypted and the application and database schema changes required for cell encryption are manageable. With cell encryption, the possibility of unauthorized attach or restore should still be considered.
Back up all encryption certificates in a secure location, ideally separate from the database backup files. The certificate backup will be required when restoring encrypted databases to another server.
For maximum encryption strength, consider using encryption keys stored on hardware security modules (HSM) via Extensible Key Management (EKM).
Keep in mind that alternate encryption methods such as EFS and BitLocker aren't suitable for SQL Server implementations.
To prevent SQL injection attacks, ensure applications validate all user input for both data type adherence and for the presence of escape characters such as ', ;, and --. Ensure the testing processes are aware of the risks of SQL injection attacks and test accordingly. Finally, validate user input at multiple levels, not just at the initial application layer.
Consider using the Microsoft Source Code Analyzer for SQL Injection tool to assess the vulnerability of code to a SQL injection attack.
When disposing of tape or disk media containing sensitive data (backups, database, extract files, and so forth), destroy the media before disposal by using magnetic erase tools.
Secure disk backup directories appropriately, particularly backups made from unencrypted databases.
If virus-scanning software is installed on the SQL Server, exclude database files (MDF, NDF, LDF, and BAK files) from the scan, and if running on a cluster, ensure the quorum disk (usually Q:) is completely excluded.
Ensure the stability and performance impact of any virus-scanning software is measured in a load test environment before introduction into a production environment, and ensure the software is cluster aware before use in a cluster.
Secure SQL Servers in locations that prevent physical access to the general public and unauthorized staff.
Delete FTP utility executables from SQL Servers unless required.
Database ownership chaining should be disabled (which is the default setting) to prevent cases in which a user can create an object with a specific owner in a database in which he is the database owner for the explicit purpose of accessing objects in another database with the same owner. Without the ownership chain in effect, the user would be denied access to those objects in the other database.
Stay up to date with security notification, service packs, and hotfixes. The Microsoft website provides links to various resources for this purpose, including an RSS feed, email, instant messaging, and mobile device notification.
Where possible, create linked servers that preserve the calling user's security context. Do so by selecting the "Be made using the logins current security context" option in the Security page of the Linked Server properties dialog. For servers not supporting this option, consider the security ramifications of a user's privileges being potentially elevated when made in another security context.
Additional information on the best practices covered in this chapter can be found online at http://www.sqlCrunch.com/security.
The focus of this chapter was configuration from a security perspective. In the next chapter, we'll examine configuration from a performance and stability perspective.