SQL Server & Data Security

Security is a crucial part of any mission-critical application. Best practices for setting up and maintaining security in SQL Server.

Authentication:

SQL Server supports two modes of authentication: Windows Authentication and Mixed Mode Authentication. In accordance with SQL Server security best practices, always choose Windows Authentication for your SQL Server installation unless legacy applications require Mixed Mode Authentication for backward compatibility and access. Windows Authentication is more secure than Mixed Mode Authentication and, when enabled, Windows credentials are trusted to log on to SQL Server. Windows logins use a number of encrypted messages to authenticate SQL Server and the passwords are not passed across the network during authentication. Moreover, Active Directory provides an additional level of security with the Kerberos protocol. As a result, authentication is more reliable and managing it can be reduced by leveraging Active Directory groups for role-based access to SQL Server. In comparison to Windows Authentication mode, Mixed Mode Authentication supports both Windows accounts and SQL-Server-specific accounts to log into SQL Server. The logon passwords of SQL logins are passed over the network for authentication, which makes SQL logins less secure than Windows logins.

Note: If you select Mixed Mode Authentication during setup, you must provide and then confirm a strong password for the built-in SQL Server system administrator account named sa. The sa account connects by using SQL Server Authentication.

Secure sysadmin account:

The sysadmin (sa) account is vulnerable when it exits unchanged. Potential SQL Server attackers are aware of this, and it makes hacking one step easier if they take control of this powerful account. To prevent attacks on the sa account by name, rename the sa account to a different account name. To do that, in Object Explorer expand Logins, then right-click sa account and choose Rename from the menu. Alternatively, execute the following T-SQL script to rename the sa account:

USE [master] GO        ALTER LOGIN sa WITH NAME = [<New-name>] GO

Membership of sysadmin fixed-server role and Control Server permission:

Carefully choose the membership of sysadmin fixed-server roles because members of this role can do whatever they want on SQL Server. Moreover, do not explicitly grant CONTROL SERVER permission to Windows logins, Windows Group logins and SQL logins because logins with this permission get full administrative privileges over a SQL Server installation. By default, the sysadmin fixed-server role has this permission granted explicitly.

Disable SQL Server Browser Service:

Make sure that SQL Server Browser Service is only running on SQL Servers where multiple instances of SQL Servers are running on a single server. SQL Server Browser Service enumerates SQL Server Information on the network, which is a potential security threat in a lock-down environment.

Disabling certain system stored procedures:

SQL Server comes with various system stored procedures such as xp_cmdshell or sp_send_dbmail that interact with operating system or execute code outside of a normal SQL Server permissions and may constitute a security risks. Thus such stored procedures should be specially treated.

Secure SQL Server Error Logs and registry keys:

Secure SQL Server Error Logs and registry keys using NTFS permissions because they can reveal a great deal of information about the SQL Server instance and installation.

Additional Instructions: 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.

Hardening the network connectivity:

A default installation of SQL Server 2005/ 2008 use TCP port 1433 for client requests and communications. These ports are well known and are common target for hackers. Therefore it is recommended to change default ports associated with the SQL Server installation.

Following are these steps to change the default port using SQL Server Manager Configuration tools:

1. Choose Start, All Programs, Microsoft SQL Server 2005/2008, Configuration Tools, SQL Server Configuration Manager.
2. Expand the SQL Server 2008 Network Configuration node and select Protocols for the SQL Server instance to be configured.
3. In the right pane, right-click the protocol name TCP/IP and choose Properties.
4. In the TCP/IP Properties dialog box, select the IP Addresses tab.
5. 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.
6. In the IPAll section for each instance, enter a new port that you want SQL Server 2008 to listen on.
7. Click Apply and restart the SQL Server Services.

SQL Data Security

Prevent SQL Injection Attacks:

SQL, or the Structured Query Language, is the command-and-control language for relational databases such as Microsoft SQL Server, Oracle, and MySQL. In modern web development, these databases are often used on the back end of web applications and content management systems – meaning that both the content and behavior of many web sites is built on data in a database server. A successful attack on the database that drives a website or web application can potentially give a hacker a broad range of powers, from modifying web site content ("defacing") to capturing sensitive information such as account credentials or internal business data.

Defending Against SQL Injection Attacks:

The good news is that there actually is a lot that web site owners can do to defend against SQL injection attacks. Although there is no such thing as a 100 percent guarantee in network security, formidable obstacles can be placed in the path of SQL injection attempts.

Comprehensive data sanitization;

 Web sites must filter all user input. Ideally, user data should be filtered for context. For example, e-mail addresses should be filtered to allow only the characters allowed in an e-mail address, phone numbers should be filtered to allow only the characters allowed in a phone number, and so on.

• Use a web application firewall. A popular example is the free, open source module Mod Security which is available for Apache, Microsoft IIS, and nginx web servers. Mod Security provides a sophisticated and ever-evolving set of rules to filter potentially dangerous web requests. Its SQL injection defenses can catch most attempts to sneak SQL through web channels.
• Limit database privileges by context. Create multiple database user accounts with the minimum levels of privilege for their usage environment. For example, the code behind a login page should query the database using an account limited only to the relevant credentials table. This way, a breach through this channel cannot be leveraged to compromise the entire database.
• Avoid constructing SQL queries with user input. Even data sanitization routines can be flawed. Ideally, using SQL variable binding with prepared statements or stored procedures is much safer than constructing full queries. Any one of these defenses significantly reduces the chances of a successful SQL injection attack. Implementing all four is a best practice that will provide an extremely high degree of protection. Despite its widespread use, your web site does not have to be SQL injection's next victim.

About Author:

Amit Gupta is technology lead in Systems Plus Pvt. Ltd and keen to resolve challenges using his technical skills. He actively contributes to technology and can be contacted at: amit.gupta@spluspl.com