Security and compliance in SQL Server

The primary purpose of any database management system is to store and provide accurate information as requested by other software clients. Security of the database system and the information it keeps is another crucial component. There are many aspects of SQL Server security configuration, such as authentication, server and database roles, ownership, or Common Language Runtime (CLR) integration. However, in this article, we’ll focus on those that are related to (and common for) most of compliance regulations.

Each compliance regulation (e.g. GDPR, PCI, HIPAA, GLBA, Basel II, FERPA, or SOX) requires a certain security level. After analyzing all of the compliance regulations, it is to be concluded that they have in common many requirements – primary about data security via database system and data access auditing.

Being compliant doesn’t also guarantee you’re safe. If you audit more than the specific compliance regulations minimum is, it’s OK as it provides higher safety. On the other hand, logging too much will make unnecessary overhead; we’ll focus on necessary compliance auditing that’s related to SQL Server security.

The following requirements are common for the compliance regulations in terms of security:

• Secure and up to date database system that hosts information
• Precisely defined access rules, with minimum needed permissions for appropriate and valid information access
• Auditing of logon attempts – both successful and failed
• Auditing of all actions and changes applied to roles, users, and databases

General recommendations for security and compliance in SQL Server

The following SQL Server security related actions are generally recommended to be taken for accomplishing the compliances.

• Set up a SQL Server environment that’s secure and monitored constantly. Provide the SQL Server system with continuous event auditing, whether the events are internal or external, invoked by the system or users. Enforce strict rules that unauthorized parties cannot change and apply the rules to logins, databases, users, tables, and any SQL Server object.

  In short, ensure the security and confidentiality of the data using proper user permissions and limitations.

  However, regardless of the strict rules set, compliance regulations for SQL Server requires auditing and periodical analysis or all events related to security – including the events were performed by users or members of administrative personnel. Any permission changes on the SQL Server objects, and access to databases/tables with confidential records must be documented.

  Each and every compliance regulation requires that users and members of administrative personnel are treated equally in the process of SQL Server auditing and providing audit information to auditors.

• Use secure and officially verified hardware and software for accessing SQL Server data. There are common potentially high security configuration omissions, such as default network settings, logins, and passwords often used by attackers.

  Any supplied default SQL Server system security parameters should be modified. It’s recommended not to use the mixed mode (enables both Windows and SQL Server authentication) for authentication. Instead, switch to the Windows authentication only – that will enforce the Windows password policy – checking of the password length, life duration, and history. The feature of the Windows password policy that makes it different from the SQL Server authentication is the login lockout – after a number of successive failed logon attempts the login becomes locked and unusable for further use.

  On the other hand, the SQL Server authentication does not provide any methods for detecting brute-force attack attempts, and what’s worse, SQL Server is even optimized for handling large numbers of rapid login attempts. So, if the SQL Server authentication is a must in a particular SQL Server system, it’s highly recommended to disable the SA login.

• Disable features and services in SQL Server that are not required for proper running of the database system. Many SQL Server components require additional set up and modification of their default settings. By omitting such components, additional potential security issues are avoided. Install only what you need.
• Periodically check and verify the SQL Server security configuration, along with rules and permissions previously defined. Any change that is not documented can be a potential security issue and lead to losing the database system compliance status.

SQL Server security and compliance auditing requirements

Compliance regulations specify only what the requirements are, not how to achieve them. The general requirement is to ensure the confidentiality, integrity, and proper availability of sensitive information, but the process is not explained. However, it’s mandatory to audit, document and provide reports for all security related events on the SQL Server instance, database, and object levels in order to be compliant.

It’s up to an auditor to request different types of SQL Server events audit reports related to security and providing adequate documentation is not an easy task.

ApexSQL Audit is a SQL Server auditing tool that can track and document all security related events, based on user selection, including password and login changes, logon attempts, and access to all or specific SQL Server objects. As an output, it provides a variety of built-in reports that can help in terms of compliance and in detecting potential SQL Server security issues.

ApexSQL Audit helps with being compliant and discovering security risks as it:

• Automatically monitors events to make sure compliance rules are met
• Provides accurate and relevant reports for compliance reviews
• Provides reports that discover risks and vulnerabilities
• Identifies compliance and security vulnerabilities

SQL Server security configuration changes

Changes applied to SQL Server settings related to security must be monitored and documented for the compliances purpose. On the other hand, this information can indicate potential security risks and omissions.

There are several reports in ApexSQL Audit that show captured events that modified the SQL Server security configuration:

• The report Security configuration history provides history of changes on SQL Server logins, roles, and users. It shows changes on password and username, a created or dropped entity, and entity permission changes:

  

• The Permission changes report shows permission changes for a particular security entity that is monitored:

  

SQL server security and data access

Monitoring user access to data is a must to meet compliance requirements. Each access must be tracked, being illegitimate or not. Also, as compliance regulations require without exceptions, actions performed by administrative personnel must be audited too

The information about the data access is provided with the following ApexSQL Audit audit reports:

• The Access history report provides a history of user access to SQL Server databases and tables. Moreover, exact procedures and T-SQL statements used to access monitored objects are documented for each access attempt. Any access to a SQL Server object by a user not supposed to have such permission is an alert to check and verify permission parameters for the particular user and accessed object

  

Logons to the SQL Server database system

Each of the compliance regulations requires user logons to the system to be tracked and documented –every logon event must be captured, unsuccessful or not. Again, with no exceptions, logon attempts by administrative personnel must be audited.

Audited information about logon events are provided with the following ApexSQL Audit reports:

• The Logon activity history report provides all logon attempts, both successful and unsuccessful. Each logon attempt is listed with the SQL Server instance, application, application host name, logon status, time, and used login name.

• The Unauthorized access report is similar to the previous report, but narrowed to the failed logon attempts. It can indicate attacks and provide information on attack targets – specific login names and SQL Server instances. Report entries represent failed logons, caused by use of non-existing login names or wrong passwords:

  

  As described, brute-force attacks are hard to indicate when the mixed authentication mode is used for the SQL Server database system authentication. The Unauthorized access report can easily indicate such attacks with numerous failed logons using the same username.

Complying with the regulations requires certain SQL Server security level, verified by appropriate auditing reports. The auditing reports ensure the compliance requirements, and help in identifying compliance and SQL Server security vulnerabilities. ApexSQL Audit with a range of auditing configuration options and a variety of comprehensive reports, makes SQL Server auditing easy, while ensuring security and compliance in SQL Server.

Useful resources

Information Security
Microsoft SQL Server 2008 Bible, Wiley Publishing, Inc.
Implementing Database Security and Auditing
A White Paper Proposing Practical, Cost Effective Compliance Strategies