FERPA Compliance for SQL Server DBAs

The Family Educational Rights and Privacy Act (FERPA) [1] is a Federal law created to protect the privacy of student education records. It establishes the rights of parents and currently enrolled eligible students to review their education records, request changes of misleading or inaccurate data, and control the record disclosure

All institutions funded by the U.S. Department of Education are obligated to comply with the FERPA regulations

The FERPA compliance ensures the integrity, confidentiality, and accuracy of personal student information. In addition, it provides students and their parents the right to access the student educational records. Also, the right to challenge the content of the records, or their release to third parties is guaranteed by the FERPA regulations

The FERPA compliance of educational institutions is revised and verified periodically by the Family Policy Compliance Office in the U.S. Department of Education

The FERPA act requirements do not strictly address IT or database security. However, according to the regulation requirements on enforcing privacy, the following steps provide compliance with FERPA:

  • Determine and document the permissions required for each education institution employee
  • Review permission settings on databases/tables and change access rights as necessary to maintain the integrity, confidentiality, and accuracy of personal student information
  • Audit the SQL Server instances, databases, and tables that contain education records
  • Periodically analyze the auditing reports that show events related to personal student records and take action where needed

FERPA compliance – general recommendations for SQL Server DBAs

The following general actions are recommended in order to meet FERPA compliance:

  • A secure and controlled SQL Server environment is the initial prerequisite to comply with FERPA regulations

    Ensure SQL Server system security with continuous monitoring of system events, and its environment. It can be achieved by enforcing strict access rules unchangeable by unauthorized parties

    Upon setting the rules, audit all events related to security – particularly pay attention to changes on permissions, and access to databases/tables with student education records

  • Whether internal or external, any user action must be tracked and reported when related to database/table access permission changes. Similar administrative stuff actions are not to be excluded from tracking
  • Use verified and secured software and hardware. Be aware of commonplace mistakes like default passwords and logins used by intruders at every initial attack attempt

    Avoid use of system supplied and other default security parameters on SQL Server. Instead of using the mixed mode (enables both Windows authentication and SQL Server authentication), switch to the Windows authentication only. The Windows authentication utilizes the Windows password policy for accessing SQL Server by enabling checking the password history, password minimum length, and password maximum and minimum life. The most valuable Windows password policy characteristic related to the SQL Server security, is the login lockout – the login gets locked for further use after a specified number of successive failed logins

  • Any tempering and modification of audited information must be evident, whether it was done by an external or internal party. This rule applies both to administrative and revision personnel as well – no exception is acceptable. Tracking tampering attempts is required both in terms of compliance regulations, further investigation, and prevention

FERPA compliance recommendations for SQL Server auditing

In order to meet FERPA regulations, it’s required to monitor, document and make available for reporting all security related events on the SQL Server instance, database, and object levels

Ҥ 99.62 What information must an educational agency or institution submit to the Office?
The Office may require an educational agency or institution to submit reports, information on policies and procedures, annual notifications, training materials, and other information necessary to carry out its enforcement responsibilities under the Act or this part.”

An auditor can request the reports that show security changes on SQL Server, access, and activity, even though the FERPA regulations are not specific and don’t provide details about the reports

ApexSQL Audit is a compliance and auditing tool for SQL Server that can audit security related events on SQL Server by tracking permission, login, and password changes, access to instance and its objects, and ensures compliance with FERPA regulations. It provides a range of built-in reports commonly required to ensure adherence to compliance regulative

ApexSQL Audit helps in becoming and remaining FERPA compliant as it:

  • Tracks automatically security events and user actions with potential impact on accuracy, consistency, and safety of data
  • Identifies risks on SQL Server security and compliance vulnerabilities
  • Provides comprehensive, accurate, and tamper evident reports for compliance reviews

Audit security configuration changes

Any action that changes security configuration and settings must be tracked in order to comply with FERPA. These actions can be a potential threat to compliance and data security

ApexSQL Audit provides several reports that show audited information related to the SQL Server security configuration events:

  • The Security configuration history report includes all changes on the SQL Server roles, logins, and users. The report shows username and password changes, created or dropped entity, and denied/granted permissions. Consider each report entry as a potential threat and an alert for a deeper investigation

  • The Permission changes per user and Permission changes per object reports provides information about permission changes for a specific security entity being audited. Any permission change, not listed within the report is a high security threat, and a warning about inappropriate auditing settings that can lead to FERPA compliance failure

  • The Activity per object report shows all actions related to data, security, and schema changes on a specific object. A history of user’s actions on the objects is shown, listing actions even for the users that aren’t supposed to be permitted access. This is again an alert for checking whether the security settings/permissions are properly set, or jeopardized

  • The Activity per application, Activity per user, and Activity per host reports provide similar information as the previously described report, but with different filtering parameters

Audit sensitive information access

Auditing user access to the confidential personal student information is a must to meet FERPA requirements – any access should be documented, regardless being legitimate or not. With no exceptions, all actions taken by members of the sysadmin role must be tracked and recorded, too

The following ApexSQL Audit audit reports provide access information:

  • The Access history per user report shows all user access events on objects in SQL Server. It provides exact T-SQL statements and procedures used to access audited objects

  • The Access history per object, Access history per host, and Access history per application reports provide similar information as the previously described report, but with different filtering parameters

Track changes applied on user entities

In order to meet the FERPA compliance, it’s required to audit all changes applied to user entities. Users with granted sufficient permissions can override security configurations set on databases and tables (e.g. if a regular user becomes a member of the sysadmin role)

The following ApexSQL Audit reports show information about user entities changes:

  • The User history report provides historical changes made to users – when a user was created, permission granted, revoked, or denied, and eventually deleted. The report can be used to indicate potential internal security issues, or malicious actions by users with sufficient or administrator privileges

  • The Created users and Deleted users reports are similar to the previous one, but focused on history of who and when created/deleted user entities
  • A history of SQL Server role changes is provided within the Role history report – logins added to a SQL Server role, altered, dropped, and permissions granted, revoked, and denied

Audit logons to the system

Auditing user logons to the system is important for any compliance regulation – any logon attempt must be documented, regardless being successful or not. Also, any logons by members of the sysadmin role must be tracked and recorded too, with no exceptions

The following ApexSQL Audit reports show captured information about logons:

  • The Unauthorized access report indicates attack attempts and give information on attacker’s targets – particular logins on SQL Server instances. The report provides all unsuccessful logons, caused by wrong passwords or non-existing logins

    Brute-force attacks can be easily spotted within the report as numerous failed logons with the same username. It’s recommended to prevent a success of brute-force attacks by:

    • Disabling/renaming the login being under attack, or
    • Using the Windows password policy through the Windows Authentication mode for accessing SQL Server instance, instead of the mixed mode (enables both Windows authentication and SQL Server authentication)
  • The Logon activity history report shows all logon attempts, regardless being successful or not. For each logon attempt, the report will show the application, application host name, SQL Server instance, login name, time, and logon status
  • The Complete audit trail report shows all monitored events on the audited SQL Server instance. The report can provide chronological history of captured events and can indicate suspicious event patterns, e.g. successful logons followed by warnings about denied access to secured data

Track audit configuration changes

FERPA requires accurate reports about permission settings on databases/tables and access right changes necessary to maintain integrity, confidentiality, and accuracy of personal student information. Changes on the auditing configuration must be tracked and documented, too. It’s essential to indicate omissions in the auditing settings and correct them on time. Documentation about auditing configuration changes can help with that

The Audit settings history report provides the history of changes made to the ApexSQL Audit auditing settings. Once set, the auditing configuration must be monitored for any change – each one can be indicative and should be investigated

Complying with FERPA regulations requires a SQL Server DBA to establish secured environment with ensured conditions for the integrity, confidentiality, and accuracy of personal student information. With a variety of fine-tuning auditing options and comprehensive historical reports, ApexSQL Audit provides auditing of SQL Server instances and its objects, with meeting with FERPA compliance requirements at the same time

[1] Family Educational Rights and Privacy Act (FERPA)
[2] U.S Government Printing Office (GPO) – The Code of Federal Regulations

Useful resources

Family Educational Rights and Privacy Act
Code of Federal Regulations
Family Educational Rights and Privacy


December 24, 2013