Tuesday, December 24, 2013

Database (SQL Server) Security Best Practices and recommendations


This article is an effort to list best practices to secure Database servers. The first half of this article is list of best practices to secure Database servers in general, later we look at specific recommendations for Microsoft SQL Server and Oracle’s MySQL Server. The article does not claim to contain an exhaustive list of methods and best practices to secure a Data base server.


1-      (Web) Application Servers: If the data base server is being used in conjunction with an application server (which they almost always are.):
a.       Separate the Database and the application (possibly web) servers, setup the database server and (web) application server on two, physically, separate machines.
b.      In case of Web servers use a web application firewall to filter out any SQL injection attacks. Source code of the web application should be reviewed to ensure it does not allow any control character to be sent to the underlying database.

2-      Encrypt the archived files: Some databases provide this functionality to encrypt the data files of the tables that are being used. In general it is highly recommended to encrypt backups and SQL files that may contain instructions to insert data in the tables.

3-      Information Security Policies/Password Policies: Develop, Implement and Maintain information security policies, the policies should include a password control policy requiring a very (very very) strong password.

4-       Backup and Recovery: Have a backup plan and disaster recovery plan.

a.       Take backups regularly on daily, weekly and monthly basis.
b.      In case of Database security the above (point a) is extremely important. A backup can be a life saver if the impact of a malware or hardware corruption was devastating. There are instances where investigating and cleaning up a malware will take more time compared to cleaning the machine and restoring a backup to a point in time before the malware infection.
c.       Create an organization specific Backup and recovery plan. This plan should be tested regularly. If not tested for dependency, regularly, then it is of little practicality.
d.      Encrypt the backups. This re-enforces point 2 above.
e.       The backup of the database server and the recovery procedure to restore any lost database are the core of any Disaster Recovery Plan, at timed and planned intervals recovery drills should be carried out, so that the workforce is versed in recovery procedure and does not panic when a recovery is needed.

5-      Minimal Installations: Make sure the installation is minimal and only required components are installed.

6-      Try to hack yourself before the hackers do. Always make sure you do scans from leading industry tools, Nessus (by tenable.com) is highly recommended. Carry out a credentialed scan if your Database server is running any of the following:
a.       Oracle
b.      SQL Server
c.       Informix
d.      MySQL
e.       PostgreSQL
f.       DB2

7-      Normalization: Architect the database carefully, normalize the data, to make sure the sensitive information is not copied at multiple locations. Sensitive tables should be few in number and clearly known. Such architecture make certifications like PCI-DSS very smooth.

8-      Seperation of Duties: This is something even security savvy dba's role their eyes at. In majority of organizations the dba and network/system administrator are the same person.

9-       Auditing Mechanisms:There should be auditing mechanisms. All logins and their purposes should be known. More than knowing their purposes the privileges granted to them should be known. There should be a minimum number of Administrators/Super users. Also if possible ensure that all DML statements are also logged.

1-  Host Base Firewall: Using a host based firewall make sure that only designated (web) application servers can access the servers. That too only for the relevant port on which a data connection can be set up.

1-  Remove/Rename Defaults: Remove built-in administrator accounts. There should be no sample databases or sample scripts

For MS SQL Server:
 -      Run the Server Configuration Tool after the installation and disable the unnecessary features. This includes disabling the SQL Browser; this approach will reduce the attack surface of the SQL server.
2-      Patched: Make sure the server is patched regularly. A regular Nessus ‘credentialed scan’ always helps.
3-       Antivirus: Ensure the antivirus is always up to date.
4-      Windows Authentication Mode: Where possible use Windows Authentication mode. Windows Authentication mode is more secure compared to SQL server mode (SQL Authentication).
5-      Change all defaults: Besides default passwords it is best to disable default usernames like 'SA'. Also change the default port to some other port. In case a hacker is looking for a specific open port this measure will throw him off, .
6-      Best Practice Analyzer: Microsoft provides Best Practice Analyser for MS SQL Server. The best practice analyzer checks a SQL server installation against a list of best practices and reports which best practices are missing. A DBA should definitely consider using such a tool in conjunction with a vulnerability scanning tool to determine the shortcomings in implementing an SQL Server according to industry best practices.

For MySQL Server:
 MySQL is considered over here because of its popularity with database powered websites and due to the author's own affiliation and experience with the product.

1-      Install using RPM as that goes most of the recommended security settings at its first install.
2-      Keep the latest stable version of the database engine.
3-      Grant access to specific users from specific hosts only. This is in addition to the host based firewall which will permit only the legal user to access the server.
4-      The default user is 'root', in order avoid login attempts to this default user we can change the username of this root/administrator by using the 'RENAME USER' command and changing all instances of the 'root' user with any preferred name in the 'user' table of the mysql database.
5-      As the audit mechanism enable the 'Error Log' and the 'MySQL Log'.
6-      Run MySQL in a ‘chroot’ed environment.

About the Author: Saquib Farooq Malik, is a senior Information Security Consultant at ITButler e-Services(www.itbutler.com.au) . Saquib Specializes in Vulnerability Assessment and Penetration Testing, implementations of ISO 27001 in different corporate environments in the Middle East.
He is a CISSP, an ITILv3 Foundation certified professional, ISO 27001 Lead Auditor, Tenable Certified Nessus Auditor and a Lumension Certified Engineer.


  1. Thanks, I admire the valuable information you offer in your articles. i would recommend 24x7ServerSupport because they provide Server Support and Server Management at affordable price. We also provide Server Security 24*7 and available to solve your server issues within the given time period.

  2. SQL server is a platform has various choices like the development Language, data types it is considered as one of the major open source trends. It has interesting features at SQL Server DBA Online Training.

  3. Thanks for sharing this Informative content. Well explained. Got to learn new things from your Blog onPower BI Online Training