Basel II Compliance for SQL Server DBAs

The Basel Capital Accord Basel II [1] is an improvement of recommendations for banking standards on international scale, based on three intertwined pillars, issued by the Basel Committee on Banking Supervision. It’s an extension the Basel I Accord [1], and it sets risk and management requirements while introducing a data management approach.

A large number of countries adopted Basel II for their banking systems in order to comply with this international act. It applies to all internationally active financial institutions, and affects corporate finance, commercial banking, payments and settlements, retail brokerage, trading, sales, retail banking, and asset management.

The Basel II compliance goals ensure consistent, safe, and accurate data, with constant control and auditing information availability.

Depending on the structure and health of financial entities, supervisors periodically audit them to ensure the effectiveness of Basel II. The frequency of auditing depends on the last audit. The entities with lower financial rating are checked more frequently.

Requirements for the Basel II compliance are:

  • Controlled systems resistant to potential security incidents
  • Information security, prevention of both internal and external fraud
  • Accurate reporting available on demand in a timely manner

Basel II Compliance – general recommendations for SQL Server DBAs

The following actions are generally recommended in order to meet Basel II compliance:

  • A controlled and secure SQL Server environment is the first prerequisite to comply with Basel II regulations

    Ensure the system security with constant tracking of events related to the system structure and environment. It’s recommended to ensure SQL Server security by enforcing access rules that are strict and unchangeable by unauthorized parties. Once the rules are set, track all security related events, especially pay attention to permission changes and access to sensitive nonpublic data – both on database and table levels

  • Both external and internal user actions must be audited and reported when related to access permission changes. This is a must even if such actions are performed by administrative staff
  • Protect against potential threats and hazards, using secured and verified hardware and software. Pay attention to commonplace mistakes such as default logins and passwords that can easily become entrance for intruders
    Do not use supplied system and other security parameters on SQL Server. Instead of using the mixed mode (enables both Windows authentication and SQL Server authentication), use the Windows authentication only. It utilizes the Windows password policy for accessing SQL Server. This enables checking the password history, password minimum length, and password minimum and maximum life. The most important Windows password policy characteristics is the login lockout – if a login continuously fails for a specified number of times
  • The audited information must be tempering and modification evident, in any case and by any party – external and internal. This applies to administrative and revision staff too – no exception is acceptable. In addition, auditing such attempts is required both in terms of compliance, and further investigation and prevention

Basel II compliance recommendations for SQL Server auditing

To comply with Basel II regulations, it’s required to track all security related events on the SQL Server instance, database, and object levels. All security changes and access to the databases and tables that store sensitive data must be audited. Such information must be available on the auditor’s request via appropriate reports.

ApexSQL Audit is a SQL Server auditing and compliance tool that can track a range of SQL Server security events by monitoring instance and data access, permission, login, and password changes, and ensures compliance with regulations. It provides more than 30 built-in reports, and many of them are commonly needed for Basel II compliance auditing.

ApexSQL Audit helps as it:

  • Automatically audits security events that can have significant impact on consistency, safety, and accuracy of data
  • Provides reports that discover potential security risks
  • Identifies security and compliance vulnerabilities
  • Provides accurate reports for compliance reviews

Track security configurations

Any changes to security settings and configuration must be audited in order to comply with Basel II. Such changes represent potential threat to data safety.

There are several ApexSQL Audit reports related to the audit SQL Server security configuration requirements:

  • The Security configuration history report shows all changes to the SQL Server security entities (roles, logins, and users), including created or dropped entity, granted/denied permissions, and username and password changes. You can take any of these changes as an alert for a deeper investigation

    The Security configuration history report shows all changes to the SQL Server security entities

  • The Permission changes report shows all permission changes on a specific security entity marked for tracking. All permission changes, (especially permission grants) that are not found within the report represent high-security threat and a warning about inappropriate auditing settings

    Permission changes report found in ApexSQL Audit

  • Any permission, data, and schema changes on a specific object are shown within the Complete audit trail report. To achieve this, select the report and specify the specific object in the filter. Any user actions on the objects will be reported, providing activity history even for the users that are not supposed to be permitted access. This is again a sign for rising a security alert, and checking whether the security settings/permissions are properly set

    ApexSQL Audit - The Complete audit trail report

Audit user access to sensitive information on SQL Server

Auditing of user access to the confidential data is mandatory to meet Basel II requirements – any access can represent a potential security breach, regardless being legitimate or not granted. Also, all activities by members of the sysadmin role must be monitored and recorded too, without exceptions

The ApexSQL Audit reports that shows tracked access information are:

  • The Access history report shows when, how, what, and who accessed SQL Server objects. In addition, it provides exact procedures and T-SQL statements executed on audited objects

    The Access history report shows when, how, what, and who accessed SQL Server objects

Audit changes applied on users

Databases and tables may be secured on their level, but on the other hand, users may have sufficient permissions granted overriding security permissions set on databases and tables (e.g. a regular user becomes a member of the sysadmin role). That’s why the Basel II compliance requires auditing of any changes applied to user entities. Such information should be analyzed as a potential threat to consistency and data safety

ApexSQL Audit shows audited information about user entities changes with the following reports:

  • The Security configuration history report shows changes applied to users in a timely manner – when a user was created, when and what permissions were granted, revoked, or denied, and when a user was deleted. The report indicates internal security issues, and potentially malicious actions of users with sufficient or administrator privileges. To see history of security changes per specific user, simply specify the user in the Login filter.

Track user access to the system

Auditing user access (logons) to the system is an important Basel II compliance requirement too – any access attempt can be a potential abuse, whether the access was granted or not, and therefore must be tracked and reported

ApexSQL Audit provides auditing information about logons with the following reports:

  • The Unauthorized access report can indicate hacking attempts and show potential attacker targets – particular SQL Server instances and logins. It lists all unsuccessful login attempts, whether they are caused by non-existing logins or wrong passwords. Using this report brute-force attacks can be easily indicated. In such case appropriate security changes should be applied. The most common actions to prevent a success of brute-force attacks are:
    • Disabling/renaming the login that’s under attack, or
    • Enabling the Windows password policy for accessing the SQL Server instance through the Windows Authentication mode, instead of using the mixed mode (enables both Windows authentication and SQL Server authentication)

      ApexSQL Audit - Unauthorized access report

  • The Logon activity history report shows both failed and successful login attempts. For each login attempt, the report shows the server, login name, application, application host name, time, and login status
  • The Complete audit trail report provides all tracked events on the audited SQL Server instance. This report is not to be used often, but it can provide chronological history of all audited events, and can help in locating suspicious event patterns, e.g. particular successful logins followed by access denied to confidential data warnings

Track audit configuration changes

Basel II requires accurate reports for compliance reviews. In addition, any change on the auditing configuration must be documented, too. Omissions in the auditing configuration can be easily located using the documentation and corrected on time

The Audit settings history report shows the changes made to the ApexSQL Audit auditing settings – any addition or exclusion of SQL Server instances, databases, tables, or events. Any change on once set auditing configuration is indicative and should be investigated

The Audit settings history report shows the changes made to the ApexSQL Audit settings

Complying with Basel II regulations requires a secured SQL Server environment with consistent, safe, and accurate data, and with constant control and auditing information availability. With a range of auditing options and comprehensive timely reports, ApexSQL Audit provides auditing on SQL Server instances, databases and objects, while meeting with Basel II compliance requirements.

[1] From Basel I to Basel II

Useful resources
Implementation of Basel II
Basel Committee on Banking Supervision
Internal Audit’s Role in Achieving Bales II Compliance

December 5, 2013